Further to my earlier theme of Intelligent Laziness, here is some key advice before setting out on the journey of creating Analysis Services cubes. Hopefully this advice will help you to ‘Work Smart’ rather than ‘Work Hard’!
Take the users along with you on the journey
If the end users don’t understand the cube, then they won’t like it; end result, they won’t use the cube. You could work iteratively so that you have a ‘working’ cube for users, and a development cube, so you can comfortably develop in a way that is insulated from users.
Plan, Plan, Plan!
It is important to plan early on in the process about your key success criteria for delivery. It’s easy to be swayed by enthusiastic users to include work that isn’t essential to the project.
Choose the correct version of Analysis Services
It sounds simple, but check you have the correct version of SQL Server that you need. For example, in SQL Server 2008 enterprise edition is aimed at large cubes which require partitioning. Motto: if in doubt, choose Enterprise.
End users querying the cube
How are the end users going to query the cube? There are lots of products which can allow users to interact with the cube, for example, Reporting Services is the obvious solution for MDX-savvy users. If the users are Excel power-users, then they might find XlCubed useful addition. If the users want data visualisation par excellence, then Tableau might be worth a look.
Letting Users know what’s in the Cube
It can be difficult to know how to best document the cubes. Try and reduce the amount of time to respond to user enquiries by relying on a mapping spreadsheet, which traces the lineage of the data from source to final cube measure.
Data Sources
It’s always worth checking how complete the data sources are. The SSAS cube depends on a well-formed data warehouse, and if this isn’t in place, then it is difficult to generate a cube from a source which it wasn’t designed to handle.
Dates
Users need dates; lots of them, and lots of different types e.g. Fiscal calendars, public holiday calendars, fiscal calendars for their business customers and so on. It is essential to work out what dates they need, and, importantly, who is responsible for generating the calendars.
Calculations
The users can be involved here; ask them to give you their most popular calculations. It is useful to use a mapping spreadsheet to record this information.
Cube Hierarchies
The users can also create the hierarchy structure within the cube, by using sample data as a base. Some hierarchies are straightforward, such as geography. Product hierarchies, for example, may not be very straightforward, and it is easy to make assumptions which are not correct.
Security
It’s better to get the security resources on board earlier than later on; it is essential to know who can access what calculations, and it’s important to know where the restrictions should be placed, sometimes for legal reasons.
Thanks for reading and hopefully this will save you some time and effort!
Thank you so much for your insightful comments. I have a learned a tremendous amount from your blog posts.