jknj72
asked on
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
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
ASKER
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
ASKER
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 help
Thanks for the help
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Thanks for the help!
ASKER
thanks
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.
I always read some pages of Kimball before starting a new star schema. Otherwise the normal relational thinking would be to dominant.
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.