Thursday, January 10, 2013

Prep for Analysis Services Multidimensional


Before getting into the thick of building a multidimensional cube, here's some questions you should ask yourself:

1. Do I have a project sponsor?
2. Do I have a reasonably sized dimensional model (aka Star Schema)?
3. Can someone check answers out of my data warehouse?

Please note these questions change if you decide on either Tabular or PowerPivot, as the prep work for those flavors of Analysis Services is different. I won't be going into Multidimensional vs Tabular in this post. For now, I presume you're here because you have SQL Server 2008 R2 or earlier and want to get started right away. Either that, or you're on SQL Server 2012 but your server memory cannot fit all your tabular cubes, or you've discovered you need parallel processing. Have a different reason for using multidimensional? PLEASE LEAVE A COMMENT. Would love to hear from you.

Your Project Sponsor

If you don't have the backing of a Senior Executive or someone influential that can pull through for you when times get rough, politically, data-wise, money-wise, then hold off until you get one. Either that, or get buy-in from your Manager that you can learn how to make a cube on your own time with their data.

You are about to do a lot of hard work that could revolutionize the way your business sees the world. Believe me, you will want the backing of someone more senior than you on the journey you're about to take. I've seen a lot of good work thrown in someone's face because the manager's, VP's Director of Operations had no idea where good money was being spent when something was being developed by someone else in some other office under some other direction. Don't put yourself in that situation or under the agony of throwing away so much hard work. If you need help selling the value proposition, reference my prior blog post on making a business case for Analysis Services.

Get a trusted Project Sponsor that knows your work and believes in you. Hopefully someone with signing authority, that has your back if things don't work out as planned. Trust me though, if you do this right, you only have a bright future ahead of you.

Dimensional Model, Reasonably sized

The success of your cube building is directly proportional to the quality of your data model. The reason I say "reasonably sized" is to encourage you to work as Agile as possible. Don't bite off more than you can chew. The opposite of Agile is referred to as Waterfall. Doesn't sound too good, does it. BI projects are best done on small chunks anyway. You'll learn stuff about your data along the way that will make you change your model in ways that will only strengthen dimensional model and cube making it more relevant and useful for end users.

If you're not familiar with dimensional modeling, go out and get a book. I recommend Kimball's Complete Guide to Dimensional Modeling. It comes with some good examples of common business models for different industries. These real world scenarios illustrate modeling concepts intertwined with the theory behind each dimension and fact table.

Validate with the Experts

"Garbage in, garbage out." "You're only as good as your data." Whoever coined these terms must have gone through validation of a data model for sure. :)

Validate your dimensional model with someone intimately familiar with the process your modeling. An analyst or production engineer would be great, but not necessary. Could be a data entry person, like a shipping/receiving clerk, an admin, or project manager. Be selective here. Hopefully there's someone knowledgeable enough to pick out a wrong number from your results and lead you to the right answer. Be prepared for frustration to set in here because its not always clear who has the right answer. The answer may lie between one or more people in different departments, and you may have to step through the process of a complete transaction (order to shipment to receipt to issue to delivery) in order to get an answer you can live with.

If you are the operations expert in your company, then validate your report delivery methods with your Project Sponsor and/or consult your ERP/CRM vendor documentation for different reports to validate against. Your project sponsor will want to get his hands on what you've been working on, so don't forget about him/her. Also, if you are working with an ERP/CRM system, you may have access to a VAR (value-add reseller) that can help you with some pre-built queries. Many times, ERP systems have built-in docs for schema definitions or make them available online. Never guess at what different codes mean in your relational system. Whatever the case, just make sure to do your homework and get feedback.

Alright, let's get on with it

If you've made it through the process of modeling your data and validating it, then congratulations. You've just build your data warehouse! You are 80% there to building your first multidimensional cube.

The whole process of dimensional modeling your data is probably the most time consuming, but also the most rewarding part of your BI project. If you've prepared well enough and stayed small (agile), then building your cube will seem quick and easy. Also, remember that at this point, many good clients can perform adhoc queries and self-service analytics against your data warehouse by itself. Clients like PowerPivot, Tableau, and Qlikview work wonderfully against a data warehouse. The potential is there to expand on your initial modeling ideas. Only problem moving forward is with the missing semantic layer. Remember those Hierarchies, Relationships, KPIs, and Drill-throughs you promised your project sponsor? That's where Analysis Services comes in.

Until next time, good luck with your models! In my next post, we'll start with a data source view that pulls in the tables from your data warehouse and continue with the process of creating a cube.