Mike Austin-398977 (3/3/2014)
All,I have developed a cube that tracks the timeliness of certain steps in a business process. Steps are on time if the number of days between 2 dates is <= values that are stored in a table. The fact table for the cube is assigned a Timeliness dimension ID accordingly.
In addition, the users want to be able to play with the results by being able to dynamically enter a cutoff day value. To accommodate this, I have a column in the fact table, called "ElapsedDays" that is the difference between the two dates. My plan was to use this column with a parameter to re-calculate the timeliness dimension. The T-SQL logic would be as follows:
CASE
WHEN ElapsedDays <= @parameter THEN 1 -- On time
WHEn ElapsedDays > @parameter THEN 2 -- Late
ELSE 3 -- Unknown
END AS TimelinessDimID
In essence, I want to substitute my new dimension values for the old ones.
Yes, you can create a calculated member. Take a look at:
http://technet.microsoft.com/en-us/library/ms146017(v=sql.105).aspx
http://technet.microsoft.com/en-us/library/ms144841(v=sql.105).aspx
If you have the Adventure Works cube, you can try something like:
WITH MEMBER [Measures].x AS
CASE
WHEN [Measures].[Order Count] < 100 THEN 'Low'
WHEN [Measures].[Order Count] < 300 THEN 'Medium'
ELSE 'Large'
END
SELECT
{ [Date].[Calendar].[Month] } ON ROWS,
{ [Measures].[Order Count], [Measures].x } ON COLUMNS
FROM [Adventure Works];
Not exactly what you asked for -- but you should get the idea.
I hope this helps,
Rob