Link to home
Start Free TrialLog in
Avatar of maq1
maq1

asked on

Analysis Services Structure

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
Avatar of Rob Farley
Rob Farley
Flag of Australia image

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
Avatar of maq1
maq1

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Rob Farley
Rob Farley
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial