Design question

Post Reply
Toto
Posts: 71
Joined: Mon Jul 20, 2009 8:52 am
OLAP Product: TM1
Version: 9.5 Build 9.5.00100.2380
Excel Version: 2003

Design question

Post by Toto »

Hello,

for a cube I chose to have the four dimensions: Customer, Year, Facts, Product. One customer in the customer dimension belongs to a special sector.
In the analysis some of the questions to answer are:
- How much sales revenue per sector?
- How much sales revenue per country?

Would you create two more dimensions, one for country and one for sector? Or would you create two attributes (country and sector) in the customer dimension?
What are the advantages/disadvantages?

Thanks a lot!

Toto
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Design question

Post by tomok »

There are a number of options and the answer lies in how do you want to be able to analyze data.

1) Put a Sector and Country attribute on the Customer dimension. This is not a very good option because you would only be able to filter subsets of customers off this and it wouldn't provide subtotals and totals in the cube. You would be relegating the cube, at least in this respect, to more of a relational database and lose some of the OLAP functionality.

2) Build multiple hierarchies in the Customer dimension. You could have a By Sector tree and also a By Country tree.

3) Create a separate Sector dimension and Country dimension.

What you have to ask yourself, or your customer, is how do you want to be able to analyze the data and how is that data organized. If you choose option 2 then you won't be able to see the intersection of Country and Sector because they are in the same dimenson. The only way to see that would be option 3.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Toto
Posts: 71
Joined: Mon Jul 20, 2009 8:52 am
OLAP Product: TM1
Version: 9.5 Build 9.5.00100.2380
Excel Version: 2003

Re: Design question

Post by Toto »

Hello Tomok,

thanks a lot for your detailed answer, it helped me a lot!

Regards,

Toto
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Design question

Post by Steve Vincent »

Another consideration is does a single customer trade in multiple countries or is there scope for that to happen in the future? If so the seperate dims would be the best way forward.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Post Reply