How to save description for transactional data !

[phamtinkt88]

Hi all,

My customer wants to save descriptions for their transactional data. For example:

They enter 5000 for Other personel expenses in January .Then they enter "Car allowance" as description.
They enter 3000 for Other personel expenses in Febuary .Then they enter "Customer conference in LA" as description.
....

I don't know how to design cubes to meet this purpose . Please help me !

Thanks & Regards,
Tin Pham

[Alan Kirk]

Hi all,

My customer wants to save descriptions for their transactional data. For example:

They enter 5000 for Other personel expenses in January .Then they enter "Car allowance" as description.
They enter 3000 for Other personel expenses in Febuary .Then they enter "Customer conference in LA" as description.
....

I don't know how to design cubes to meet this purpose . Please help me !

Step 1: Visit http://www.microsoft.com/sqlserver/en/us/default.aspx
Step 2: Create a purchasing system based on that relational database. Or, if you don't want to do that, use My SQL. Or Oracle. MS Access, even. Or if you're desperate, DB2. If you're even more desperate, just buy them a copy of MYOB or Quicken.

What you DO NOT do is use OLAP cubes for entering transactional data (much less descriptions of it) any more than you use a snow plough to cross the Sahara. TM1 is used to aggregate, calculate, slice and dice numeric values. It has some limited string manipulation ability, but strings like that do not aggregate meaningfully. They do not consolidate meaningfully. This is why we have RDBMS's. If you're taking money from these people to provide them with management information / business intelligence solutions, you should already know this. (And how to store strings in a TM1 cube, come to that. It is covered quite well in the manuals. "When not to" comes with experience and this, emphatically, is a "when not to".)

[phamtinkt88]

Hi all,

My customer wants to save descriptions for their transactional data. For example:

They enter 5000 for Other personel expenses in January .Then they enter "Car allowance" as description.
They enter 3000 for Other personel expenses in Febuary .Then they enter "Customer conference in LA" as description.
....

I don't know how to design cubes to meet this purpose . Please help me !

Step 1: Visit http://www.microsoft.com/sqlserver/en/us/default.aspx
Step 2: Create a purchasing system based on that relational database. Or, if you don't want to do that, use My SQL. Or Oracle. MS Access, even. Or if you're desperate, DB2. If you're even more desperate, just buy them a copy of MYOB or Quicken.

What you DO NOT do is use OLAP cubes for entering transactional data (much less descriptions of it) any more than you use a snow plough to cross the Sahara. TM1 is used to aggregate, calculate, slice and dice numeric values. It has some limited string manipulation ability, but strings like that do not aggregate meaningfully. They do not consolidate meaningfully. This is why we have RDBMS's. If you're taking money from these people to provide them with management information / business intelligence solutions, you should already know this. (And how to store strings in a TM1 cube, come to that. It is covered quite well in the manuals. "When not to" comes with experience and this, emphatically, is a "when not to".)

Hi Alan,

Sorry for using wrong word. It's not transactional data.
I mean : user plan for "Other personel expenses" and they want to enter description for that data .And when the manager review ,he will know the budget prepare for what.
For example :

• Year : 2008
  Month : January
  Account : Other Expenses
  Amount : 5000

User want to use this for "Car allowance"

• Year : 2008
  Month : January
  Account : Other Expenses
  Amount : 3000

User want to use this for "Cutomer Conference"
I know how to store string in cube ,but i don't know how to store string for a specific figure.
I intend to use a Description cube along with main cube.
For example :

• GL cube:
  • Currency
    Version
    Fiscal year
    Department
    Account
    GLMeasure
  GLDescription cube:
  • Currency
    Version
    Fiscal year
    Department
    Account
    GLMeasure
    Description

If using this method,i can store description in GLDescription mapping with each cell in GL. But i don't think it's efficiency.
Could you give me advices on this !

Thanks & Regards,
Tin Pham.

[Alan Kirk]

We did use a two cube solution for Capex budgeting when that was done in TM1, though it was one that I inherited; the reason that they did it that way was because a Capex project's value was entered by month but there was only one description required for the whole year, though you aren't in that situation.

You do, however, face a similar problem which is that if these are your dimensions:

Currency
Version
Fiscal year
Department
Account
GLMeasure

(and I'm assuming that "Fiscal Year" is actually broken down into months and possibly week elements as well), then depending on what you have in the GLMeasure dimension it's entirely possible that you will have multiple budget entries which correspond to any given combination of elements. (As per your example.)

That was still a problem with our Capex system as well. The solution that the original designer came up with was to create a "Line Number" dimension which allowed users to enter different Capex budget entries for the same accounting combinations into different "lines". That would be a possibility for you as well; the manager would have to know to drill down to N level to see the descriptions (because as I said earlier, you certainly won't see them at consolidation level), but it would be a possibility. The biggest difficulty I see you facing is if you have an expense which spans multiple periods; you'd either need to write it at consolidation level or copy the description across each period.

[phamtinkt88]

Hi Alan,

I appreciate your advices !

Thanks again,
Tin Pham.