Multiple dimensions Vs. Single dimension and hierarchy

Hi BI community,

I just have a design question.

Background: Our main dimension is the article dimension. It is pretty big, because the granularity of the table is one row per article number, size, season and country. Also every article belongs to one and only one style. The articles also have standard attributes like brand, division, etc. and country dependent attributes like landed costs, catalogue price and so on.
One possible hierarchy would be Style --> Article --> Size
There are two major report categories: Whole sales and Retail
Whole sales reports go only to Style level
Retail reports are more specific and could go to size level by store and day.
The maintenance of the article table is slow regardless of the loading strategy that is used.
From de multidimensional design point-of-view I would like to get your suggestions about these two models:

Option 1:


Option 2:



For me one major advantage of the option 2 is the performance of the whole sales report, but in theory build hierarchies (option 1) enrich the multidimensional analysis. The loading process is even more complicated with the option 2.

The last question is if it worth to keep the country dependent attributes in a separated table. I think this is worst because creates a snow flake schema and also the data model should win against the complexity of the ETL process. In other words, the data model should not be changed just to simplify the ETL load.

Kind Regards

Any comment will be highly appreciated.

I don’t understand the hierarchy you are writing.

You wrote every article belongs to one and only one style, that means there are 2 hierarchies (independent) in the Article dimension:

Article-->Style and
Article--> Size

I would suggest to build these hierarchies into the Article denormalized dimension table.
If you have performance problems, you should create aggregate tables or materialized views where you store aggregated fact data for Style /Size ( and other dimensions).

I think there is no need to store the country specific attributes in separated tables.

Hi,

thanks for your answer.

I may expressed wrong, one style has one or more articles, and one article one or more sizes, i.e.
Style: cool and dry
Articles: running shoes A, B, C, etc.
Sizes: running shoes A: S,L, XL. Running shoes B: S, L, M, etc.

I will keep a denormalized table with the hierarchy in it and try to optimize the data load + try to materialized a view for some reports.

Thanks again,

Paul

It not really a hierarchy, they are characteristics of the article.

The best approach depends on how articles are identified.  Different retailers do it different ways.  Some assign an id to the article and record the same id regardless of size while others assign unique id's to each variation of an article.  If your business did the former, then option 2 is appropriate.  Since your business does the latter then option 1 is correct.

If what you are trying to do is load the sales data into a cube, and you find that option 2 performs better, then that is fine.  Go with it.  But in a relational model, option 1 is the most common.