Different Granularity

[pramodb]

Hello All,

I have two tables:
PRODUCT (FACT) (here behaving as Dimension) and SALES (FACT) table which are on different granularity but are joined on 1..1 to 1..n from PRODUCT to SALES...on 3 fields ABC, DATE field and XYZ but SALES  has data at lowest level of  SALES_DIM which means for every above 3 fields, there is SALES_DIM...so when data is pulled from these tables...wrong data is returned as in duplicate records...because suppose there are 2  SALES_DIMs  for that dataset, the result will be actual count*2 times which is wrong...

What can be done here to resolve the issue...is determinants solution here? i am not getting how to apply determinants please tell me and on which fields....

Thanks

[MFGF]

What is the primary key of the Product dimension? Assuming it is not the combination of the three fields you are using in the join, you will need to define two determinants in your Product dimension. The first will be for the combination of these three columns, and these will all be in the 'id' of the determinant. You will need to check the 'Group by' checkbox for this determinant. Any descriptive items at this level will need to be added as attributes. The second determinant will need to have the PK as the id, and should have the 'unique' checkbox selected. It is important that this is second in the list of determinants.

Regards,

MF.


Sent from my iPad using Tapatalk HD

[pramodb]

thanks for the reply..but i think you mean the determinants need to be applied on SALES fact or PRODUCT dimension..as SALES is at more granular level..having SALES_DIM as the primary key

[MFGF]

No. You need to define the determinants on the Product dimension.

Regards,

MF.


Snet form my fumblefingers
iPhon 5 usig Tapatalk

[MFGF]

Just to be sure I have understood your situation properly...

You have a product dimension with multiple levels of granularity. Your sales fact data joins to the product dimension at a higher level than the lowest (meaning multiple product dimension rows could exist for each fact)? Is this your situation?

If so, then your determinants need to be defined on your product dimension to prevent double-counting of the facts when reporting.

Cheers!

MF.

Sent from my iPad using Tapatalk HD

[pramodb]

Taking your last post, I rephrase my situation:

I have Sales Fact with multiple levels of granularity.
Sales fact data joins to the product dimension at a higher level than the lowest in Sales Fact (meaning multiple Sales Fact rows could exist for each of the Product Dimension based on joining fields)?

In this case, i think on the sales fact determinants need to be applied which I did, but now the query generated is like:

SELECT ABC,
       SAMPLE_DATE,
       XYZ,
       MIN(),
       MIN(),
   ..
   MIN(SALES_DIM)
FROM SALES
GROUP BY   
ABC,
       SAMPLE_DATE,
       XYZ

This gives me SALES_DIM as minimum values and which is not correct.

It will help if you could give detailed description of what needs to be done.

[MFGF]

Oh. Hold on! Having a single fact containing multiple levels of granularity is a big no-no. Best practice is to separate these out into different fact query subjects, each of a single level of granularity, and each joining to the same set of dimensions, but with different grain dimensional keys. Determinants on the dimension(s) then help the query engine to aggregate without double-counting.

Regards,

MF.


Snet form my fumblefingers
iPhon 5 usig Tapatalk

[pramodb]

one quick query, how to apply determinants now on combination of  3fields as "Key" and whether rest of the field as "Attributes" on Fact Query subject with Sales_Dim to be kept in the query and in Attributes?