December 2, 2011 at 1:58 pm
Hi All,
We have one fact table which has two dates 1)Admission Date key 2) DischargeDate key
and i need to enhance our cube such that user can drill down the report till the lowest level of date..
To give an example.
Lets assume there is fact table with below reocrds:
Id AdmissionKey DischargeKey
1 12/2/2011 12/3/2011
2 15/5/2010 05/6/2010
and the report should look like this
Date Count of days
2011 - 31
2011- Feb 18
2011- Feb -12 1
2011- Feb -13 1
2011- Feb -14 1
..
..
2011- March 12
---
--
---
I am not sure how to implement this....
Please give me some suggestions..
Regards
Krishna
December 3, 2011 at 7:14 pm
The following should help quite a bit. As usual with my code, the details are in the comments. Please let me know if you find it useful or not because about the only thing I know about SSAS is that it's a four letter word. 😀 I'm not sure how to shoe-horn a stored procedure into SSAS.
--===== Do this testing in a nice, safe place that everyone has
USE tempdb
;
GO
--=======================================================================================
-- First things first. We need to build a permanent Tally Table to replace some
-- WHILE loops and Recursive Counting CTE's to solve this problem. DO NOTICE that
-- the Tally Table I'm using for this problem starts at zero.
--
-- Please see the following URL for why Recursive Counting CTE's should be avoided.
-- http://www.sqlservercentral.com/articles/T-SQL/74118/
--
-- Please see the following URL for how a Tally Table works to replace certain kinds
-- of loops (like the ones we'd normally need to solve this problem)
-- http://www.sqlservercentral.com/articles/T-SQL/62867/
-- Jeff Moden
--=======================================================================================
--===== Create and populate the Tally table on-the-fly.
-- Good for more than 30 years of dates.
SELECT TOP 11001
IDENTITY(INT,0,1) AS N
INTO dbo.Tally
FROM Master.sys.ALL_Columns ac1
CROSS JOIN Master.sys.ALL_Columns ac2
;
--===== Add a CLUSTERED Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
;
GO
--=======================================================================================
-- Create the test data.
-- Nothing in this section is a part of the solution.
-- We're just creating test data here to demonstrate the solution with.
--=======================================================================================
--===== Change the date format to accomodate the OP's country of origin.
SET DATEFORMAT 'DMY'
;
--===== Conditionally drop the test table to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#OneFactTable','U') IS NOT NULL
DROP TABLE #OneFactTable
;
GO
--===== Create and populate the test table on-the-fly.
SELECT ID = d.Col1,
AdmissionKey = CAST(d.Col2 AS DATETIME),
DischargeKey = CAST(d.Col3 AS DATETIME)
INTO #OneFactTable
FROM ( --=== Original data from the post (mdy date format)
SELECT 1,'12/2/2011','12/3/2011' UNION ALL
SELECT 2,'15/5/2010','5/6/2010' UNION ALL
SELECT 3,'29/12/2010','3/1/2011' UNION ALL --I added this to span a year.
SELECT 4,'12/2/2008','12/3/2008' UNION ALL --This spans a Leap Year February
SELECT 4,'29/12/2008','3/1/2009' --and a year.
) d (Col1, Col2, Col3)
;
GO
--=======================================================================================
-- Now, we'll create a stored procedure that accepts an ID and creates the
-- drill down structure. I don't know how to use Analysis Services to use the
-- data, but all the data and some "control" columns are available.
--=======================================================================================
CREATE PROCEDURE dbo.DrillDownByID
--===== Declare a parameter for the ID we want to drill down on
@pIdToDrill INT
AS
--===== Supress the auto-display of rowcounts to prevent false error returns to the app.
SET NOCOUNT ON
;
--===== Create the result set for an external program to process as a Drill Down menu.
-- It includes a drill-down level column and a display order column.
WITH
cteGenDates AS
( --=== Create all the dates between the AdmissionKey and the DischargeKey
-- for the given ID using the "Tally Table".
SELECT TheDate = DATEADD(dd,t.N,oft.AdmissionKey)
FROM #OneFactTable oft
CROSS JOIN dbo.Tally t
WHERE t.N <= DATEDIFF(dd, oft.AdmissionKey, oft.DischargeKey)
AND oft.ID = @pIdToDrill
AND oft.AdmissionKey <= oft.DischargeKey
),
cteSplitdates AS
( --=== Split the dates into (ugh!) indivdual columns so we can ROLLUP each column
SELECT Year = YEAR(TheDate),
Month = MONTH(TheDate),
Day = DAY(TheDate)
FROM cteGenDates
),
cteTotals AS
( --=== Do the totals with a ROLLUP (subtotal) on each column break
SELECT Year,
Month,
Day,
TheCount = COUNT(*)
FROM cteSplitDates
GROUP BY Year, Month, Day WITH ROLLUP
)
--===== Format the final output.
SELECT TheDate = ISNULL(
+ RIGHT(Year,4)
+ ISNULL('-' + LEFT(DATENAME(mm,DATEADD(mm,Month-1,0)),3),'')
+ ISNULL('-' + RIGHT('0'+DATENAME(dd,DATEADD(dd,Day -1,0)),2),'')
,'Total'),
TheCount,
DrillDownLevel = ISNULL(SIGN(Year),0)
+ ISNULL(SIGN(Month),0)
+ ISNULL(SIGN(Day),0),
DisplayOrder = ROW_NUMBER() OVER (ORDER BY Year, Month, Day)
FROM cteTotals
;
GO
--===== Then, we can use the stored procedure to return a drill-down structure to the app,
-- whatever it is.
EXEC dbo.DrillDownByID 4 -- This number is an "ID" from the table.
;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2011 at 7:37 pm
You can do this quite easily using a tally table; read Tally Tables[/url] if you are not familiar with these.
It's easy if the periods for each Id can't cross year boundaries, but the query is a little more complex if they can.
The basic idea is to use a join with the tally table to allow days to be listed.
edit: I see Jeff replied while the original post was sitting in my queue - ignore me, my reply just referred you to Jeff's article.
Tom
December 5, 2011 at 4:23 pm
Hi Jeff,
Thanks for your reponse and the code...
However becuase of some design constraints...i have to implement the logic at the Datawarehouse level and i dont want to create extra rows for each date/month/year..because of performance issues while processing the cube...
But i learned importance of Tally tables..from you...:-)
Thanks for your time again.
Regards
Krishna
December 5, 2011 at 7:48 pm
Not sure how you're going to get all of the dates anywhere, then, Krishna. And generating the dates using the Tally Table aren't really going to take but an instant. They have to come from somewhere. Do you, perhaps, have a "calendar" table somewhere?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 6, 2011 at 12:09 pm
Hi Jeff,
I just modified your code little bit to get count at the month level instead of day level and wants to drill down day level at report instead of cube , however i need one suggestion from you....
In code you mentioned one SP where we have to pass the ID ..
Is there anyway we can get the count with out looping through IDs...need for all the IDs in a table....Using Tally Tables...
I understood we have to reset the Tally table for each ID from our..#OneFactTable'.
To give more details
EXEC dbo.DrillDownByID 3 -- This number is an "ID" from the ..#OneFactTable'. ----Instead of passing ID Is there anyway we can hardcode the Column name in the Stored procued itself...so it will populate the data for all the IDs in single shot
Please let me know if am not clear with my question..
FYI: Modified Code is below
--===== Do this testing in a nice, safe place that everyone has
USE tempdb
;
GO
--=======================================================================================
-- First things first. We need to build a permanent Tally Table to replace some
-- WHILE loops and Recursive Counting CTE's to solve this problem. DO NOTICE that
-- the Tally Table I'm using for this problem starts at zero.
--
-- Please see the following URL for why Recursive Counting CTE's should be avoided.
-- http://www.sqlservercentral.com/articles/T-SQL/74118/
--
-- Please see the following URL for how a Tally Table works to replace certain kinds
-- of loops (like the ones we'd normally need to solve this problem)
-- http://www.sqlservercentral.com/articles/T-SQL/62867/
-- Jeff Moden
--=======================================================================================
--===== Create and populate the Tally table on-the-fly.
-- Good for more than 30 years of dates.
SELECT TOP 11001
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.sys.ALL_Columns ac1
CROSS JOIN Master.sys.ALL_Columns ac2
;
--===== Add a CLUSTERED Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
;
GO
--=======================================================================================
-- Create the test data.
-- Nothing in this section is a part of the solution.
-- We're just creating test data here to demonstrate the solution with.
--=======================================================================================
--===== Change the date format to accomodate the OP's country of origin.
SET DATEFORMAT 'DMY'
;
--===== Conditionally drop the test table to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#OneFactTable','U') IS NOT NULL
DROP TABLE #OneFactTable
;
GO
--===== Create and populate the test table on-the-fly.
SELECT ID = d.Col1,
AdmissionKey = CAST(d.Col2 AS DATETIME),
DischargeKey = CAST(d.Col3 AS DATETIME)
INTO #OneFactTable
FROM ( --=== Original data from the post (mdy date format)
SELECT 1,'12/2/2011','12/3/2011' UNION ALL
SELECT 2,'15/5/2010','5/6/2010' UNION ALL
SELECT 3,'29/05/2010','3/1/2011' UNION ALL --I added this to span a year.
SELECT 4,'12/2/2008','12/3/2008' UNION ALL --This spans a Leap Year February
SELECT 5,'29/12/2008','3/1/2009' --and a year.
) d (Col1, Col2, Col3)
;
GO
--=======================================================================================
-- Now, we'll create a stored procedure that accepts an ID and creates the
-- drill down structure. I don't know how to use Analysis Services to use the
-- data, but all the data and some "control" columns are available.
--=======================================================================================
ALTER PROCEDURE dbo.DrillDownByID
--===== Declare a parameter for the ID we want to drill down on
@pIdToDrill INT
AS
--===== Supress the auto-display of rowcounts to prevent false error returns to the app.
SET NOCOUNT ON
;
--===== Create the result set for an external program to process as a Drill Down menu.
-- It includes a drill-down level column and a display order column.
WITH
cteGenDates AS
( --=== Create all the dates between the AdmissionKey and the DischargeKey
-- for the given ID using the "Tally Table".
SELECT TheDate = DATEADD(month,t.N,DATEADD(MONTH, DATEDIFF(MONTH, 0, oft.AdmissionKey), 0))
,oft.ID
FROM #OneFactTable oft
CROSS JOIN dbo.Tally t
WHERE t.N <= DATEDIFF(month, oft.AdmissionKey, oft.DischargeKey)
AND oft.ID = @pIdToDrill ----- ******want to replace...@pIdToDrill with tables.......***********
AND oft.AdmissionKey <= oft.DischargeKey
),
cteSplitdates AS
(
SELECT
TheDate,
ID ,
----- This is to count number of days...in a month......
CASE WHEN MONTH(TheDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(TheDate) IN (4, 6, 9, 11) THEN 30
WHEN (YEAR(TheDate) % 4 = 0 AND YEAR(TheDate) % 100 != 0) OR (YEAR(TheDate) % 400 = 0) THEN 29
ELSE 28
END As 'CountOfDays'
FROM cteGenDates
)
SELECT * FROM cteSplitdates
GO
EXEC dbo.DrillDownByID 3 -- This number is an "ID" from the table.
December 7, 2011 at 4:19 am
krish.sett (12/6/2011)
Hi Jeff,I just modified your code little bit to get count at the month level instead of day level and wants to drill down day level at report instead of cube , however i need one suggestion from you....
In code you mentioned one SP where we have to pass the ID ..
Is there anyway we can get the count with out looping through IDs...need for all the IDs in a table....Using Tally Tables...
I understood we have to reset the Tally table for each ID from our..#OneFactTable'.
To give more details
EXEC dbo.DrillDownByID 3 -- This number is an "ID" from the ..#OneFactTable'. ----Instead of passing ID Is there anyway we can hardcode the Column name in the Stored procued itself...so it will populate the data for all the IDs in single shot
Please let me know if am not clear with my question..
That's quite a bit different from the original request. Before we do this for all IDs (which isn't difficult), what do you actually want for a drill down structure? Are you simply looking for the number of days for each patient's visit? A quick listing of what you actually want would be really helpful.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 7, 2011 at 2:35 pm
HI -
You can build a date hierarchy in a SSAS cube. You need a date dimension table, which is essentially a calendar table, as Jeff mentioned. From there as you are building the cube you define your hierarchy (year> Quarter > Month > week > day ...usually) and name it like calendar year ( there are fiscal ones as well). Now you can slice (filter) and dice (group) any measure that has a date as long as you associate that measure to this dimension and then using the hierarchy for the drill down. Bottom line : SSAS can do this for you.
----------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply