Seven Steps for Building an SSAS Cube
So you're interested in building a cube in SQL Server Analysis Services? I commend you for your choice. OLAP tools in SQL Server provide a host of performance, organization, and flexibility benefits when it comes to data organization and intelligence.
Just like anything it's important to have a plan when building putting your cube together. This article will take you through the cube planning process and the steps you will take at a high level. The series will follow with more detailed articles and blog entries on each step and best practices involved.
Step 1
Make a list of measures (Fact tables) and dimensions you want to include in your cube. Remember that Measures are the "What" and dimensions are the "By". For example: your users may want to see sales and costs by state and sales person.
In this example Sales and Costs would be the measures, and State and Sales Person would be the dimensions.
Step 2
Create a new project in BIDS (Business Development Intelligence Studio). You will choose Analysis Services Project and supply the location information.
Step 3
Create your data sources. You may have data coming from multiple sources here. This is where you will configure what sources contain your fact and dimension tables for the cube. This can be from a variety of RDBMS systems, but for this article we will focus on SQL Server.
Step 4
Create your Data Source View. This is where you will choose your tables, apply keys if necessary, add new calculated columns, and perform other tasks to get your UDM (Unified Dimensional Model) in place for your cube to be built off of. Objects in your DSV are what will be available for you to build into your cube.
Step 5
Once you have the DSV created you can begin to walk through the cube wizard. This will ask you to identify your measure groups (fact tables) and your dimensions so that SSAS can configure them appropriately.
Step 6
Configure your Dimensions and Measures. You will now need to go through each dimension and configure it to contain the right attributes, and properties for your desired purpose. This will be done through the cube designer and the dimension editor. To get to the dimension editor click on the Edit <dimension> Link in the bottom left pane on the cube designer tab, or you could double click the dimension in the solution explorer.
Step 7
Once you have your dimensions and measures configured appropriately, you will need to configure security, performance tune, and publish your cube. There are more great articles coming up for details on each of those topics.
Thanks for reading and as always, post your questions in the BIDN forums. The best place to get answers to your questions :) !
Thanks – Adam
Ajorgensen@pragmaticworks.com