Concatenation - When to use?

[#1]

Hi all,

I need to take two fact tables which are closely linked so they share a lot of dimension tables so from advice and some resources, I would concatenate the fact tables togather. However, I do not want them to be reported in the same tables/charts as they mean different things.

My question would be how would I be able to take the 1st part of data and present it in a chart etc and the 2nd part to be presented in another part of data? I think that if I added filters, it would affect both parts of the concatenated table?

Thanks for any help/advice you can give.

Mike

[Anonymous]

Mike,
First, the way you build your "data cloud" does not restrict the way how you create your front-end objects. If for example, if a logical table (in the data model) contains fields F1, F2, F3, F4 - it does not mean you must use them all in the same chart. You can have a chart with F1 and F2, another chart with F3 and F4, yet another chart with F2 and F3.
Second, the selections in a filter as a rule will affect all objects, no matter if all fields are in one table, or in different but logically linked tables.
Third, if you want some parts to be independednt, you can take steps towards this goal. I can't give you a specific advice because the question is rather generic. Possibly you need to use logically disconnected parts of the data model, or to use generic keys, or maybe use macros to control the selections behavior. It depends on your actual business needs.

[Oleg_Troyansky]

Adding to Michael's points (all valid ones, I could perhaps argue about the use of macros to solve data modelling issues...):

Yes, when you make a selection in a field that's common between the two data entities, both will be affected - in most cases, this is what's required. In some cases, it isn't and then you need some more "modelling" logic than a simple concatenation.

To be able to segregate the two, you can approach it in a few ways:

- you can have separate fields for your amounts. For example, if your 2 data entities are Invoiced Sales and Open Orders, you can have 2 seaparate fields: [Sales Amount] and [Open Orders Amount] and this way, one chart will only show sum([Sales Amount]) etc...

- you can keep identical field names (in this example - a field "Amount") and manage separate flags. Flags are usually fields that are assigned 1 for all relevant rows and null() for all irrelevant ones.Multiplying by the flag serves the role of "selecting" one data entity vs. the other. For example:

sum(Amount*Sales_Flag) versus sum(Amount*Orders_Flag)

regards,

Oleg

[Anonymous]

"I could perhaps argue about the use of macros to solve data modelling issues" - I'd argue about this myself... Was pushed in the corner a few times, and didn't have much choice