Solved

Setting up design for Data Mart

Posted on 2014-11-03
8
191 Views
Last Modified: 2014-11-07
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
0
Comment
Question by:jknj72
  • 5
  • 3
8 Comments
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
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
 

Author Comment

by:jknj72
Comment Utility
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
 

Author Comment

by:jknj72
Comment Utility
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
 

Author Comment

by:jknj72
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 32

Accepted Solution

by:
Stefan Hoffmann earned 500 total points
Comment Utility
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
 

Author Comment

by:jknj72
Comment Utility
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
 

Author Closing Comment

by:jknj72
Comment Utility
thanks
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now