July 20, 2016 at 11:06 am
I'm inexperienced with SSAS, and I am just ready to beat my head against the wall...
I have a CustomerOrder fact table with the following relevant columns..
factCustomerOrderKey ( identity )
ShipToCustomerKey -- FK
BillToCustomerKey -- FK
OrderNumber
LineItemNumber
.
.
.
I'm using this table as a degenerate dimensions, and I cant get the hierarchies straight.
I want to be able to have a hierarchy for BillTo Customers that would look like this:
BillToCustomer
ShipToCustomer ( the relation between BillTo and ShipTo is usually 1:1, can be 1:N )
OrderNumber
LineItemNumber ( with related LineItem Attributes )
When I define the attribute relationships, it looks like this:
Fact -- > OrderNumber - > ShipToCustomer - > BillToCustomer
Problem 1
I keep getting duplicate key errors when I try to process the dimension. At a generic level, what's the correct method for setting the key columns to avoid the duplicate keys? I'm thinking it should be like this:
BillToCustomer -- single column
ShipToCustomer -- BillTo and ShipTo
OrderNumber -- ShipTo and OrderNumber
I need some expertise and guidance... please
Problem 2
Can I also have a similar hierarchy that leaves out the BillToCustomer? It would look like this:
ShipToCustomer
OrderNumber
LineItemNumber
In general -- what's the best way to diagnose problems like this and design to avoid them??
ANY advice, knowledge, or even links to suggested reading would be very helpful
_____________________________
Past performance != future results.
All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...
July 20, 2016 at 11:47 am
Not saying it's the best or only way to do it, but, I'd be tempted to:
1. Create a dimension with Ship To and Bill To customers in it.
PK can be a surrogate but the natural key is the combination of BillTo-ShipTo
Hierarchy is like
Bill To
Ship-to (key is natural key or surrogate)
This then manifests itself in a UI, using the following as examples,
Bill To - Customer A
Ship To's Customer B and Customer C
Customer A drills down to
CustomerA-CustomerB
CustomerA-CustomerC
The upside, you can analyze by BillTo customer, andthen see where it all shipped to.
The downside, if ShipTo's are 'shared' between customers, you can't (easily) analyze by the SHipped To customer (ie Customer culd be a child of several other BIllTos).
The way to get around the downside - have three different 'customer' dims:
1. BillTo - uses normal BillTo FK on fact to PK on dim
2. ShpTo - uses normal ShipTo FK on fact to PK on dim
3. Bill_plus_ShipTo - ses the appraoch detailed above. To an extent, this dimension offers a superset of what the BillTo offers by itself.
Steve.
July 20, 2016 at 1:02 pm
Steve that makes a lot of sense.
So my original fact table where the columns were:
factID
BillToCustomer
ShipToCustomer
OrderNumber
LineItem
.
.
.
Would become:
factID
BillTo/ShipTo Combination
OrderNumber
LineItem
Your suggestion to handle the ship-to analysis means that the fact table could be:
factID
BillTo Customer Key
ShipTo Customer Key
BillTo/ShipTo Combination
OrderNumber
LineItem
Thank you for the suggestion!
_____________________________
Past performance != future results.
All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...
July 20, 2016 at 1:54 pm
Yes. Like i said though, the BillTo FK in the fact becomes superfluous though, because you can get to the analysis of BillTo simply by using the top level of the BillTo+ShipTo hierarchy.
A couple of things to watch for -
it looks like you're building the fact at the line level. While it's nice to have the order in the fact record, you could get to this via the LineID key , assuming that you have an Order dimension that has Order (being a parent of) Line in it.
do you really need a 'factid' - the combination of FK's technically is the unique identifier for the fact record. If you don't have a specific need for an identifier on the fact, don't add one.
Steve.
July 20, 2016 at 2:16 pm
stevefromOZ (7/20/2016)
Yes. Like i said though, the BillTo FK in the fact becomes superfluous though, because you can get to the analysis of BillTo simply by using the top level of the BillTo+ShipTo hierarchy.
That makes sense.
stevefromOZ (7/20/2016)
A couple of things to watch for -it looks like you're building the fact at the line level. While it's nice to have the order in the fact record, you could get to this via the LineID key , assuming that you have an Order dimension that has Order (being a parent of) Line in it.
I don't have an Order Dimension -- my thinking was that with an order dimension I'd have a dimension that was growing nearly as quickly as my fact table. So I decided to denormalize the order data into the fact table.
stevefromOZ (7/20/2016)
do you really need a 'factid' - the combination of FK's technically is the unique identifier for the fact record. If you don't have a specific need for an identifier on the fact, don't add one.
Thank you for pointing that out! I will review whether or not I need a factID.
Thank you again for the advice -- very helpful.
_____________________________
Past performance != future results.
All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply