Analysis Services Structure

maq1
maq1 used Ask the Experts™
on
Hi all,

I'm designing a SQL 2008 SSAS Cube that will report product and service sales. We need to be able to report on both at the same time to see the total value spent by customer (products and services). Products have different metdadata (ie. categories) than services do.

What I was considering was to create an item dimension that contained both products and services, but I'm wondering if there is a more logical method.

Does anyone have any suggestions?

Thanks!

Matt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
You have a few different options here (as always).

If a sale is either a Product or a Service, but that the sale information is basically the same, you could assign a sale to an Item, and then use a Snowflake structure to indicate the Service information separate to the Product information. Then use MDX to make reports that need to reflect both.

Or you could quite easily have a ProductSale table and a ServiceSale table, and have a TotalSales measure available, which adds them both up.

In my experience, it comes down to what "typical use" is likely to be be.

Rob

Author

Commented:
Rob,

Thanks for your reply. Would you mind explaining what you mean by:

"If a sale is either a Product or a Service, but that the sale information is basically the same, you could assign a sale to an Item, and then use a Snowflake structure to indicate the Service information separate to the Product information. Then use MDX to make reports that need to reflect both."

Are you suggesting that I combine products and services into a single item table and then linking the fact table to the PK of the item table?

Having said that though - option 2 sounds very feasible too. I just need to think it through (we've also got targets to deal with too)

Many thanks again,

Matt
Commented:
Yeah - Option 1 involves having a single item table, and then having some of those records linking to Services, and others to Products.

Option 2 will probably be easier, using 2 fact tables: one for ServicesSales and one for ProductSales. That way, if you information that is pertinent to one but not the other, it's easy. You'll have a Product dimension, and a Services dimension, and then you can have reference dimensions that are related to both Product and Services.

Rob

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial