SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Views to replicate dimensional database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cidr
Posting Yak Master

United Kingdom
207 Posts

Posted - 11/11/2012 :  15:57:17  Show Profile  Reply with Quote
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 data
3. Interger keys will be used as table references

Do you think it may be possible to use views for this?

Any ball-park advice or futher considerations would be very helpful.

Thanks

Edited by - cidr on 11/11/2012 15:58:18

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 11/18/2012 :  22:42:53  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000