customer address dimension design
-
Hi I am having problems with what should be an easy cube.
2 tables.
- Customer (key custid)
- Customer Address (key custid AND line #)
Problem 1. In the view designer, how do mark the primary key of the customer address table. Its
really custid and line#.
Problem 2. When i try creating the dimension for cust address i use custid as the KEY.
I try to make a heirchy of custid - line # so it would look like:
Cust 1
line 1
line 2
line 3
Cust 2
line 1
line 2
HOWEVER when the above example processes it looks like this:
Cust 1
Line 3
Cust 2
Line 2
Note even if i add unique key to the customer address table by combining the custid and line # i get
the same results.
If someone could help guide me in the right direction it would be greatly appreciated.
thanks in advance
-
Are you trying to use two tables in the dimension editor?
Why not combine the tables into a dimension table that has multiple levels:
Cust# Line#
1 1
1 2
1 3
2 1
2 2
-
Thanks for your relpy.
No I am only using the customer address table as the dimension and the customer table as the fact table (measure count customer id)
The customer address table is already in the format of:
Cust# Line#
1 1
1 2
1 3
2 1
2 2
-
So, in the dimension editor, level 1 should be set to Cust# and level 2 should be set to Line#.
With it setup that way, and after you do a full process on the dimenion, can you then browse it properly?
-
Ave,
What are you actually wanting to see in the final result? If that Cust_address table truly is a multiple line address per customer it seems a little strange to want to see the address details (Street Address, City, State and Zip) all as individual children of each Customer. 'Normally' (where is anything normal?) these tpyes of items would be rolled out in to individual fields so that you could then use them as levels within user hierarchies (e.g. drill down to customers from Country to State to City to Customer).
Going back to my initial question, even if you get the dimension to work as you're outlining, how do you want the measure/s to display? The count (of 1) for each customer will be correct at the customer level but what should be shown at the lower child levels? 1 would appear incorrect as this would then roll up to 3 for a given customer with 3 address detail lines.
Shoot through some more detail of what you're trying to achieve and we'll all try to help where we can
Cheers,
Steve.
-
If by level one you mean the top most level in the heirarcy then yes that is how it is set up.
Level 1 custid
Level 2 Line#
When i browse the heirarchy it only shows 1 line (the last line) for each custid. Example customer 1 has 3 lines. Browser shows:
Cust 1
Line3
Lines 1 and 2 are missing.
-
Can you also post what version (2000 or 2005) you're using?
Steve.
-
Hi Steve.....I am using SQL 2005.
Currently I can get a count of customers with the total number of lines (address lines)
IE:
1 - 5,000 (so there are 5,000 customers with just one address line)
2 - 100 (so there are 100 customers with 2 address lines)
3 - 40
4 - 5
I am looking for a way that an end user will also be able to use the cube to create a mailing list. So they should be able to drag the a heirarcy that will show Each Customer and Every address they have.
OR
Throw in a filter of say address line 1 and only show their first address listed.
OR
Show the total counts of for the a chosen state. IE for EVERY address how many have a state of CA.
Or how many and WHICH custmoers have a state of CA for line # 1.
All the examples i have seen (the MS adventure works db) have only ONE address per customer connecting to the customer table to the geographic table via geographic key.
Does this make more sense?
-
hmmmmmm
browse the geography dimension in adventure works datawarehouse.
wouldnt you expect to see multiple zip codes per city?
There is only one.
-
I take that back....i see dussledorf germany has 2 postal codes.
-
1st thing.... Analysis Services is not really designed to create a mailing list, so I would leave that out of your design considerations. A mailing list is a job for a relational report.
You may want to consider redesigning your dimension by maybe creating a State dimension.
This way, you can have a "Customer Address Count" measure, and when browsing a specific Customer, you will then be able to see how many addresses they have in each State. You will not be able to see the specific addresses unless you do a relational Drill Through.
All you would have to do to create this would be to add a field to your fact table for the Customer Address Count measure and place the number 1 in each row for each customer / line combination.
Does that make sense?
I don't know why your dimension is not being displayed properly without seeing the sourcedata for the dimension.
-
To David's point (don't use AS to generate a mailing list), i generally agree although witht he UDM in AS2K5, this is the type of relational info that is expected to be in any given dimension as a Dimensional Attribute I guess.
I think the problem you're encountering relates to your schema. Even if you specify the PK of the dim table to be both the ID and line#, you're fact table has only the ID (ie each transaction isn't at the address line grain). Either increase the granularity of your fact records or make the customer dimension both a dim and a fact. Or alternatively, judiciously use some filtering on an RS report and let your users create their mailing lists through RS.
Steve.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply