Tutorial 12: MSAS : Building the Cube Part #1
A cube is a multidimensional structure consisting of dimensions and measures. Therefore, we need to know what dimensions we intend to use and what measures will be captured. In short, we need to know what quantitative values we want to analyze or monitor from our database. Usually organizations capture actual measures such as revenues, sales in context of expenses, costs along with “budget”, “Plan” or “forecast” values.
Dimensions represent the different perspectives or views. Measures have relevance and value in the context of the dimensions. The granular levels of detail for the values chosen as measures are to be found in the Fact tables. All the dimensions of the cube are linked to the fact table in a snowflake schema or star schema to make the analysis meaningful. We will need to decide upon the kind of schema that would best suit our purpose. However for the purposes of the current tutorial we will select a basic fact table and link it to several dimension tables in a star schema and analyze the measures.
The objective will be to select a few measures from the data source’s specified fact table and stipulate the dimensions we wish to capture. We will also define a time dimension and design a simple cube. For this purpose will use the Analysis Server’s Cube Wizard. This Wizard makes the job fairly simple and guides the user through the steps with ease.
Initializing the Cube Wizard
Step 1 : Right Click on the Cubes folder and select ‘New Cube’
The cube wizard screen opens. Note that there is a small check box at the bottom of the screen prompting the user to check the option if he wants to skip the screen in the future. This screen can be dispensed with by advanced users by checking this option. Click Next to Proceed with the process of building the cube.
In the cube wizard we need to select a fact table. The user needs to click on the + sign next to the data source name ‘sample’ and expand the Default Schema of the data source. A list of fact tables appears. Select the fact table sales_fact_1998 and click Next.
take a look at the data contained in this fact table by clicking “Browse data…” button. A small window appears displaying the columns and rows of data as under:
Close the window by clicking on the close window button and return to the cube wizard screen above and click Next for selecting the measures we wish to incorporate into our cube design. We will select store_sales, store_cost and unit_sales measures by double clicking on the items and populating it into the right pane of the wizard.
The next step is to select the dimensions for the cube. The ‘_id’ columns that appeared in the preceding dialog box represented key columns that might be joined to dimension tables from the fact table to pull in data from the dimension tables. On clicking Next in the above dialog box the ‘Select the dimensions for your cube’ dialog box appears:
In the next part of the same tutorial, we will discuss about how to create Dimensions.
