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 |
|
cidr
Posting Yak Master
207 Posts |
 Posted - 2012-11-11 : 15:57:17
|
| Hello,I am currently working on a reporting database. Each table is based on a SharePoint list which is frequently updated to the SQL database.I have been tasked with developing SSAS cubes with this data. However, due to design restrictions I may not be able to develop a physical dimensional database using Kimball methodology.Therefore, I'd like to ask if SQL views are a viable alternative to replicate a physical dimensional - Star/Snowflake - schema?Below are some considerations:1. There are only around 30 tables in the database.2. A table will have no more than 5000 rows at any given time so there is not a lot of data3. Interger keys will be used as table referencesDo you think it may be possible to use views for this?Any ball-park advice or futher considerations would be very helpful.Thanks |
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2012-11-18 : 22:42:53
|
| I've gone the other direction...I started off with views on an 3NF database in order to give users the ability to run reports more easily. It turned out to be a nightmare...there are tons of performance issues and it was very difficult to get everything to the same grain that we run into cartesian product from the snowflaked tables that would normally be conformed dimensions in a star schema that we decided to scrap the view idea and go with a dimensional model. You may have better luck creating views, but some of the hurdles were things like not being able to parameterize the views, so the view would look up every record first, then the user's filters would take effect.I haven't completed a DM in this environment yet, but what testing I've done has been far more productive than the views so far. I DO think it's possible to approach what you're trying to do with views, you might also consider sprocs as you can add parameters to them. |
 |
|
|
|
|
|
Views to replicate dimensional database