October 9, 2007 at 11:17 am
Background:
We have a data mart that describes purchases. To simplify it (greatly) for the purposes of this question, the fact is a Purchase, and the dimension we're interested in is the Customer. The FactPurchase table has a key to reference the DimCustomer table. Within that DimCustomer table is an Address attribute. During the ETL process a score is associated with that Address, to represent how accurately the User who entered the Purchase entered the Address. This is stored in DimCustomer as AddressScore.
The Problem:
What we're trying to do is have the DimCustomer as both a Fact and Dimension. This is straightforward. What we're also trying to do, is utilize Dimensions in the FactPurchase table to restrict the rows in what we'll call FactCustomer. This is because we want to rate the User who entered the Purchase as opposed to the User that entered the Customer. This is in addition to the other Dimensions in the FactPurchase table, such as Location, SaleDate, etc.
The Question:
Is this even possible in SSAS 2k5? I've tinkered with the Dimension Usage of the Measure Group, to no avail. The more I think about it, the more it seems like it might not be possible at all. I'm certainly loathe to start adding anything I want to "score" (Address, PhoneNumber, EmailAddress) to the FactPurchase table, but I'm also not excited about limiting the querying to only what Dimensions I add to FactCustomer (really DimCustomer).
If you've made it this far I thank you for your time,
Rick
Rick Todd
October 10, 2007 at 9:56 pm
hey Rick, I may not have fully understood the problem but tried to replicate what you'd described using some of the sample data from Adventureworks (and the adventure works DW). Basically have two fact tables and two other dims, not including the fact acting as a dim also. So Sales Header is a fact and dim. Sales Detail is a fact only. I have a date dim related to the Sales header fact. I have a product dimension related to the Sales Detail fact. By setting the Dimension Usages as
Measure Group - Sales Order Header ->related to dim Sales Order Header -> type = Fact
Measure Group - Sales Order Header ->not related to dim Product
Measure Group - Sales Order Header ->related to Dim Time -> type = Regular
Measure Group - Sales Order Detail ->related to dim Sales Order Header -> type = Regular
Measure Group - Sales Order Detail ->related to dim Product -> type = Regular
Measure Group - Sales Order Detail ->related to Dim Time -> type = Referenced (via Sales Order Header dim)
Like i said, not sure if i got it quite right but this does let me do things like put Products on rows, add a measure from the Details fact (say Qty) and then split/filter that measure based on the Time dimension, a dimension that's indirectly related to the Detail fact.
Steve.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply