Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
slimasian
Starting Member
1 Post |
 Posted - 2007-07-17 : 13:06:45
|
| Hey all,I'm a bit of a newbie when it comes to SQL Server 2005 Analysis Services/Reporting Services. I last tooled around with SQL Server 2000 DTS with some Reporting Services for some very simple reports, not even cube design.I'm currently designing some cubes for usage at work (a bank), and ultimately the reports that will implement them. These monthly reports would measure how much is in an account for the given month, as well as the types of fees that are collected for each account. So, technically, the report would be showing two measures.My question is two prong, one related to cube design and the other the design of the report which would implement the cube:1) Cube Design: Based on what I said above, I had a few design ideas for the star schema...I was wondering which approach (if any) would be optimal?: a) Two Fact tables sharing dimensions -- one FeesFact and one AccountValueFact, both of which would share the same dimensions (Account Dimension, Business Unit Dimension, etc.). b) One Fact table (AccountFact) which would have a Fees dimension and an AccountValues dimension. [Not sure if this one even follows "proper" star schema]2) The report is a little cloogy...see below for an example...=================================================================Report for the month of July 2007 Manager Acct No. Date Value Tax Fees IA Fees Total Fees---------------------------------------------------------------------Business Unit 1 $400 $20 $100 $120--------------------------------------------------------------------- BOSTON $200 $10 $50 $60 M. Bolton 12345 7/1/07 $150 $5 $25 $30 J. Smith 67890 7/1/07 $50 $5 $25 $30 WICHITA $200 $10 $50 $60 M. Bolton 12346 7/1/07 $150 $5 $25 $30 J. Smith 67899 7/1/07 $50 $5 $25 $30 =================================================================Given the need of this report, would any of my star schema design ideas fit the bill?In terms of implementation, it seems like a pretty simple drill-down report that is just expanded by default, but then how do I create a cross-tabbed drill-down report using an MDX query? My train of thought says to create a drill-down that starts with the Business Unit dimension first, followed by Location dimension, then Manager dimension. Again, thanks for the help guys. Being in OLTP for so long, my OLAP skills are a bit lackluster... :-slim... |
|
|
|
|
|
|
|
Advice on Designing Cubes and Reports