Solved

data warehouse design and how to make relation between specific tables

Posted on 2011-09-15
25
339 Views
Last Modified: 2012-08-14
Dear all
I am working on my data warehouse with start schema structure it is retial sales
I have my Fact table is “Fact Sales”
I have a target Header and Target details in my OLTP database.
I want to add the targets tables to my warehouse database
So my idea of thinking is the following:
•      I will create a new table “Dim Targets” and I will relate this table to “Fact Sales”
•      I will create a new table “DimTargetDetails” and I will relate this table to “Dim Targets”
As far as my understanding that this structure is called snowflake design?
Is there any other way that I can avoid the snowflake design?
Is this a good design or bad one?
Thanks,
0
Comment
Question by:RamzyNEbeid
  • 15
  • 10
25 Comments
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 500 total points
Comment Utility
Yes, creating relationships between DIM tables is snowflake.

If you want to avoid snowflake, combine DimTargets & DimTargetDetails into a single table to be used a single dimension.  The DimTarget information will just repeat for each different DimTargetDetails item.
0
 

Author Comment

by:RamzyNEbeid
Comment Utility
if i combine them this is not a bad practice righ ?

if i used it as a snow flake am i going to face problems in my Cube?

i want you to know that all DIM tables data are not so big the max table is 20000 row
but the FACT tables will take unlimited number of rows

i think that snowflake is more easy to understand?

can i send you the whole layout in attachement, i will be very thankful if you can advie me if something i did wrong

thanks
0
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 500 total points
Comment Utility
Combining / "denormalizing" data is not bad design for a Data Warehouse.  In fact I'd encourage it in your dimension tables :)  

Normally, your FACT table will contain few columns (measure & fields to create relationships with DIM tables) and TONS/TONS/TONS of rows.

Normally, your DIM table will contain TONS of columns and less rows (significantly less when compared to the FACT table).

IMO, I think that snowflake is not as easy to understand as it requires additional relationships between DIM tables.  
0
 

Author Comment

by:RamzyNEbeid
Comment Utility
thanks,  
please find attachd Sales-Data-Warehouse.vsd that contains all the structure

please advice me if you have any comments
it is for Retail Sales.



Sales-Data-Warehouse.vsd
0
 

Author Comment

by:RamzyNEbeid
Comment Utility
Let me ask you something else
I have “DimSite” table and this table is linked to “Fact sales” table
I have another table “DimTarget” which have a column “SiteID”
Do I need to make any relation between the 2 tables “DimSite” and “DimTarget” ?
Or I will leave it like this?
“SiteID” column here in table “DimTarget” will be equivalent to “SiteKey” or “SiteAlternateKey” in table “DimSite”?
0
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 500 total points
Comment Utility
Assuming there is a logical relationship between DimTarget & DimSite:

Following closer to the snowflake model, I'd create a relationship between the two dimensions.

Following closer to the star model, I'd combine the two tables into a single table. (This is probably how I'd handle the scenario)

Note:  Neither of the approaches is technically incorrect (depending on who you ask) Star vs. Snowflake.
0
 

Author Comment

by:RamzyNEbeid
Comment Utility
every new month i have new targets for all Sales Rep.

ths means that the DimSite table will be duplicated every month since we have new targets every month ?
0
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 500 total points
Comment Utility
In that case, they are obviously separate dimensions.  I can only make assumptions on your business requirements :)  

DIMTarget is almost sounding like a fact table onto itself.
0
 

Author Comment

by:RamzyNEbeid
Comment Utility
i will add 5 Dates in the FactSales Table
and i only have 1 DimDate table
how can i solve this?
0
 

Author Comment

by:RamzyNEbeid
Comment Utility
dear gohord
i did the idea you said concerning adding Fact table for the Target

please i need your help on the above question.

thanks,
0
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 500 total points
Comment Utility
I'd use separate Time Dimensions.  This will allow you to filter the FACT table differently based on filter criteria.  You can use an alias in T-SQL to handle this, I forget how SSAS handles this.
0
 

Author Comment

by:RamzyNEbeid
Comment Utility
what is the alias in T-SQL?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 500 total points
Comment Utility
A simplistic example, assuming you have a fact table analyzing invoices that you'd like to see what was received and paid in 'JULY' (which would exist as a field in your time dimension)

Alias = TimeAlias1 and TimeAlias2 (so you don't create actual physical tables for each DIM)

select *
from dbo.myFACTTable fact

inner join dbo.TimeDim TimeAlias1 on
 TimeAlias1.TimeId = fact.PaymentTimeId

inner join dbo.TimeDim TimeAlias2 on
 TimeAlias2.TimeId = fact.InvoiceTimeId

where TimeAlias2.Month = 'June' and TimeAlias1.Month = 'June'

Note: I'll be offline until later this afternoon.


0
 

Author Comment

by:RamzyNEbeid
Comment Utility
Yes I know that
But I want to make it physical from the database
So how can I accomplish this?
I have an idea I want to tell you about if I make many views from the same table “DimDate” and then make I created this relation would it work? Also what about performance?


0
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 500 total points
Comment Utility
Yes, you could create many views of the same Time table if you'd like (rather than use an alias).  I've never found a scenario where I've needed to create multiple phyiscal Time Dimension tables.

I wouldn't be considered about performance since you should be joining the tables based on a surrogate key and not the date field itself (does this make sense?).  Put an index on the PK in the TimDIM and the surrogate key representing time in the fact table.  

Also, it the most appropriate design regardless of performance.  (multiple Time dimensions for your multiple time fields in FACT table.
0
 

Author Comment

by:RamzyNEbeid
Comment Utility
i want to ask you about my design is it a good design professional one, for me as a beginner?
0
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 500 total points
Comment Utility
You might want to start a bit simpler if you are just starting out.  For example, a "Data Mart" is a subset of a larger data warehouse that is meant to answer specific questions about the business.  Get a successful "Data Mart" up and running and them move on to another.

A good way to target what your first "Data Mart" should represent is to ask the business what is the most important question that you'd like to ask of your data.  Based on this one question, you *should* be able to create a single FACT table with 2-4 dimension tables to allow the business to slice and dice content.

This will also give you a good prototype on how the business will be consuming information in your Data Warehouse.  It will answer questions like (note: I'm not asking you these questions, just stuff to think about):

Will this content be loaded into SSAS?  
Will power users be using Excel Pivot Tables for analysis?  
Will you be developing reports off of the warehouse?  
How will the reports be deployed to power users?  
Do you have/need a third party tool for consuming the Data Warehouse?  

Good luck!



0
 

Author Comment

by:RamzyNEbeid
Comment Utility
I am impressed of what you said,
All what I am doing now is a proof of concept I mean I am trying to make everything in my data warehouse but I will not use all these tables at once.
Will this content be loaded into SSAS?  YES
Will power users be using Excel Pivot Tables for analysis?  YES
Will you be developing reports off of the warehouse?  YES
How will the reports be deployed to power users?  In the Reports Server SSRS
Do you have/need a third party tool for consuming the Data Warehouse?  I don’t what do you mean by this?

Thanks again, but can you tell me is what I did is good or bad professional or not etc…?
0
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 500 total points
Comment Utility
It's tough to answer the question, "good or bad or professional" without having a deep understanding of your business requirements.  There are a LOT of things going on that I like:

1. Your using a TIME Key in your fact table instead of the date itself (very good design choice)
2. The relationships between your FACT tables and DIM tables all seem logical and sound.
3. Your FACT tables do not have an excessive amount of measures.
4. I don't see a lot of DIM snowflake design.

As for things I don't like, again, I'd have to fall back on the fact that it's tough to be critical of your design without a deep understanding (which would require too much time for a site like this).  



0
 

Author Comment

by:RamzyNEbeid
Comment Utility
thanks thanks thanks thanks thanks thanks thanks thanks thanks
thanks thanks thanks thanks thanks thanks thanks thanks thanks
thanks thanks thanks thanks thanks thanks thanks thanks thanks
thanks thanks thanks thanks thanks thanks thanks thanks thanks
thanks thanks thanks thanks thanks thanks thanks thanks thanks
thanks thanks thanks thanks thanks thanks thanks thanks thanks
0
 

Author Comment

by:RamzyNEbeid
Comment Utility
please find attached my final data warehouse schema

if you please give me your feedback Sales-Data-Warehouse.vsd
0
 

Author Comment

by:RamzyNEbeid
Comment Utility
dear gohord

In my data warehouse as attached.
The DimBudget table contains a field “Channel(Customer)”
This column is exists in the DimCustomer table.
If I want to relate these 2 tables to each other then I will create a new table “DimChannels” and then relate it to DimCustomer and to DimBudget
Tell me how to think.
0
 

Author Comment

by:RamzyNEbeid
Comment Utility
I am creating my new data warehouse for retail sales.
I have 2 fact tables “FastSFASales” and “FactTarget”
I have Item Hierarchy of product (Item / brand / family)
The FactSFASales are using Item as its granularity
Mean while FactTarget are using Brand as its granularity
So what I did is that I excluded the Brand and Family from DimItem to be on sperate DimFamily and DimBrand to server 2 facts tables.
Is there any other solution that will help me avoid the snowflake design?
Thanks,

0
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 500 total points
Comment Utility
OK,

So your original product DIM consisted of a hierarchy of Item / Brand / family.  You wanted to use this DIM to analyze two separate FACT tables, one at the granularity of Brand, and another at the granularity of Item.  

In order to create this relationship, you've decided to break out "Item" from the product DIM into it's own separate DIM leaving you with a Product DIM with Item and a Brand DIM.

Again, nothing wrong with creating relationship between the separate DIM (thus snowflaking), but if it were me, I'd leave your original Item / Brand / Family DIM and create a new Brand / Family DIM to analyze the additional FACT table at the Brand granularity.  One less JOIN to deal with if your hitting the SQL Server directly (outside of SSAS).  
0
 

Author Comment

by:RamzyNEbeid
Comment Utility
you did help me more than i can imagine

so i want to say thank you very much.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

19 Experts available now in Live!

Get 1:1 Help Now