November 4, 2016 at 7:37 am
I have 3 tables and the schema does not really fit into a many to many or a referenced relationship. Please advice on how to define relationship
FactTable- exposure_amount (Pkey exp_id, Fkey exp_ps_id, exp_amount)
DimTable1 exposure_settings (Pkey ps_id)
DimTable2- exposure_cycles (Pkey pc_id, Fkey pc_ps_id, pc_name)
I call it strange as exposure_settings is kind of a link table but not. Both Facttable(exposure_amount) and DimTable2(exposure_cycles) point towards DimTable1(exposure_settings)
With this kind of schema, I cannot define a referenced relationship as exposure_settings does not have a Fkey to DimTable2(exposure_cycles)
I cannot define a many to many relationship as exposure_settings does not have a Fkey pointing outwards towards DimTable2 and FactTable.
I need to be able to browse exposure_amount at exposure_cycles level(pc_id)
I am able to get to the granularity of ps_id but not pc_id.
The granularity that I need is at the exposure_cycles. Please advice on this...
Note: Consolidating the Dimensions is not an option because of the huge amount of data involved.
TIA
November 4, 2016 at 8:06 am
From what you're describing, it sounds like Dim1 is the parent of both the Fact and Dim2, but the Fact and Dim2 have no relationship other than ps_id. If that's the case, how are you going to relate the fact table at the pc_id level? If you can work that logic out, then you can write an mdx expression for your measure. Providing some sample data and expected results would help.
November 4, 2016 at 8:28 am
Thanks Brian,
With a sql query by bringing all the tables together in a join, I am able to get exp_amount(fact table) broken down into exposure_cycles(dimension2). Why can I not get the same result in the Cube? This is the part I am trying to figure out.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply