Design of fact table/cube. One to many problem.
-
Hi, I Need some help with my cube and how I should design the fact table and dimensions. I'm a beginner on OLAP/MDX so maybe it's a simple task I'm trying to do .... I've done some cubes that I access with MDX and return the result in an ASP application. The problem I have is how to handle records in the fact table that have a relation, a one to many relation, to a dimension. I'll try to explain with a simple example:
I have three tables in the database:
Customer:
CustomerID
SaleAmount
Active
CustomerGroups:
GroupID
Name
CustomersCustomerGroups:
CustomerID
CustomerGroupID
Table Customers consist of customer records with "measure" columns, this table is my "fact table". Table CustomerGroups consist of customer groups, CustomerGroup is a dimension. Table CustomersCustomerGroups consist of the relation between Customers and CustomerGroups, ie in this table a customer is connected to one or more groups. No dimension on this (maybe it should be that?).
When I do a cube of this, Customers will be the fact table, I have dimensions on CustomerGroups and Customers. I have included the table CustomersCustomerGroups in the cube and it's connected to table Customers (connection with CustomerID) and then I connect dimension CustomerGroups to the CustomersCustomerGroups table. My problem now is that this affects my result. If Customer 1 has a record in table Customers with SaleAmount 1000 AND belongs to two groups, ie two records in CustomersCustomerGroups, SaleAmount for Customer 1 will be 2000 if I browse my cube and that is not what I want, it should still be 1000. Just becasue that customer belongs to two groups he still has a sale amount of 1000.
The groups should only be used to set conditions on, maybe I only wants to see customers that belongs to group 1 and 4, then I should specify that in my MDX query. The result should be summerized on SaleAmount in Customer and should not be multiplied with the amount of times that the customer exists in table CustomersCustomerGroups.
Should I change my design or could a MDX statement solve this? Some kind of Distinct Count query??
Many thanks
C-J
-
This was removed by the editor as SPAM
-
I'm not sure if you want all of the customer groupings in one dimension. If a customer can belong to more than one group, and the dimension is a standard one, how would the cube allocate the measure values other than giving each instance of the customer the same value (ie what you are seeing now). But looking at your table relationships and fields, unless you can specify that sales were made to this customer when they were acting as part of a certain group, then it actually makes sense to allocate the same measure amount to each instance of the customer. i.e. If I am in the Australian and Male groups, and I have spent $1000, then I have spent $1000 as a member of Male and $1000 as a member of Australian.
Possibly you could break your groups up into multiple dimensions. The down-side on this approach is where do you stop -> allowing for a customer to be in 3 groups? 4? 5? This shouldn't be an issue if your groups are easily separated (Male/Female, Australia/USA/Canada, etc etc).
HTH,
Steve.
Steve.
-
If it were me, I would take a look at changing the design. If you think about it, being a male has nothing to do with being Australian...any more than being female has to do with being German or American, right? Essentially, your customer attributes (the groups they belong to) are not intrinsically interconnected. A customer could be an Australian male or an Australian female. So, if you developed a star schema with a fact table containing your measures and appropriate dimension key fields (and dimension tables)for gender, country, etc etc then you would have the ability for accurate reporting without jumping through design or mdx hoops as well as more flexible reporting(in my opinion). You can transform your normalized data in the loading process of your fact and dimension tables...
hth,
Michael
Michael Weiss
Michael Weiss -
Michael,
Keep in mind that those customer attributes were introduced by me (evil steve
), they may not actually exist! Steve.
Steve.
-
oh...well that might make a difference then! lol
Michael Weiss
Michael Weiss
), they may not actually exist! 
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply