Solved

data warehouse design and how to make relation between specific tables

Posted on 2011-09-15
25
341 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
ID: 36542621
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
ID: 36542656
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
ID: 36542676
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:RamzyNEbeid
ID: 36542688
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
ID: 36542738
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
ID: 36542826
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
ID: 36543246
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
ID: 36543370
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
ID: 36543402
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
ID: 36543657
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
ID: 36543665
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
ID: 36543683
what is the alias in T-SQL?
0
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 500 total points
ID: 36543751
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
ID: 36544081
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
ID: 36544387
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
ID: 36558532
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
ID: 36559700
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
ID: 36559737
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
ID: 36559820
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
ID: 36559835
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
ID: 36560681
please find attached my final data warehouse schema

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

Author Comment

by:RamzyNEbeid
ID: 36561629
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
ID: 36562243
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
ID: 36562377
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
ID: 36597413
you did help me more than i can imagine

so i want to say thank you very much.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

776 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