Few Questions about SSAS Cubes

Question

I am newbie to SSAS 2012 and here are my questions:

• Can we have measures of complex types (table)?
• Is it possible to order the results by a specific measure or attribute?
• My cube can be modeled as one dimensional hierarchy, is this generally good?
• Can I shard cubes using remote partitions on a range of members?
• Do we have remote table partitions with SQL Server 2012 databases?

Thank you!

Answer 1

Answers to your questions, in order:

• First point: I'm not sure what you're getting at here. A measure can only be something that will display in a single cell. You can aggregate stuff up to create a measure. You can also have multiple fact tables (called measure groups) in a cube. If you slice by common attributes they measure groups will slice by those attributes. If you slice by something that one of the measure groups does not have in common, the values will be repeated for each slice of the other group.

• Yes. You can order by attributes or values of measures.

• Probably OK. I can't see any harm in that. Can't see a lot of point in it either, but if you really only have one drill down path there's no reason that why that can't be implemented in a cube.

• Yes, you can do distributed partitions. Never built a system with remote paritions personally, but there's a technet article here..

• SQL Server will do distributed partitioned views over partitions on multiple separate instances with Enterprise Edition. There is a shared-nothing version as well, but you can only buy that in a hardware bundle from an OEM. It doesn't seem to be getting a lot of traction in the market.

Answer 2

You don't say whether you have looked at both possibilities in SQL Server SSAS 2012 - those being either the UDM (OLAP cube) or the BISM (Tabular Model). To get a quick, high-level overview of the differences between the two - read this. After you have decided on one model over another, then you may want to revise your question a bit.