May 7, 2009 at 12:12 pm
I have 3 tables:
FactTransaction
SaleDateKey
CustomerKey
DollarAmount
DimDate
DateKey
Date
DimCustomer
CustomerKey
CompanyCustomerKey
You can probably see how they relate from the keys, but I'll spell it out anyway:
FactTransaction is the fact table, relating to DimDate via FactTransaction.SaleDateKey = DimDate.DateKey. It also relates to DimCustomer via FactTransaction.CustomerKey = DimCustomer.CustomerKey
DimCustomer also contains a Parent-Child relationship on CompanyCustomerKey may refer to another DimCustomer entry via CustomerKey with the max depth being 1.
I have 2 measure groups:
FactTransaction, measuring DollarAmount
DimCustomer, measuring CustomerCount (Count of rows in DimCustomer) and CompanyCount (distinct CompanyCustomerKey)
The part I can't get my head wrapped around is the relationship I need to setup so that I can tell how many customers (CustomerCount) had Transactions for a specific SaleDate.
Can anyone help me out?
Rick Todd
May 7, 2009 at 1:20 pm
For the specific query in the described scenario only two of the tables enter into play: DimDate and FactTransactions; as I can see it there is a 1-to-n relationship in between DimDate.DateKey and FactTransaction.SaleDateKey.
Query's predicate would filter by DimDate.Date then access by DimDate.DateKey = FactTransaction.SaleDateKey
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 7, 2009 at 2:17 pm
Well, I'm hoping to get some advice on how to configure the relationship between the FactTransaction Measure Group, the DimCustomer Measure Group, and the DimSaleDate Dimension in Analysis Services Dimension Usage section.
If I didn't have the CompanyCustomerKey I could just count the CustomerKeys in FactTransaction, but I'm also wanting to manipulate the CompanyCount measure by attributes in the Dimensions associated with FactTransaction, like SaleDate.
I guess I didn't make that clear enough in the initial post.
Rick Todd
May 7, 2009 at 3:00 pm
In the Dimension Usage tab, for the intersection (ie relationship) between the Customer Measure Group and the Date dimension, set the relationship to be Many-To-Many, via the Sales fact table.
So,
the Sales Measure Group will be related to the Date and Cust dims using 'Regular'
the Customer Measure Group will be related to the Date using M2M as detailed above, and also to the Customer Dim as 'Fact' type.
HTH,
Steve.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply