Go Premium for a chance to win a PS4. Enter to Win


Setting up design for Data Mart

Posted on 2014-11-03
Medium Priority
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..
Question by:jknj72
  • 5
  • 3
LVL 36

Expert Comment

ID: 40421114
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.

Author Comment

ID: 40421401
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

Author Comment

ID: 40421497
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.


Author Comment

ID: 40421498
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?
LVL 36

Accepted Solution

ste5an earned 2000 total points
ID: 40424395
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.

Author Comment

ID: 40428501
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!

Author Closing Comment

ID: 40428502
LVL 36

Expert Comment

ID: 40428612
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.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

824 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