May 8, 2008 at 11:21 am
If I wanted to add a calculated measure that calculated a count of items against a customer where the customer.code was in the item.codes (for instance a customer may be code "x" and an item has a codes column of "xyz"), is there a way to do this?
May 13, 2008 at 9:15 pm
In analysis services you mean?
May 14, 2008 at 8:42 am
Yes... Well in BIDS more specifically.
May 18, 2008 at 2:42 pm
Just add the measure as a count of the items. When you slice and dice by customer (in a front end tool such as ssms or proclarity, or an mdx query) the item count will roll up correctly by customer.
May 18, 2008 at 6:38 pm
Hi Phillip,
So just a straight COUNT(DISTINCT item_id) will work? How does it know to count only those items where the codes align if I am linking things together via an associative entity (FactShop, which has PK id, and FKs customer_id and item_id)?
Essentially, what I need to do is this:
Let's say two customers are of the same type, and one has 3 items and the other has 5. If I look at the cube for these customers, when I drill down to the appropriate level (where the items should be evident), I'd like to see 4, as the average of 5 and 3 items for that category of customer. I am not even sure what the SQL syntax would be there since I am not averaging on a numeric field but actually on row count... let alone the MDX.
Does any of that make sense, and can you help? I'd be MUCH obliged!
Cheers,
Chris
May 18, 2008 at 9:06 pm
We may be at odds. I'm imagining a fact table "items" with foreign keys to customers.
Using count to rollup a column will give you a tally of records as opposed to a sum of the column value and you can apply an avg function to this.
Could you provide a little more detail as to the exact requirements and expand on your last post and I may be able to help?
May 18, 2008 at 9:32 pm
Hi Phillip!
First off, thanks a TON for the willingness to help. I am definitely learning this stuff, but if I could get a couple of questions/concepts resolved, I think my understanding would make a huge leap.
For the sake of not re-posting, I hope you don't mind if I just give you the links to my other questions, which will, hopefully, answer your question about my setup.
http://www.sqlservercentral.com/Forums/Topic497285-147-1.aspx
http://www.sqlservercentral.com/Forums/Topic494013-147-1.aspx
In a very quick nutshell, I have two dimensions (Customer and Item), and a fact (FactShop). The fact table is just a PK (auto-incrementing integer), and FKs for the customer and item association, because there is a many-to-many relationship between them.
Customers have three attributes, size, shape, and height that correspond to size, shape, and height on an item.
The associations in the fact table come from some precalculations we do (our proprietary algorithm) to match customers to items. So, an item could be associated with a customer even though the three attributes do not all align. The fact table is the definitive answer.
What I am trying to accomplish is a drilldown on the rows of Shape :: Size :: Height, with Type :: Sub-type as the columns (drillable), and the data being the average number of items per customer at each of the drill levels.
Does that make sense?
Cheers,
Chris
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply