July 14, 2011 at 11:04 am
I'm fairly new at BI, I read a book, did some examples and built a data vault for all the operational data.
Now and creating my first real life Cube on top of that... I stuck on the following:
Data: I have premium values in my data vault that are valid through a period of time (start and end date)
Goal: I want to report what the premium values are at the beginning and end of each month, quarter or year.
I created a data mart with a premium fact table, premium component type & date dimension tables.
The current solution contains a start date key and an end date key to the date dimension table.
How do I create a calculated member that calculates the premium value for the first day in a selected period?
I only have a start date dimension and an end date dimension, so should I add a 3rd date dimension to select a transcending period?
Or is my fact table allready wrong and should a create one based on the date keys from the date dimension and totalize for each beforehand? (duplicating a lot of data and having the problem that not all other dimensions will be present for each date key)
I hope this can be done by an MDX query which I do not yet fully understand.... but I have no idea... thanx!
July 25, 2011 at 3:36 am
Ok, let's try another approach...
I have a fact table called [fact_component_premium]. It holds an premium amount [value], a [start_date_key] and an [end_date_key]. I also have a [dim_date] dimension with [date_key], [full_date], [day_of_year] and lots of other data. And finally a [dim_premium_component_type] dimension which holds the different types of premium components.
When browsing my cube I want to be able to see the amount of premium, for each component type, for the first day of a given period (e.g. month, quarter or year) and the amount of premium on the last day of that period.
How do I do this?
When creating the cube it automaticaly links 2 date dimensions to the [start_date_key] and [end_date_key], but I don't know how to use these for a period.
July 26, 2011 at 4:05 am
The dimension attributes should be in your date dimension. Using the Date Dimension, you would want to select Qtr, Month, Year. Maybe sure you setup the relationship between your start date and the date dimension correctly.
July 26, 2011 at 5:41 am
They are. The problem isn't the selection of the month, quarter or year belonging to the startdate OR enddate.
Example: a value has a startdate 1/1/2011 and an enddate of 1/1/2012.
With the date dimension linked to the startdate I would get a value for the first month, first quarter or the year 2011.
All the other months and quarters will be empty.
But, since the endate is 1/1/2012, the value should occur on every day, month and quarter between 1/1/2011 and 1/1/2012.
A solution I'm going to try on next is building a view on my facts table with this query:
SELECT DISTINCT DAT.date_key, FCP.start_date_key, FCP.end_date_key
FROM fact_component_premium FCP INNER JOIN
dim_date DAT ON DAT.date_key BETWEEN FCP.start_date_key AND FCP.end_date_key
Then create a measure group based on that and use it as an intermediate in a many-to-many relationship to link the calendar date dimension to the component premium facts.
July 27, 2011 at 6:33 am
Ok, that worked. I now have all the values spread out across the calendar.
How do I create a calculated member that shows the value for the first day in a selected member of the date dimension?
OpeningPeriod([Dim Calendar Date].[Calendar].[Date],
[Dim Calendar Date].[Calendar].CurrentMember), [Measures].[Value]
Something like this?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply