Setting up design for Data Mart

I couldnt find the right Topics that I was looking for so I added this to MS SQL Server and Reporting Services. I would have liked to have found a Business Intelligence or Data Warehouse topic to put this under but I couldnt find anything related? If anyone has a suggestion on where to put this please let me know.

I am setting up my tables for a data mart that I have been tasked to create. I wanted to get some advice before I keep going the way I am going. First of all I have a DB that I am using as a source(some of the tables are in the pic I attached). I have come across a table that I need to create a Dimension table for. It has 3 columns defined as the Primary Key. Ive been taught to create a new Primary Key on every table and that will be the Foreign Key in my Fact table. Im curious what I should do with the existing keys as they are used in the detail table as well? I think this is a good spot for me to ask for help.
First I need to know what/how to relate the DIM_INSPECTION table to the FACT_VMIS table and then how to relate the DIM_INSPECTION to the DIM_INSPECTION_DETAIL table?
Also, am I doing the right thing as far as the overall setup of this?
Please help me with this. I have developed small datamarts before and everything has worked pretty good from what Ive done but this seems a little different so I want to ask for help...
The Dimesion tables I need help with are in yellow and the Fact table is in green in the pic I attached..
C--Users-VCISJMK-Desktop-Visio-Pics-Data
jknj72Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
You should start reading Kimball. Start with The Data Warehouse Toolkit.

The star schema uses single column keys from fact to dimension tables to make quering easier. The dimension is built during the ETL process. Depeding on the kind of dimension you may create addtional UNIQUE constraints, but they are not necessary in the star model. Also when your dimension has a temporal aspect you may have more than one row with the same transactional primary key.

At the first glance I cannot clearly guess the granularity of you fact table.
The fact table contains a vehicle key and the incident dimension a vehicle number. Sounds redundant.
I don't see a reason at the first glance, why inspection and inspection detail should be splitted.
0
jknj72Author Commented:
I understand what your saying but if the data that Im working with has columns in tables that seem redundant I dont feel that I should exclude the table from the design. If the Incident table has a vehicle number does that mean I should exclude the vehicle table? Also, Im not sure what you mean with the inspection and detail being splitted? Im kind of new to this but I do have an idea on how to set this up but I could def use some help so thanks
0
jknj72Author Commented:
Im sorry I didnt send the right pic of the diagram Im using. What you said makes sense now. Let me show you another pic of my Datamart. I realize there will be a lot of redundancy and Im not sure how to get away from that due to the design of the original database but thats not my biggest fear. I just want to know if I am on the right track and if I could get some tips on how to make this a better design I could use the help. The thing that I am most interested in is the snowflake portion of my schema. Am I creating the relations on things like the dimension tables that are connected to other dimension tables done correctly. Like my Inspection dimension is connected to my fact table but the inspection details is connected to the inspections dimension.. Is this done right ? I think it will make more sense when you see the diagram I attached....
Thanks for the helpC--Users-VCISJMK-Desktop-BusReports-Acce
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

jknj72Author Commented:
I also would like anyones advice on my measures. I havent included any measures in my Fact table. I was unsure exactly what should be going into the Fact table as far as measures go?
0
ste5anSenior DeveloperCommented:
Please read at least those two articles: Fact Tables and Factless Fact Tables.

Define the grain of your fact table. What does one fact row should express? Keep in mind, that under most circumstancs the fact table should be additive for all measures in it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jknj72Author Commented:
I have to tell you. I have built some small data marts a few years ago and figured I would be able to put this together for my new company. Its bigger as far as tables and there are much more reports to create. After just reading the first page of the Fact table article from Kimball has really helped greatly. Its all coming back to me now! I was having trouble trying to figure out my measures and to apply them into my Fact table and now I am on the right path to figuring this out and I appreciate it. Im gonna give you the points and try and figure this out on my own(any tips would help though ;)
Thanks for the help!
0
jknj72Author Commented:
thanks
0
ste5anSenior DeveloperCommented:
Yup. Dimensional modelling is not hard per se, but changing the mindset before is ;)

I always read some pages of Kimball before starting a new star schema. Otherwise the normal relational thinking would be to dominant.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.