Lab: Partitions in Analysis Services
Introduction
One of the primary objectives of Business Intelligence is providing rapid and accurate return of query results. Because the data sources underlying our cubes, and even the cubes themselves, can become very large in physical size, storage becomes a significant consideration within cube design strategy. A partition is a physical file on a hard disk that contains a subset of the data included in an Analysis Services database. Analysis Services uses partitions to manage and store data and aggregations for a measure group in a cube.
Objective: To create partitions in the Datawarehouse.
In the following tasks, you will create partitions in the Visiual Studio environment using the Assignment2 DatawarehouseFactable (Classes Completed) time attribute.
Creating new partitions
In order to accomplish this Lab, you should have already completed creating your cube for Assignment2 and started your SQL server 2008 and Analysis Services .
1 Click StartMicrosof Visual Studio 2008
Select FileOpen TrainingCenterProject (your assignment2 project)
The Microsoft Visual Studio 2008 development environment opens.
2 While you are on the cube design tab, go to the “Partitions” tab.
You will see that a partion already exists once you create a cube. This partition contains all the rows.
We would like to create new partitions based on time(year).
3 ClickNew Partitionlink and the Partition Wizard will open, click next.
4 Specify Source Information: You need the select the measure table which is named Classes Completed. However once you select that wizard, it doesn’t allow you to go any further since the existing partition contains all the rows so that creating another partition will cause duplicate values.
5 Delete existing partition:Cancel the wizard and right click on the existing partition and delete and confirm deletion than start the partition wizard again by clicking New Partition link.
6 Select Classes completed and click next
7 Select the rows :Check the “Specify query to restrict rows” option. Once you check it, the following query will be displayed with an empty WHERE clause. Type “[dbo].[ClassesCompleted].[YEAR] ='2001' “ following to WHERE clause. Through this query a new partition will be created and it will contain only the rows for the YEAR 2001. Click next to proceed.
8 Processing and storage LocationsLeave the Processing at the current server instance and as an option, different storage location can be selected if a different folder is specified. For now leave it as default and click next.
9 Completing the Partition :Choose the design aggregations later and check the Deploy and process and click finish.
When it is deployed and partition is completed the following scrren will appear. Click the “run”botton on the same screen and if everything is okay then the process succeeded message will appear.
To confirm the partition please check the cube Browser and try to get a report using the Year attribute in the time dimension and with any measure,only the Year 2001 will appear. Depending on the design, other partitions should also be created,like one partition for every year,so that all data can be displayed.
99-01-FM-Defining a Data Source View with Project.doc 10/21/20181 of 6