Measure between dates in Dimension ( SSAS )

Question

Measure between dates in Dimension ( SSAS )

savioceg

Right there with Babe

Points: 795
More actions
January 15, 2013 at 5:41 am

#151166

I have a data warehouse with the following dimensions ( say )
1.Dim Time
2.DimensionX
and a Fact table
The sample design as follows
DimensionX ( Key, Name, Start Date , End Date ) - This is related to Time Dimension.
DimTime ( Key , FullDate , Year,Month etc )
FactTable:
Timekey ( Refers Time Dmension )
Measure1 ( Decimal )
I have to find out the Measure1 which happened between start and end date of the DimensionX .
Could you please let me know how to proceed with the same ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

create two dim one for start and one for end using the dimtime table map this table to dim x , then map these to the fact table.

Jayanth Kurup[/url]

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

Need a little more info (like how is DimX related to the time dimension, doesn't appear to be any PK-FK relationships there).

Having said that, if DimX is in reality something like 'Promotion', then I'd treat the date ranges (start/end) as attributes of the promotion (rather than true dates) and simply ensure that the Promotion (DimX) PK is a FK in the fact table. Then, if you had a pivot with Measure1 on it, you could view the results by any given member of DimX ("promotion"). In general, it doesn't appear like you're trying to find the value of a measure between two dates - you're trying to find the value of a measure associated with a non-time based entity, that happens to have start and end dates as attributes.

Steve.

savioceg Right there with Babe Points: 795 More actions · Answer 3

Hi Steve,

Let me give a little more specifics. I have the dimension table named campaign ( these are marketing campaigns for a product )

each campaign has a start date and end date . I have a time key column in the dimension table which will refer to the time dimension ( time dimension has a time key as the primary key which is an integer )

and Fact table has the same timekey which refers to the time dimension as well .

Let me know if it needs more clarity.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

Thinking on this a little more, I think you may have a Classic many-to_many, as defined by Marco Russo et al (download their whitepaper here[/url]).

If you look at their example, where they have a bridging table for Customer to Account (where each Customer can have multiple accounts), I could see you using a bridging table for Campaign to Date, where each Campaign is related to many Dates (ie all of the dates between the start and end). While this approach would mean you'd have to generate date FK id's for all dates for all campaigns, it *should* give you what I think you're looking for - wich is, let me filter to a campaign, and have it filter the sales total by subsequently filtering to the dates for which tha campaign was active.

good luck and HTH,

Steve.