How to avoid a proliferation of cubes

Question

How to avoid a proliferation of cubes

Elizabeth.Block

Hall of Fame

Points: 3516
More actions
February 24, 2014 at 3:06 pm

#150630

We're just starting to dig into cubes and BI work. One of my DBA colleages is very concerned about developers randomly creating cubes off the production databases instead of creating an Enterprise Data Warehouse using dimensional modeling. What do you do to avoid random cubes everywhere? What books or websites can you suggest for DBAs who need to manage SSAS, SSRS, and SharePoint? I've seen a lot of BI information for the developers and report writers but not much for how to administer a BI infrastructure. Any tips would be greatly appreciated.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

sneumersky SSCertifiable Points: 7667 More actions · Answer 1

For advice on administration check out the SSAS Operations and Performance Guide:

http://dennyglee.com/2012/06/28/microsoft-sql-server-analysis-services-multidimensional-performance-and-operations-guide/

Elizabeth.Block Hall of Fame Points: 3516 More actions · Answer 2

Thanks very much for the link! I'll read it right away.

sneumersky SSCertifiable Points: 7667 More actions · Answer 3

Not sure if you are using the multidimensional (classic) or the tabular model. There is a separate performance guide for the tabular model of SSAS 2012, along with advice on hardware considerations for the tabular model of SSAS in another separate white paper. Let me know if you need those.

Elizabeth Block SSC Enthusiast Points: 165 More actions · Answer 4

We're not using the tabular model right now but I would like to read the white papers for those. I think we'll eventually use the tabular model.

sneumersky SSCertifiable Points: 7667 More actions · Answer 5

Tabular Performance: http://msdn.microsoft.com/en-us/library/dn393915.aspx

Deciding which model to pick: http://msdn.microsoft.com/en-us/library/hh994774.aspx

The short version of why to use Tabular.....if you want to do quick data profiling, proofs of concept, advanced work in excel. You are not bogged down by those pesky attribute relationships, but that is not a REASON to leave data dirty either 😀

Elizabeth.Block Hall of Fame Points: 3516 More actions · Answer 6

This is awesome. Thanks for the links. We're still trying to figure out when tabular makes more sense so those links will hep.

RonKyle SSC-Dedicated Points: 31541 More actions · Answer 7

The main reason to avoid cubes everywhere is that if fact tables and dimensions don't have common definitions agreed to across the enterprise, then the warehouse will never reach the level of the sole source of agreed upon truth. It would also be unusual, but not impossible, to imagine a company with a lot of developers who are all good at OLAP development.

Elizabeth Block SSC Enthusiast Points: 165 More actions · Answer 8

That makes sense. Right now all the developers are creating cubes independently and it's not an enterprise wide effort. The managers don't want to take the time to create an enterprise data warehouse so everyone is just throwing up cubes. Not the ideal...

RonKyle SSC-Dedicated Points: 31541 More actions · Answer 9

Often things aren't the ideal, but a bunch of ad hoc cubes won't likely have the data safeguards that go with a properly designed ETL system. I appreciate that to a point, things can't be done "perfectly," and as long as management is read into the risk, then there's nothing you can do. You provide the due diligence for your area and management assesses the risks and rewards for the company.

Edited to correct a mispelling and clarify the last statement.

sneumersky SSCertifiable Points: 7667 More actions · Answer 10

I'm with Ron here regarding both the skill set volume and "cube anarchy". I can definitely understand different VERSIONS of a FEW cubes to avoid version collisions, but you will want to avoid "cube madness". If possible, use source control as well.

Also, I would avoid the "one cube per fact table" approach if that is indeed going on. Usually, you can easily fit 5 - 15 fact tables in a cube using one measure group per fact table. Oftentimes using perspectives (if you have Enterprise Edition) is a great way to present only the relevant information to an end user in the same way views are used in a relational database.

Just do not use perspectives as security mechanisms. They are not intended to be used that way.

Elizabeth Block SSC Enthusiast Points: 165 More actions · Answer 11

I'll have to do some research on perspectives, as I haven't worked with them. I'll also have to check whether the developers are using source control for this. They usually do but since this is a new direction they may not have thought of that yet. We've got a lot to learn about this. Thanks for the input!