cancel
Showing results for 
Search instead for 
Did you mean: 

Organisation of dimensions and characteristics

Former Member
0 Kudos

Hi gurus,

I have one basic question about modeling with dimensions. I am still learning and trying to better understand.

I saw the scenario 1 and thought it is wrong.

So I thought scenario 2 would be a better solution.

Now I read that the scenario 3 is also possible.

If there is the current scenarios with the same characteristics but with different dimensions, what is the difference between them? Especially between 2 and 3.

The requirement would be for example:

Turn over for customer X with risk class 2 in month 03.2011.

Open items within sales division X for Customer X in month 05.2011.

Turn over for business segment X in month 02.2011.

Turn over for customer X in country US with risk class 5 in month 01.2011.

Wouldn't it be possible to achieve that with scenarion 2 and 3 and is there any reason why not with scenario 1?

Thank you in advance for your help!

Scenario 1:

-


Dimensions:

Organisational units

Company code

Credit control area

Customer number

Profit Center

Business unit

Business segment

Sales organisation

Sales division

Distribution channel

Time

Calmonth

Risk class

Risk score

Country

Country

Facts:

turn over

open items

Scenario 2 (data model changed/ separated dimensions):

-


Dimensions:

Company code

Credit control area

Company code

Customer

Customer number

Profit Center

Profit center

Business segment

Business unit

Sales organisation

Sales organisation

Distribution channel

Sales division

Time

Calmonth

Risk class

Risk score

Country

Country

Facts:

turn over

open items

Scenario 3:

-


Dimensions:

Customer

Customer number

Organisational units

Company code

Credit control area

Profit Center

Business unit

Business segment

Sales organisation

Sales division

Distribution channel

Time

Calmonth

Risk class

Risk score

Country

Country

Facts:

turn over

open items

Accepted Solutions (1)

Accepted Solutions (1)

anindya_bose
Active Contributor
0 Kudos

Hi

Whatever characteristics you put in one dimension will NOT impact anything in reporting.

But whenever you are designing you dimension you need to make sure dimension table size are well within 20% of the ( preferably 10%) fact table size-- this is because if your dimension table are too big then you will have bad reporting and loading performance.

You can chose any of the data model and then do some data load to the cube. Then run one report SAP_INFOCUBE_DESIGNS

This report will tell you the size of dimension table with respect to fact table.

I think you can definitely put more characteristics under dimension COUNTRY because this dimension is really small one.

Before actual dimension modeling you need to know what is the probable cardinality of one particular characteristics.

Cardinality = number of distinct values for a characteristics

Regards

Anindya

Former Member
0 Kudos

Ok and thank you.

I always thought that it is a big mistake to put characteristics which don´t belong to the same dimension in one dimension.

Like in scenario 1 the different characteristics are in one dimension "Organisational units".

So like you mentioned it, the cardinality of that dimension would be too big.

Correct?

Cheers,

saplaz

anindya_bose
Active Contributor
0 Kudos

That completely depends on your data..

Just as an example if you put material and customer in one dimension and any customer can buy any material..

Now total number of Material is 10,000 and total customer is 90,000 ... then your dimension table will have.

10,000 X 90,000 = 90,0000000 entries which is quite a big number.

So it's always advisable to keep material and customer in different dimension in this case.

Another example is like if you are reporting for a Telecom Operator then "Customer Number" would have lots of values.

In that case if you use only Customer Number in Customer dimension and mark that dimension as *Line Item" dimension you loading and reporting performance would be much better.

Regards

Anindya

Edited by: Anindya Bose on Dec 16, 2011 10:20 PM

Former Member
0 Kudos

Thanks, your answer makes it more clear.

I read a few papers about bw modeling.

For better understanding:

In one it says that each row in fact table is uniquely identified by a value combination of the respective dimension.

For example the scenario 2 and dimension Profit Center.

The granularity in this dimension would be, the most granular business unit.

That means for the combination Profit center, Business Segment, Business unit with turn over 2000 Euro will be one data row in fact table?

For example:

Profit Center -- Business segment -- Business unit -- turn over.

100 -- 10 -- 1 -- 2000 Euro.

How would it then be possible to analyze in quey only the turn over, for example for Business unit 1?

anindya_bose
Active Contributor
0 Kudos

This is like below...

Let's say you have 3 characteristics under one dimension Material, Color, Type. Your fact table contains Amount.

Your Time Dimension contains Cal/Day and Cal/Year

You got one record from Source

Material, Color, Type. Cal/Day Cal/Year Amount

M01 Red Software 01.01.2011 2011 $ 100

Material Dimension:

DIMIDl | SID_MATERIAL | SID_COLOR | SID_TYPE

1111 1001 |2002 | 3003

So here dimension id 1111 represents Material m01, of Color Red of Type Software. So basically your dimension ids are combination of SIDs of characteristics.

Now the link between Material number and SID can be found in SID table of InfoObject

Similarly, say ur Time dimension table has one id 2222..which means 01.01.2011 and 2011.

So your Fact table will contain.

KEY_DIM_MATERIAL | KEY_DIM_TIME | KeyFigure

1111 2222 | $100

NOw you can see wherever you put one characteristics , different combination of SIDs will generate one DIM id which can determine one transaction record uniquely.

Regards

Anindya

Answers (1)

Answers (1)

ravi_chandra3
Active Contributor
0 Kudos

Hi

I think 2nd scenario design is the best data model design according to ur 3 design model.Plz make sure that the relationship between two characteristic in a dimension should not have the relation like M * N because that will generate large no. of entries i the dmension table . like don't try to keep customer no . and docno. in a single dimension because that will have large no. of entries leading to the large no. of dimension table.

Hope u understand.

Thanx & Regard,

RaviChandra