How to model header vs. line item data

[Former Member]

Hi everyone,

My data source differentiates header and line item data that are of different granulation.The header data has two key figures on an aggregated level. The line-item data has complementing kex figures on a more detailed level.

How would I model this in BW? Would I create two cubes (one for header and one for line item data)? And for reporting purposes I would combine them into a multiprovider? Or which way would I choose?

Regards

Daniel

[Former Member · Accepted solution]

Hi there,

I believe it is better to upload everything in one InfoCube, with two update rules coming from two different datasources, one that would feed the Infocube with header data and the other that would feed the cube with line item data.

For example, imagine that you would have in your header data the company, the financial count and a key figure of total amount. And you would have for detailed line item data the company, the financial count and the key figure of detailed amount of line item.

So the first update rule you would feed and map into the cube the company, the financial count and the keyfigure_1 (total amount) and for this update rule you're not mapping either line item neither keyfigure_2 (detailed line item amount). As opposite in the second update rule you would feed and map into the cube the company, the financial count, the line item and the keyfigure_2 (detailed line item amount) and for this update rule you're not mapping the keyfigure_1 (total amount).

So after this mapping imagine you would run the two extractors for the company xpto and the count 331, you would have an example like this in the cube, two records each coming from the two different update rules:

company count line_item keyfigure_1 keyfigure_2

xpto 331 350,20 0

xpto 331 001 0 222,13

...

...

...

So if in your query over the same cube you saw the data by company xpto and count 331 you would see both the keyfigures. If you detailed by line_item 001 you would only see the keyfigure_2

You get the idea.

Diogo.

[Former Member · Accepted solution]

Hi Diogo,

Thanks for your reply.

Do I understand that correctly: you can upload data into the cube without necessarily specifying a line-item?

In other words I do not have to fill all characteristics of the cube? So in this way I can upload data of different granulation into a cube?

Cheers

Daniel

Edited by: Daniel Krumme on Oct 2, 2008 3:17 PM

[Former Member · Accepted solution]

Hi Diogo,

one more question: why would you recommend this solution over a solution with 2 cubes?

Because the handling in querying is easier?

Cheers

Daniel

[Former Member · Accepted solution]

Hi again,

Yes it is possible,

Just when mapping the keyfigures of the update rule that doesn't have line item, in the mapping of the line item choose (if it is not already chosen by default) the "inital value" option.

My solution is a possible solution, you can simple create a single InfoCube that has related data and is feed by two sources, taht's why the same InfoCube. Off course you could also create two cubes and one multicube, although in this case you're having 3 InfoProviders instead of 1. Also having separate InfoCubes might be helpful if you really want to separate data at any point. If you always want to see data from both header and line item why not only one InfoProvider and one place to keep and maintain data. The cons in this case you could have a lot of data in only one cube, rather than separate data in two cubes.

But I guess this is ways of perspective.

Diogo.

[Former Member · Accepted solution]

Hi Diogo,

OK, excellent. Thanks a lot!

Cheers

Daniel