May 2, 2013 at 3:20 am
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
May 3, 2013 at 6:44 am
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!
May 3, 2013 at 7:10 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply