Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Confusion on Facts and dimensions Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 3:20 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 5:45 AM
Points: 763, Visits: 840
Hey all,

I have just built a basic cube and all seems ok. I have just had my first confusion when it comes to design.

So lets say we have a system that has 100 clients that may or may not have orders.

So basically we would have a Fact table of orders. This would contain information about the order, client etc. Dimension would be the order value.

All cool there!

However from this table i couldnt count how many clients we have because:

1) the client would be duped on orders.
2) the client may not have an order.

Should i then have a separate FACT table for clients? Or even a separate Cube?

Also the product > order relationship is one to many - so should that be a different FACT table?

Many thanks for your time.

Dan
Post #1448735
Posted Friday, May 3, 2013 6:44 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:40 PM
Points: 804, Visits: 1,990
There's not enough here to be sure, but you need to determine what business process you are trying to represent, and then what factors are relevant to it. In this case, I think Orders is the business process. Customer and Product are two of the dimensions. You will also need a date dimension for the order date.

There's probably more you can capture. I would suggest you read Ralph Kimball's The Data Warehouse Toolkit, a source that has been recommended many times by many people on this site. Proper modeling is essential in a successful BI solution. Software will not cover for it.

Good luck!



Post #1449170
Posted Friday, May 3, 2013 7:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 2:14 AM
Points: 4, Visits: 31
If I understand you question right, client has to be a dimension

Since your fact may or may not have have captured orders from all clients, it is not possible to get a reliable value for count of all clients from the fact.

to find the number of clients with orders-> you have to query the fact.
to find the number of clients->you have to query the clients dimension.

to find the number of clients by joining the dimension and fact use a left join on client dimension.
Post #1449181
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse