menu

Showing posts with label SQL Server Analysis Services. Show all posts
Showing posts with label SQL Server Analysis Services. Show all posts

Friday, May 27, 2011

Using XMLA Command to Clear Cache of a SQL Server Analysis Service Database

Problem
One of the Business Intelligence developers in my company approached me yesterday with a dilemma. He wanted to know if there was a way to clear the cache of an Analysis Services database other than by recycling the Analysis Services service in SQL Server. At first I started to tell him, but figured it would be smarter to document the same and share the information. Below is the a process that can be used for SQL Server 2005 and alter versions.
Solution
You can clear the cache of an Analysis Services Database, Cube, Measure Group or a Dimension using the ClearCache XMLA command. While specifying the values for Analysis Services Database, Cube, Measure Group or a Dimension, you need to specify the Object ID rather than its name. You can easily find the Object ID by opening the Analysis Services Databases within Business Intelligence Development Studio (BIDS). Select any object in Object Explorer and look for the Object ID value within the Properties window.
Here are the steps.
  1. Connect to an Analysis Service Instance using SQL Server Management Studio.
  2. In the Object Explorer, expand the Analysis Server Node, Expand Databases -> Right click on the Analysis Services Database and select the New Query … XMLA option from the drop down list as shown in the snippet below.
Here are the commands you would use to clear the cached data.
XMLA script to Clear Cache for an Analysis Services Database
<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW 2008</DatabaseID>
</Object>
</ClearCache>
If you are going to test the performance of a specific MDX query then first clear the cache and then execute the MDX query.

XMLA script to Clear Cache for a Cube
<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW 2008</DatabaseID>
<CubeID>Mined Customers</CubeID>
</Object>
</ClearCache>

XMLA script to Clear Cache of a Measure Group
<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW 2008</DatabaseID>
<CubeID>Mined Customers</CubeID>
<MeasureGroupID>Exchange Rates</MeasureGroupID>
</Object>
</ClearCache>

XMLA script to Clear Cache of a Dimension
<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW 2008</DatabaseID>
<DimensionID>Dim Currency</DimensionID>
</Object>
</ClearCache>
Next Steps

Using the Built-In Time Dimension and Time Intelligence in SQL Server Analysisl

ProblemWe are building an Analysis Services cube and trying to understand how to create a time dimension and take advantage of the built-in time intelligence features that provide many useful calculations such as period-to-date, period-over-period growth, etc.  Can you provide a detailed explanation of how it all works?
SolutionGetting the time dimension configured correctly is a necessary first step in order to take advantage of the Time Intelligence features in SSAS which support the various queries that you mention.  Just about every data warehouse and cube has a time dimension which represents the hierarchies that you use to filter your queries such as year, month and quarter.  The dimension names time, date, calendar, etc. are generally used interchangeably.  My naming preference is calendar because that's really what you're modeling in most cases and the name easily supports multiple calendars like fiscal year, reporting year, etc. 
Probably the best way to understand how the SSAS built-in features for the time dimension work is to walk through an example.  When you create an SSAS cube you have the option of building it from an existing data source or without using a data source.  In the latter case the SSAS Cube Wizard can generate the relational schema that you need to populate the cube.  This option comes in handy when you're trying to design your cube as the to-be solution; i.e. this is what I want versus creating a cube from an existing data source; i.e. this is what I have and let's use it to create a cube.  Another advantage of creating the cube without a data source is that the important properties for configuring the time dimension get set for you automatically.
Let's walk through the steps to build a cube that uses the built-in SSAS features for the time dimension and associated time intelligence.  The starting point is to create an SSAS project in Business Intelligence Developer Studio (BIDS) or Visual Studio.
After creating the SSAS project, right click on Cubes in the Solution Explorer and select New Cube to launch the Cube Wizard.  On the Select Build Method dialog select Build the cube without using a data source.  This allows us to create a cube and generate the relational schema that will be used to populate the cube. 
Define a couple of measures.  Measures are typically the dollar amounts that are monitored to determine how your business is performing.  When the relational schema is generated, a table is created for each Measure Group; a column in the table is created for each Measure Name. 
The Define New Dimensions dialog allows us to define dimensions for the cube.  Dimensions are the attributes that we use to slice and dice the data.  For our example we'll just create a time dimension.  Check the Time type and enter the name Calendar.  When the relational schema is generated, a table called Calendar will be created.
After selecting a Time-type dimension, you define the date range and the time periods.  When the relational schema is generated, the time dimension will be populated automatically based on the selections below.  The time periods allow us to perform queries that filter the measures by year, quarter, month, week or day.  You also have the option to define a Fiscal Calendar, Reporting Calendar, etc. (this step is skipped in our example).
The Define Dimension Usage dialog allows us to link the measures to the respective dimensions.  When the relational schema is generated, there will be a foreign key in each measure group table enabling a join to the dimension table.
The final dialog in the Cube Wizard summarizes the selections made and allows you to name the cube.  Click the Generate schema now checkbox; this generates the relational schema that will be used to populate the cube.
When you check Generate schema now the Schema Generation Wizard is launched to guide you through the steps to generate the relational schema.  The Specify Target dialog is used to identify the database where the relational schema will be generated.  The Data source identifies the database; the Data source view is used to specify the tables, views, etc. from one of more data sources that will be used to populate the cube.  The cube is always built from the data source view.  Prior to this step you may want to create a new database to use as the data source.
For the remainder of the steps in the Schema Generation Wizard just accept the defaults.  After the schema is generated, the cube needs to be processed.  Right click on the cube in the Solution Explorer and select Process.
Let's review a few key points about the time dimension that was automatically created for us.  Double click Calendar.dim under Dimensions in the Solution Explorer to review the dimension structure which shows the attributes and hierarchies.  Two hierarchies have been created with the levels as shown.  These hierarchies allow us to summarize and drill through the measures in the cube.
Click on Calendar (first entry under Attributes above) to display the dimension properties.  The Type property (in the Basic group in the Properties window) has the value of Time which designates it as a Time dimension.  If you click on the dropdown for the Type attribute, you will see that there are many other types of dimensions that have some sort of built-in support in SSAS.
For an individual attribute, there are several properties that must be set correctly as well.  As an example click on the Year attribute to view its pertinent properties.  The Type property must be set to Years.  The Name column specifies the column in the generated relational schema that is used as the display name for the attribute ; note that it is a WChar type which is a string.  The ValueColumn represents the underlying value of the attribute and it is a Date type.  You can browse the other attributes and note their property settings.  A key advantage to generating the time dimension in the Cube Wizard is that all of the important properties get set correctly for you automatically.
Click on the Browser tab for Calendar.dim to view the actual data in the dimension.  This step shows the hierarchies in the time dimension, based on the time periods selected in the Cube Wizard.  The hierarchy  shown below allows us to query at the year, quarter, and month levels. 
At this point we have created a cube with a properly configured time dimension.  The next step is to add the built-in Time Intelligence which allows us to specify the types of time slicing we want to use in our queries.  Click on the MSSQLTips cube in Solution Explorer then click Cube on the top-level menu and Add Business Intelligence to launch the Business Intelligence Wizard.  Select Define time intelligence from the list of available enhancements.  The prerequisites for adding Time Intelligence are a time dimension with one or more hierarchies that is linked to one or more measures.
Click on the Available time calculations that you want to add to the cube.  A brief description of each is displayed as you click or scroll through the list. 
Select one or more measures to implement the calculations you selected in the previous dialog.  This is the final step in the wizard.
The Business Intelligence Wizard creates a number of calculated members in the cube which you can view on the Calculations tab of the cube.  The wizard creates an additional hierarchy in the Calendar dimension.  The hierarchy name is derived from the hierarchy that you pick in the Choose Target Hierarchy and Calculations step of the wizard.  In this example the hierarchy name is Year - Quarter - Month - Date Calendar Calculations.  Right click the MSSQLTips cube in the Solution Explorer and select Process to update the cube with the Time Intelligence calculations.
Our final step is to browse the cube and take advantage of the Time Intelligence to perform some queries.  Double click the MSSQLTips cube in the Solution Explorer to display the cube designer then click the Browser tab.  The Browser tab user interface is essentially the pivot table that you are familiar with from Excel or the Office Web Components.  Note the instructions on where to drop column fields to browse the cube.
Perform the following steps to see an example of the Time Intelligence calculations:
  • Drag and drop Sales and SalesCost from Measures onto the Drop Totals or Detail Fields here area
  • Drag and drop Year from the Calendar dimension onto the Drop Row Fields here area
  • Drag and drop Year - Quarter - Month - Date Calendar Calculations from the Calendar dimension onto the Drop Column Fields here area
  • Click Year - Quarter - Month - Date Calendar Calculations in the Drop Column Fields here area and click Current Calendar, Year over Year Growth % and Year Over Year Growth
  • Click Year on the Drop Row Fields here area and click the years 2006, 2007 and 2008
You should now have a pivot table that looks like this:
The above pivot table shows Sales and SalesCost for the years 2006 through 2008, as well as the Year Over Year Growth % and Year Over Year Growth (in dollars).  The sample data used to populate the cube was a simple query that extracts data from the sample AdventureWorksDW database that comes with SQL Server 2005. 
Next Steps
  • Download a copy of the sample SSAS project here to experiment with the built-in Time Intelligence features in SSAS.  Note that the project assumes you have an instance of SQL Server and Analysis Services running locally.  Unzip the files into the folder "C:\MSSQLTips" to minimize changes to get the sample to run and follow the instructions in the README.txt file.
  • While the Time Intelligence Wizard provides many calculation options, you may come up with others and you will have to implement these yourself.  You can follow the pattern of the calculations created by the built-in Time Intelligence to do this.
  • Before you run the Time Intelligence Wizard make sure that you have all of your measures defined in the cube.  Each time you run the wizard it creates a whole new hierarchy in the time dimension for the calculations.  You can manually edit the generated calculations from the Calculations tab in the cube designer if necessary.

Processing an Analysis Services Cube Using SQL Server Management Studio

Problem

One of the Junior DBA in my company who is new to SQL Server Analysis Services approached me yesterday with a dilemma. He wanted to know how to process a cube Using SQL Server Management Studio. In this tip we will go through the steps which a database administrator needs to follow to process an Analysis Services cube using SQL Server Management Studio.

Solution

Let us go through the steps which a database administrator needs to follow to process an Analysis Services cube using SQL Server Management Studio.
1. Connect to the SQL Server Analysis Services Instance using SQL Server Management Studio.
2. In the Object Explorer Pane, expand the Analysis Services Instance, expand Databases and then expand the Analysis Services database that contains the cube which needs to be processed.
3. Right click the cube to be processed and then click the Process option from the drop down list as shown in the snippet below.
4. In the Process Cube dialog box, in the Process Options column under Object list, verify that the Process Fulloption is selected for the column as highlighted in the below snippet. If it is not selected, then under Process Optionsclick the option and then select Process Full from the drop down list as shown in the below snippet.
5. To verify settings for the processing batch you need to click the Change Settings button in the Process Cube dialog box as shown in the snippet below. The settings you specify in the Change Settings dialog box will override the default settings inherited from the Analysis Services database for all the objects listed in the Process dialog box.
In the Processing Options tab, shown below, there are different processing options available:
  • Parallel: - Use this option to process all the objects in a single transaction.
  • Sequential: - Use this option to process the objects sequentially.
  • Writeback Table Option: - Choose the option which can be used to manage a Writeback table. There are three options available Create, Create always and Use existing
  • Affected Objects: - Processing affected objects will process all the objects that have a dependency on the selected objects.
6. In the Dimension Key Errors tab under change settings you will be able to use either a default error configuration or a custom error configuration. Click OK to save the changes and to return to the Process Cubedialog box.
7. Finally to Process the Cube click OK in the Process Cube dialog box. Once the Cube Processing has completed successfully click the Close button in Process Progress dialog box to complete the cube processing.

Next Steps

Process a SQL Server Analysis Services Cube Using an XMLA Query

Problem

One of the Junior DBAs in my company wanted to know how to process a cube using an XMLA Query. In this tip we will go through the steps a database administrator needs to follow to process an Analysis Services cube using an XMLA command.  

Solution

Let us go through the steps a database administrator needs to follow to process an Analysis Services cube using XMLA.
1. Connect to a SQL Server Analysis Services Instance using SQL Server Management Studio.
2. In the Object Explorer Pane, expand the Analysis Services Instance, expand Databases, expand Analysis Services database, and then right click the Analysis Services cube which you want to process and then choose New Query ->XMLA from the drop down menu to open a new XMLA Query window.
3. In the XMLA Query window copy and paste the below XML. You need to change the DatabaseID and CubeIDappropriately as per your environment and then to do a Full Processing of the cube execute the XMLA Query.
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  
<
Parallel>    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">      <Object>        <DatabaseID>Adventure Works DW</DatabaseID>        <CubeID>Adventure Works DW</CubeID>      </Object>      <Type>ProcessFull</Type>      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>    </Process>  </Parallel>
</
Batch>
4. You can also generate the XMLA Query to process the cube by selecting Script Action to New Query Windowoption under the Process Cube GUI as shown in the below snippet.  Refer to this tip for more information on how to get this this screen.

Note

When you run the XMLA query as shown in Step 3 this will process the cube with the default options. If you need to specify other options as shown in this tip,, you can select all of the options using the SSMS and then use the "Script Action to New Query Window" to see how all of these addtional options are configured in XML.

Next Steps

How to restore a SQL Server Analysis Services Database

ProblemOne of the Junior DBA in my company approached me yesterday with a dilemma. He was assigned a task to restore an Analysis Service SQL Server 2008 Database on a development server. At first I started to tell him, but figured it would be smarter to document the steps which he needs to perform to successfully restore an analysis service database from an encrypted analysis service database backup on an SQL Server 2008 Analysis Services Instance.
SolutionIn this tip, you will go through the steps which a DBA needs to perform to successfully restore an analysis service database to a SQL Server 2008 Analysis Service Instance.  I will show you how this can be done via SSMS and also using an XMLA query.

Restoring Analysis Service Database using SQL Server Management Studio GUI
1. Connect to SQL Server 2008 Analysis Service Instance using SQL Server Management Studio
2. In Object Explorer, right click the Databases node and select the Restore… option from the drop down list as shown in the snippet below
3. In the Restore Database window, you need to click the Browse… button next to Backup file text box under Restore Source; this will open up the Locate Database Files window as shown in the below snippet.
4. In the Locate Databases Files window, you need to specify the path where the analysis service database backup file is stored, in our case ProductAnalysisDB, as shown in the below snippet and click OK to save the Restore Source information.
5. Next within the Restore Database window, you need to click the Browse… button next to Storage Location text box under Restore Target; this will open up Browse for Remote Folder window as shown in the below snippet. Here, you need to select the path where the Analysis Service database needs to be restored; click OK to save the changes and to return to the Restore Database window.
6. Finally, you need to choose one of the options Allow database overwrite or Overwrite security information. If you have an encrypted backup of the analysis service database, then you need to provide the encryption password else this filed can be left blank. Click OK to go ahead and restore the Analysis Service Database.
7. Once the Analysis Service database is successfully restored you can make necessary configuration changes by changing the Data Source property of the restored database.

Restore Analysis Service Database Using XMLA Query
1. Connect to SQL Server 2008 Analysis Service Instance using SQL Server Management Studio
2. In Object Explorer, right click Analysis Server Node and select New Query … XMLA option from the drop down list as shown in the snippet below
3. This will open up XMLA query window where you can copy and paste the below mentioned XML code and execute the same to perform the restore of ProductAnalysisDB analysis service database. You will need to change for your database.
<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<File>C:\Program Files\Microsoft SQL Server\MSAS10.SQL2008\OLAP\Backup\ProductAnalysisDB.abf</File>
<DatabaseName>ProductAnalysisDB</DatabaseName>
<AllowOverwrite>true</AllowOverwrite>
<Password>Backup</Password>
<DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">C:\Program Files\Microsoft SQL Server\MSAS10.SQL2008\OLAP\Data\</DbStorageLocation>
</Restore>
4. Once the analysis service database is successfully restored you will be able see the below mentioned success message.
Executing the query ...
<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<File>C:\Program Files\Microsoft SQL Server\MSAS10.SQL2008\OLAP\Backup\ProductAnalysisDB.abf</File>
<DatabaseName>ProductAnalysisDB</DatabaseName>
<AllowOverwrite>true</AllowOverwrite>
<Password/>
<DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">C:\Program Files\Microsoft SQL Server\MSAS10.SQL2008\OLAP\Data\</DbStorageLocation>
</Restore>
Restore started.
Restore finished.
Next Steps

How to remove parent as a child from a SQL Server Analysis Services SSAS parent child dimension

Problem

In a parent child dimension, when an attribute's usage type is set as Parent and browsed from a client tool, many designers as well as users may get confused based on the displayed results. For example, when you browse a parent attribute in a parent child dimension like Employee and say the parent attribute is Employees, you can browse data members from the parent node until the deepest available level of the child node. By default you will find each parent member having a child member with the same name even though this member might not exist in the data. From a user experience point of view this is not a desirable way of browsing a parent child hierarchy because it may get confusing. In this tip we will look at an example of this issue and how to solve this problem.

Solution

The Parent attribute in a parent-child dimension is a special kind of attribute. To visualize the problem, we need to a have a parent child dimension with a parent attribute. For the purpose of this tip, we will use the AdventureWorks sample that ships with SQL Server. Open this solution and deploy it and then browse the Employees attribute of the Employee dimension. Employees is an attribute whose usage type is set to "Parent" in the Employee dimension. When you browse this attribute, you will find the properties as shown in the below screenshot.


As you can see below the parent member is also available as a child member and this has the potential to confuse users.
It would be beneficial to understand the reason for this data representation before we proceed with the solution. Say a manager has two employees and all of them have their own sales targets. As per the hierarchy, the manager should be shown as a parent node and both employees should be displayed as child nodes. From an aggregation point of view, both these employees data can roll up to their manager, but manager is also an employee and can have his own targets. So the final data value associated with manager would be the roll up value of both employees as well as his own individual data value. This is the reason SSAS adds a system generated data member which is the parent member itself in the hierarchy. This has no effect on aggregation, but the intention is to preserve the individuality of the parent node in the display of the hierarchy.


In the below screenshot, you can see the setting for the Employees attribute. The above mentioned issue is a result of a property named "MembersWithData" which has a default value of "NonLeafDataVisible". 
Our intention is to hide this system generated data member, so that when the hierarchy is browsed, the parent attribute is not shown as a child member to itself. To change this, modify the value of "MembersWithData" to "NonLeafDataHidden" as shown in the below screenshot.



After changing this value, process the dimension and deploy it. Connect to the cube and browse the parent attribute of the "Employee" dimension. This time when you browse this attribute, you will not find the parent nodes displaying themselves as a child to itself as shown in the below screenshot.




Next Steps

  • Check out how many attributes can be configured as a parent attribute in a parent child dimension.
  • Use MembersWithDataCaption property to make the system generated data members stand out explicitly to avoid confusion.
  • Read these related SSAS tips.

How to reduce MDX code redundancy in SQL Server Analysis Services SSAS

Problem

To query an Analysis Services cube, MDX is used as the query language. In most business settings, one would find a set of queries that are common across a number of user query requirements. To cater to this, even with a modest size IT team, there is a good chance that the same queries are developed redundantly either within a SSAS MDX script or repetitively in an ad-hoc manner in client applications. In this tip we would look at how to reuse queries without redeveloping them over and over.

Solution

Technically speaking, we have an issue of code refactoring and the intention is to centralize redundantly formed queries for ease of use and to improve performance in certain cases. In MDX, a construct is available that serves almost exactly the problem at hand, and it's called Named Sets. I will take it for granted that the reader has a working knowledge of MDX and SSAS.
Named Sets have two types, Static and Dynamic. Dynamic named sets were introduced in SQL Server 2008 and in this tip we will be discussing static named sets only.
Before we start with an example to understand named sets, we should be aware of different query scopes in which we can create and use named sets. Query scopes can be classified in three categories - Query, Session and Global. We will not discuss these scopes in detail as the scope names are relevant enough to reflect the meaning.
Let's say that the IT team has been assigned the task of addressing a number of queries that are used by a number of business analysts. Generally, ad-hoc requirements are assigned in an ad-hoc manner to developers, and these queries are formed in client applications like SSRS reports for example.
As a first step towards addressing the requirements, we should creating a process to track these queries. In the most simple form, one can create an Excel matrix of dimensions vs measuregroups, similar to the dimension usage tab and track each query in the matrix. The benefit is that by tracking queries, one would be able to figure out repeated queries that qualify for named sets or reuse existing queries without spending time and resources to redevelop existing queries. There is no standard template for this purpose, and one can design and develop a tracking sheet that best suits one's IT implementation vs business requirements process.
When a query qualifies to be created as a named set by the tracking mechanism described above, one needs to think of the scope in which the named sets should be used. In my personal opinion:
  • If the query is too complex and huge, one can create a named set to use the same as an alias at multiple places in a query.
  • If the same query is required across a session, for e.g. multiple reports accessed using the same connection, creating named sets in session scope is advisable.
  • Generally speaking, there is a very thin line between session scope and global scope. Unless you have a single odd user who uses the same query across the session and no other users require the same kind of query, one should create named sets in global scope.
To create a named set in global scope, you need to define it in the "Calculations" tab of the cube. The benefit of static named sets in the global scope is that they are already evaluated at the time the cube is processed. Keep in view that static named sets help in a limited set of scenarios, where data do not change often, for the rest we can use dynamic named sets. In my personal opinion, if you use a very complex filter criteria in the "WHERE" clause of your MDX query, it's worth considering a static named set. From an implementation perspective, one of the examples where static named sets are useful, is to populate a series of filters/drop-downs across various scorecards in your dashboard.
An example of a named set can be seen in the below screenshot. A good source to learn about static named sets are examples in the SSAS Samples project that ships with SQL Server 2008, and the below screenshot is from that source.

After the named set is created, post processing of the cube, you can see the named set as shown in the below screenshot.

Next Steps

  • Create static named sets of a few commonly used sets that you use in your filter criteria, and use the same in your query.
  • Measure the performance of using a static named set against using the same query in an ad-hoc manner, by executing the query to retrieve the same set using a named set and through ad-hoc queries.
  • Read these additional SSAS tips

How To Implement Proactive Caching in SQL Server Analysis Services (SSAS) 2005

Problem
We have chosen MOLAP storage in order to maximize the query performance of our cubes.  Since we have a number of cubes we are now focused on coming up with a strategy for keeping the cubes up to date as the data in our warehouse changes frequently.  Can you give us the details on how we go about implementing the Proactive Caching feature in SQL Server Analysis Services 2005?
Solution
SSAS supports three storage modes:
  • MOLAP - stores detailed data and aggregations in a compressed, proprietary format; i.e. a complete copy of the data is made but query performance is excellent
  • HOLAP - stores aggregations same as MOLAP, detailed data is accessed as required from the relational data source
  • ROLAP - accesses detailed data and aggregations from the relational data source
Note that with MOLAP or HOLAP storage, the cube becomes out of date as soon as the relational data source changes.  Proactive Caching is a feature in SSAS that allows you to specify when to process a measure group partition or dimension as the data in the relational data source changes.  When Proactive Caching is implemented,  SSAS will handle keeping the cube up to date on its own, per the parameters you specify.  The alternative to Proactive Caching is to develop an SSIS package that processes the dimensions and measure group partitions; you would execute the SSIS package periodically. 
A typical scenario for Proactive Caching would be that you have a measure group partition where the latest data is added periodically then at some point the partition is merged with an existing partition.  The data for the partition is often determined based on the transaction date.  For instance you want the partition updated hourly during the day with new transactions, then at night you will merge the contents of the partition into a current week partition.  This approach allows you to process the partition often, keeping it up to date with the relational data source, while minimizing the amount of data that needs to be processed each time.  For additional details about measure group partitions, please refer to our earlier tip How To Define Measure Group Partitions in SQL Server Analysis Services (SSAS) 2005.
Proactive Caching supports three notification methods which inform SSAS that the relational data source has changed:
  • SQL Server (2000 and later)
  • Client Initiated
  • Scheduled Polling
SQL Server notification can only be used if the relational data source is a SQL Server 2000 or later database.  SQL Server raises trace events when data changes.  In order to catch these trace events, SSAS must connect to the SQL Server with administrator rights.  With SQL Server notifications, measure group partitions are always processed using Full Process which discards the contents of the partition and rebuilds it; dimensions are processed using Process Update which picks up inserts, updates, and deletes in the relational data source.
The client initiated notification is performed by sending a NotifyTableChange XMLA command to the SSAS server.  For example an SSIS package that updates the data warehouse could use the Analysis Services Execute DDL Task to send the NotifyTableChange XMLA command to the SSAS server every time the data warehouse update processing is completed. 
Scheduled polling simply queries the relational data source periodically to determine if the data has changed.
In this tip we will walk through the setup of Proactive Caching using the Adventure Works DW SSAS sample database that comes with SQL Server 2005.     
Implementing Proactive Caching
To begin launch SQL Server Management Studio (SSMS) from the Microsoft SQL Server 2005 program group and connect to an Analysis Services server.    
Step 1: Drill down to the partitions for the Internet Sales measure group in the Adventure Works cube:
Step 2: Right click on the Internet_Sales_2004 partition and select properties from the context menu; click Proactive Caching in the Select a page list box to display the following dialog:
There are a number of predefined settings as shown above.  The default setting is MOLAP with Proactive Caching disabled; i.e. SSAS does not automatically update the partition.  You can move the slider to any one of the standard settings and go with it; you can also start with a standard setting then customize it by clicking the Options button at the bottom of the dialog (not shown above).
Step 3: Move the slider in the Proactive Caching settings dialog to Scheduled MOLAP then click the Options button at the bottom of the dialog (not shown above); you will see the following Cache Settings on the General tab:   
For the Scheduled MOLAP setting, we see that the Update the cache periodically option is the only one selected, with the rebuild interval set to 1 day.  Thus the partition will be processed every day.
There are a number of other options on the Cache Settings dialog.  As you move the slider to the various standard settings you will see these how options change.  At this point let's describe these various options and settings:
  • Silence interval is the amount of time to wait after an update notification is received before processing the partition.  This allows for other updates before starting to process the partition.
  • Silence override interval is the maximum time to wait after an update notification is received before processing the partition.  Without the silence override interval you could conceivably have changes that keep occurring and the partition would never get processed.
  • Latency is the maximum amount of time that you want to use the MOLAQP cache after changes have occurred in the relational data source and before/while the partition is processed.  In other words you may continue to use the existing MOLAP cache for a period of time while it is being rebuilt.  Once the latency is reached the MOLAP cached is dropped and queries can be satisfied by going back to the relational data source.
  • Bring online immediately (when checked) will satisfy queries from the relational data source while the MOLAP cache is being rebuilt.
  • Enable ROLAP aggregations (when checked) will create materialized views for aggregations in the relational data source.
Step 4: Click the Notifications tab to review the available options:
For both SQL Server and Client initiated notifications you must specify the table(s) to be tracked; i.e. which table(s) do you care about if they change.
For the Client initiated notification you could use the Analysis Services Execute DDL Task in an SSIS package and send XMLA like the following:
<Command>
  <NotifyTableChange>
    <Provider>SQLOLEDB</Provider>
    <DataSource>localhost</DataSource>
    <InitialCatalog>AdventureWorksDW</InitialCatalog>   
    <TableNotifications>
      <TableNotification> 
        <DBTableName>FactInternetSales</DBTableName>
        <DBSchemaName>dbo</DBSchemaName>
      </TableNotification> 
    </TableNotifications>
  </NotifyTableChange>
</Command>
Step 5: Click the Scheduled polling radio button on the Notifications tab to review/setup this notification option:
The Polling interval specifies how often to poll the relational data source for changes.  The Polling Query is the query to be run to determine if there are any new rows.  The query must return one row with a single column; e.g. the MAX of a datetime column like LoadedDate.  SSAS maintains the value returned to determine when it changes, signifying that there is new data to load.  Click Enable incremental updates to extract just the new rows from the relational data source.  This requires entering the Processing Query which must properly identify the new rows using the single column returned in the Polling query.  Sample queries are shown below (you can't see the entire query in the dialog):
-- Add LoadedDate column to table
ALTER TABLE dbo.FactInternetSales
ADD LoadedDate DATETIME NOT NULL DEFAULT GETDATE()
-- Polling query
SELECT MAX(LoadedDate) LoadedDate 
FROM dbo.FactInternetSales
-- Processing query
SELECT * FROM dbo.FactInternetSales
WHERE LoadedDate > ? AND LoadedDate <= ?
Since the FactInternetSales table didn't have a suitable column to use for the queries, a DATETIME column was added.
You can experiment with the remaining Standard settings on the Proactive Caching properties dialog to get an idea of the basic capabilities.   In addition you can customize them to meet your specific requirements.
Next Steps
  • If you don't already have the AdventureWorks SSAS sample projects and databases available, you can download them here to get the starting point for this tip.  Click the AdventureWorksBICI.msi link.  Also click on the Release Notes link for the details on attaching the relational database.  The default install location for the project is C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project; you will see Enterprise and Standard folders.  We used the project in the Enterprise folder.
  • While Proactive Caching is a very interesting feature, you should consider carefully whether it's the right solution versus creating an SSIS package to process your dimensions and measure group partitions.

How To Enable User-Defined Hierarchies in SQL Server Analysis Services (SSAS)

ProblemI have a requirement to provide users with the capability to define their own reporting hierarchies for sales data. For instance they had a report which broke down sales by the four regional VPs then by office.  Later they created regions, assigned VPs to each region, then assigned offices to each VP.  Now they want to be able to create a number of other scenarios and choose which one when they run a report.  Can you provide some suggestions on how to do this? 
SolutionThere is a relatively simple solution to your problem that leverages the many-to-many dimension capability in an SSAS cube.  Let's start out with a simple dimensional model then expand upon it to allow multiple reporting hierarchies.  Schema 1 is shown below:
The above schema shows a Sales fact table, joined to a date dimension (Calendar) and an Office dimension.  This allows us to report on sales based on fiscal year and office.  Now let's introduce a revised schema to support your requirement of allowing users to define their own hierarchies for reporting.  Schema 2 is shown below:
The OfficeHierarchy table allows us to define multiple reporting hierarchies using a parent-child relationship.  The HierarchyName column value is defined by the users and appears in every row that belongs to a particular hierarchy.  Each row that we add to the table is either a parent (i.e. a grouping) or a child (i.e. an office).  Each child row belongs to a single group and contains an OfficeKey value which matches a row in the Office table.  Each parent row has one or more child rows, which can represent actual office(s), group(s) or both.  The OfficeHierarchyBridge table has a single row in it for every combination of OfficeHierarchy and Office.  The relationships between Office, OfficeHierarchyBidge and OfficeHierarchy will allow us to take advantage of the many-to-many dimension capability in SSAS.  In fact the cube wizard will automatically recognize the many-to-many dimension provided the foreign key relationships exist as shown in the above schema.
Let's build our first reporting hierarchy; we'll call it FY 2008 to represent our actual reporting hierarchy for our previous fiscal year.  We will add the following two staging tables to our schema.  Schema 3 is shown below:
We'll use the stgOfficeGroup table to define the groups in our hierarchy.  There is a parent-child relationship in the stgOfficeGroup table, allowing us to have nested groups.  We'll use the stgOfficeMapping table to associate an office with a group.
The stgOfficeGroup table is shown below with some sample data:
FY 2008 is a top-level group, and will be the name of our hierarchy.  The other four rows each represent a group and they are all children of the FY 2008 group.
A portion of the stgOfficeMapping table is shown below with some sample data:
Each row represents an office assigned to a group as defined in the stgOfficeGroup table.
Next is a sample T-SQL script that we can use to populate the OfficeHierarchy and OfficeHierarchyBridge tables from our staging tables:
declare @hierarchyname nvarchar(50)
set @hierarchyname = N'FY 2008'
-- step 1: insert groups
insert into dbo.OfficeHierarchy(
 HierarchyName
, Description
, stgGroupNumber
, stgGroupParent
)
select  
 @hierarchyname
, GroupName 
, GroupNumber
, GroupParent
from dbo.stgOfficeGroup
-- step 2: insert children
insert into dbo.OfficeHierarchy (
 HierarchyName
, OfficeKey
, Description 
, stgGroupParent
)
select
 @hierarchyname
, m.OfficeKey 
, o.OfficeName
, m.GroupNumber
from dbo.stgOfficeMapping m
join dbo.Office o on o.OfficeKey = m.OfficeKey
-- step 3: update ParentKey values
;with cte_group as (
 select
  OfficeHierarchyKey
 , stgGroupNumber
 from dbo.OfficeHierarchy 
 where HierarchyName = @hierarchyname
)
update dbo.OfficeHierarchy
set ParentKey = p.OfficeHierarchyKey
from dbo.OfficeHierarchy c
join cte_group p on p.stgGroupNumber = c.stgGroupParent
where c.HierarchyName = @hierarchyname
-- step 4: insert into OfficeHierarchyBridge
insert into dbo.OfficeHierarchyBridge (
 OfficeHierarchyKey
, OfficeKey
)
select
 OfficeHierarchyKey
, OfficeKey
from dbo.OfficeHierarchy
where HierarchyName = @hierarchyname
and OfficeKey IS NOT NULL
The following are the main points about the above script:
  • All rows from the stgOfficeGroup table are inserted into the OfficeHierarchy table.  The stgGroupNumber and stgGroupParent columns are used later to update the ParentKey with the OfficeHierarchyKey value which is an identity.
  • All rows from the stgOfficeMapping table are inserted into the OfficeHierarchy table; the stgGroupParent column is used later to update the ParentKey with the OfficeHierarchyKey value.
  • The ParentKey values are updated with the OfficeHierarchyKey value by joining the stgGroupParent values with the stgGroupNumber values for the HierarchyName we are processing.
  • The OfficeHierarchyBridge table is populated with the OfficeHierarchyKey and OfficeKey values, where the OfficeKey is not null; i.e. only child rows.
Now that we have populated the OfficeHierarchy and OfficeHierarchyBridge tables, we are ready to build an SSAS cube.  For the basics of building a cube, please refer to our earlier tip How To Build a Cube From an Existing Data Source Using SQL Server Analysis Services.  There are two settings in the Parent Key attribute of the Office Hierarchy dimension of our cube that should be highlighted:
Since the Office Hierarchy dimension will hold numerous hierarchies, we really don't want an All member which would cause us to see measure totals that are greater than the actual detail in our fact tables.  Therefore we set the IsAggregatable property to False.  The MembersWithData property is set to NonLeafDataHidden so that the parent (or group) does not also appear in its list of children.
Now we are ready to see our multiple hierarchies in action.  The next two screen shots show an Excel pivot table connected to the SSAS cube where the Hierarchy Name column is used as the filter.  First we'll show our pivot based on the FY 2008 hierarchy:
Next we'll show the pivot for our FY 2009 hierarchy:
The FY 2009 hierarchy was another hierarchy that was added utilizing the same staging tables and T-SQL script as shown above.  The Red, White and Blue groups are meant to represent regions.  Specific VPs are assigned to each region, and offices are assigned to each VP.
The benefit of this approach is that you can support multiple reporting hierarchies for any dimension by simply adding a bridge table and a <dimensionname>hierarchy table.  Adding the actual hierarchies themselves does not require any further schema changes.
Next Steps
  • For an excellent paper on many-to-many dimensions please take a look at this link.  A PDF and some sample SSAS projects are available for download.  This tip was based in part on the Multiple Hierarchies section.
  • Download the sample code and experiment with the multiple hierarchies.  See the README.txt file for the details on the sample code.

How To Define Measure Group Partitions in SQL Server Analysis

ProblemWe are anticipating building some very large cubes and would like to get a handle on how to partition our measure groups to provide flexibility in processing and administering our cubes.   Can you guide us through the steps to define multiple partitions for a measure group and provide some explanations?
SolutionThe default behavior in SSAS is to create a single partition for a measure group.  Creating multiple partitions for a measure group is definitely a best practice when you have very large fact tables.  The following are the advantages of multiple partitions:
  • Improved Performance - SSAS can query a smaller amount of data by isolating one or more partitions to query rather than the entire measure group.  SSAS can also query multiple partitions in parallel.
  • Flexible Configuration - each partition has its own storage mode; e.g. MOLAP, HOLAP or ROLAP and aggregation design.  You can pick the optimal storage mode and aggregation based on how often the data is queried, how much data there is, how often the data changes, etc.
  • Flexible Processing - a partition can be processed separately or in parallel; you can drop a partition without having to process the cube.  If you have a partition for the latest week, month, quarter, etc. you can process just the rows from the fact table that belong in the partition as opposed to processing the entire fact table.
  • Multiple Source Tables - each partition can optionally specify its own fact table, allowing the flexibility to physically split large fact tables into multiple tables.
  • Improved Scalability - you can take advantage of processing a partition on another SSAS server.
In this tip we will walk through the steps to define multiple partitions for a measure group.   As a starting point we will use the Adventure Works DW Standard Edition sample project that comes with SQL Server 2005.  This project was chosen because the Enterprise edition sample already has multiple partitions, while the Standard edition sample has only one partition per measure group.  We'll define a single partition for each year of data in the Internet Sales measure group.
When defining multiple partitions for a measure group there is one critical point that should be emphasized.  You must specify a query for each partition such that every row from the fact table gets included in one and only one partition.  The simplest way of doing this is to partition based on the Time dimension which is what we will do in the example. 
Note that multiple partitions are only supported in the Enterprise Edition of SQL Server 2005.  You can also use the Developer Edition in your development environment.
Creating Multiple Partitions
Business Intelligence Development Studio (BIDS) is the integrated development environment that comes with SQL Server 2005.  Launch it from the Microsoft SQL Server 2005 program group then open the Adventure Works DW Standard Edition project (default location is C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project\Standard).
Step 1: Edit the project properties by clicking Project, Properties from the top-level menu; set the Deployment Server Edition to Enterprise.  Multiple partitions are only available in the Enterprise Edition of SQL Server 2005 so you need to specify the Deployment Server Edition as Enterprise.
Step 2: Double click the Adventure Works cube in the Solution Explorer then click the Partitions tab to display the existing partitions (only the first partition is shown below). 
Click on Internet_Sales and edit the Partition Name to Internet_Sales_2001.
Step 3: Click the Internet_Sales partition (you should see the entire row highlighted as shown above) to display the partition properties.  Click the button in the Source property to display the binding:
Step 4: Change the Binding type from Table binding to Query binding and add the WHERE clause as shown below:
The idea is to change this partition so that it only has orders that were shipped in the year 2001.  The ShipDateKey column references the TimeKey column in the DimTime table (Time dimension) in the AdventureWorksDW database.  Run the following query to determine the ShipDateKey for the date 2001-12-31 and you will see that 184 is the TimeKey value:
SELECT TimeKey FROM DimTime
WHERE FullDateAlternateKey = '2001-12-31'
Step 5: Click New Partition (see hyperlink in Step 2 above) to add another partition to the Internet Sales measure group.  This will launch the Partition Wizard.  Select the dbo.FactInternetSales table under Available tables:
Step 6: Click Specify a query to restrict rows and add the WHERE clause as shown below:
The idea is to set this partition so that it only has orders that were shipped in the year 2002.  If you query the DimTime table you will find that the TimeKey value for 2002-12-31 is 549.
Step 7: Accept the defaults on the Processing and Storage Locations dialog:
Note that selecting Remote Analysis Services data source for the Processing location is a scale-out feature where you could actually process this partition on another SSAS server.
Step 8: Complete the Partition wizard; fill in the dialog as shown below:
Repeat steps 5 through 8 to create and configure partitions for the years 2003 and 2004.  
Step 9: Process the cube; right click on the Adventure Works cube in the Solution Explorer and select Process from the context menu. Accept the defaults on the Process Cube dialog; click Run (Run button not shown below):
You can also define measure group partitions using SQL Server Management Studio.  Connect to an Analysis Server, select a database and a cube, then right click on a measure group.  The context menu will have a New Partition option that launches the same Partition Wizard we used above.  There are also options to Merge Partitions and work with Aggregations.
Next Steps
  • If you don't already have the AdventureWorks SSAS sample projects and databases available, you can download them here to get the starting point for this tip.  Click the AdventureWorksBICI.msi link.  Also click on the Release Notes link for the details on attaching the relational database.  The default install location for the project is C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project; you will see Enterprise and Standard folders.  We used the project in the Standard folder.
  • Keep in mind that multiple partitions for a measure group provides several advantages as discussed; a good time to think about a partitioning strategy is while you're developing a cube.  You can change the partitions after deployment; you may want to use SQL Server Management Studio in this case.
  • You can download the modified version of the AdventureWorks Standard SSAS project used in this tip here.