Newbie: I came up with a couple of Dim and Fact tables... what's next? stuck
All,
I'm new at Data Warehousing and been reading quite a bit about it, since I figured it's the way to go for my current project.
My data deals with hospitals and patients, so I want to be able to create reports on patient visits, admissions, discharges, etc by region, hospital, year, quarter.
I came up with a Dimension Table for Dates (where I have Year, Quarter, Month, Date, DayOfWeek, etc) and a second Dimension Table for Hospitals (where I have Region, State, City).
My Fact tables deal with the actual Patient Visits -- I have a table for Admissions, one for Discharges. Each row in these tables will include a DateId and a HospitalId that point to the above mentioned dim tables.
Now I want to work on reports for this data. My coworker will be using Reporting Services to write the reports and he wants me to write Stored Procedures that he'd run on the reports to get the data. I want to be able to report by Date (Year, Quarter, Month, Year to Date, Last Year, etc) and by Hospitals (in a region, city, state).
This is where I'm stuck... I'm not sure how to build the stored procs based on what parameters, and I can't find an example on the net that would help me. Obviously the parameters are the Date Range and the Hospital / City / State but I'm not sure how to tie it all together.
Any leads will be appreciated.
Thanks!
Answers
This is where I'm stuck... I'm not sure how to build the stored procs based on what parameters, and I can't find an example on the net that would help me. Obviously the parameters are the Date Range and the Hospital / City / State but I'm not sure how to tie it all together.
Any leads will be appreciated.
Thanks!
Follow the the sample below. It is easier than going against a transactional database since the data is already nicely pigeon-holed into dimension and fact tables.
The input parms are usually dimension filters.
-- Data warehouse stored procedure. USE AdventureWorks; GO CREATE PROC uspResellerSales @Month INT AS SELECT dST.SalesTerritoryRegion, dT.CalendarYear, @Month MonthNo, sum(fRS.ExtendedAmount) AS ExtendedAmountSum FROM FactResellerSales fRS INNER JOIN DimTime dT ON fRS.OrderDateKey = dT.TimeKey INNER JOIN DimSalesTerritory dST ON dST.SalesTerritoryKey = fRS.SalesTerritoryKey WHERE dT.MonthNumberOfYear = @Month GROUP BY dT.CalendarYear, dST.SalesTerritoryRegion ORDER BY SalesTerritoryRegion, CalendarYear GO EXEC uspResellerSales 7 GO /* SalesTerritoryRegion CalendarYear MonthNo ExtendedAmountSum Australia 2003 7 54329.4735 Canada 2001 7 115360.896 Canada 2002 7 642384.7523 Canada 2003 7 505758.1809 Central 2001 7 19243.0643 Central 2002 7 273688.0674 Central 2003 7 217933.6056 France 2002 7 98193.785 France 2003 7 55776.6572 Germany 2003 7 191192.4712 Northeast 2001 7 21598.3855 Northeast 2002 7 320999.809 Northeast 2003 7 192607.318 Northwest 2001 7 93905.6051 Northwest 2002 7 307712.6313 Northwest 2003 7 394114.064 Southeast 2001 7 139164.1943 Southeast 2002 7 186000.0114 Southeast 2003 7 178225.4437 Southwest 2001 7 100056.4335 Southwest 2002 7 500542.5254 Southwest 2003 7 783991.9571 United Kingdom 2002 7 82046.9992 United Kingdom 2003 7 155253.8781 */
Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com- Marked As Answer byMariano77 Monday, November 02, 2009 2:54 PM
All Replies
- Usually the report developers build the stored procedures they need. For you to be able to anticipate what they need is nonsense. Nevermind using sprocs in a DW environment for reporting is probably not the best idea, as it places the support of the reporting on the DBA/DW person, not the report developer. (At least the way you have described your situation.)
Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer - Unfortunately we had to temporarilly hire someone outside the company as there is no Reporting Services knowledge in the company (we are very small).
Before the DW option, I was feeding him (or his reports, I should say) Stored Procs that would spit out the data exactly the way the report ends up displaying it. So what he does is the design of the report, he doesn't deal with the data at all - as he's not familiar with the data.
But now that we changed strategies and decided to go with a DW approach, I'm not sure how to replicate what I was doing before, which is a Stored Proc that the report will run to get its data from Fact and Dimension tables.
Your situation requires someone who can write Reports using reporting services using Mdx. If I'm not mistaken, you're referring Fact and dimensional tables and store proc so you probably have Analysis services database up and running?
If you're still writing store procs and reports from the relational warehouse, your reporting services personnel should be able to write those store procs. In the company I work for, one person creates the relational warehouse + builts ETL packages + designs Analysis Databases + writes Custom mdx queries for the reporting services and Does data mining.
Working with Reporting Services is easy and user friendly. You should be expecting him to do that (store procs)
just my two cents
hth
Rok
This is where I'm stuck... I'm not sure how to build the stored procs based on what parameters, and I can't find an example on the net that would help me. Obviously the parameters are the Date Range and the Hospital / City / State but I'm not sure how to tie it all together.
Any leads will be appreciated.
Thanks!
Follow the the sample below. It is easier than going against a transactional database since the data is already nicely pigeon-holed into dimension and fact tables.
The input parms are usually dimension filters.
-- Data warehouse stored procedure. USE AdventureWorks; GO CREATE PROC uspResellerSales @Month INT AS SELECT dST.SalesTerritoryRegion, dT.CalendarYear, @Month MonthNo, sum(fRS.ExtendedAmount) AS ExtendedAmountSum FROM FactResellerSales fRS INNER JOIN DimTime dT ON fRS.OrderDateKey = dT.TimeKey INNER JOIN DimSalesTerritory dST ON dST.SalesTerritoryKey = fRS.SalesTerritoryKey WHERE dT.MonthNumberOfYear = @Month GROUP BY dT.CalendarYear, dST.SalesTerritoryRegion ORDER BY SalesTerritoryRegion, CalendarYear GO EXEC uspResellerSales 7 GO /* SalesTerritoryRegion CalendarYear MonthNo ExtendedAmountSum Australia 2003 7 54329.4735 Canada 2001 7 115360.896 Canada 2002 7 642384.7523 Canada 2003 7 505758.1809 Central 2001 7 19243.0643 Central 2002 7 273688.0674 Central 2003 7 217933.6056 France 2002 7 98193.785 France 2003 7 55776.6572 Germany 2003 7 191192.4712 Northeast 2001 7 21598.3855 Northeast 2002 7 320999.809 Northeast 2003 7 192607.318 Northwest 2001 7 93905.6051 Northwest 2002 7 307712.6313 Northwest 2003 7 394114.064 Southeast 2001 7 139164.1943 Southeast 2002 7 186000.0114 Southeast 2003 7 178225.4437 Southwest 2001 7 100056.4335 Southwest 2002 7 500542.5254 Southwest 2003 7 783991.9571 United Kingdom 2002 7 82046.9992 United Kingdom 2003 7 155253.8781 */
Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com- Marked As Answer byMariano77 Monday, November 02, 2009 2:54 PM
- Thanks Kalman, I had come up with a number of SPs that are similar to the one you pasted. Good to see I was on the right track!
