Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

data warehouse design and how to make relation between specific tables

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
RamzyNEbeid
Asked:
RamzyNEbeid
  • 15
  • 10
10 Solutions
 
Christopher GordonSenior Developer AnalystCommented:
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
 
RamzyNEbeidAuthor Commented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
RamzyNEbeidAuthor Commented:
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
 
RamzyNEbeidAuthor Commented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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
 
RamzyNEbeidAuthor Commented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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
 
RamzyNEbeidAuthor Commented:
i will add 5 Dates in the FactSales Table
and i only have 1 DimDate table
how can i solve this?
0
 
RamzyNEbeidAuthor Commented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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
 
RamzyNEbeidAuthor Commented:
what is the alias in T-SQL?
0
 
Christopher GordonSenior Developer AnalystCommented:
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
 
RamzyNEbeidAuthor Commented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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
 
RamzyNEbeidAuthor Commented:
i want to ask you about my design is it a good design professional one, for me as a beginner?
0
 
Christopher GordonSenior Developer AnalystCommented:
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
 
RamzyNEbeidAuthor Commented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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
 
RamzyNEbeidAuthor Commented:
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
 
RamzyNEbeidAuthor Commented:
please find attached my final data warehouse schema

if you please give me your feedback Sales-Data-Warehouse.vsd
0
 
RamzyNEbeidAuthor Commented:
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
 
RamzyNEbeidAuthor Commented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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
 
RamzyNEbeidAuthor Commented:
you did help me more than i can imagine

so i want to say thank you very much.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 15
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now