MSBI -- SSAS -- Part-13

Partitions:-

In general we Use cube partitions to improve Analysis Services performance and it also reduce downtime.

Ø A partition is a container for a portion of the measure group data. 

Ø Partitions are used by Microsoft SQL Server Analysis Services to manage and store data and aggregations for a measure group in a cube.

Ø Every measure group has at least one partition; this partition is created when the measure group is defined.

Ø Partitions are a powerful and flexible means of managing cubes, especially large cubes.

For example:-

----------------

 A cube that contains sales information can contain a partition for the data of each past year and also partitions for each quarter of the current year. Only the current quarter partition needs to be processed when current information is added to the cube; processing a smaller amount of data will improve processing performance by decreasing processing time.

NOTE:- In the above scenario, instead of storing whole measure group data into a single physical block, we can create the partitions for each year.

STEPS TO CREATE THE PARTITIONS

For creating partitions, Launch Microsoft Visual Studio and select SSAS project.

1.     In SQL Server Data Tools, in Solution Explorer, double-click the cube to open it in Cube Designer, and then click the Partitions tab.

2.     Expand the measure group for which are adding partitions. By default, each measure group has one partition, bound to a fact table in the DSV.

3.     In the Source column, click the browse (. .) button to open the Partition Source dialogue box. 

4.     In Binding Type, select Query Binding. The SQL query that selects the data appears automatically.

5.     Then select the data source (say AdventureWorksDW2012).,Type in the Select Query with the where condition ( Say If we need only 2006 year data, accordingly we need to provide the SQL Command).

6.     Click Check to check for syntax errors, and then click OK.

 CREATION OF NEW PARTITION:-

1.     Click New Partition to start the Partition Wizard 

Then click Next button. You would be able to see Partition Wizard Window.

2.     In Specify Source Information, Choose the Available tables and click next.

3.     Then Restrict Rows pop up appears. We need to choose “Specify a query to restrict rows “.

Once we provide the Query, need to click on check button .Then you should be able to see the below pop up.

Click Next, then we get the below window.No need to change settings here above, just click next.

4.     Then we get “Completing the wizard”, where it allows us to choose the Aggregation options and also we can give the partition a descriptive name. Select the option “Design Aggregations Later” and click Finish.

5.     Deploy the solution or process the partition to load the data.

6.     Browse the cube to verify the correct data is reflected.

Thank you Chaitanya for clean documentation.