Cube modeling for Write-back in BI planning solutions and scenarios

Article
07/22/2014

Applies to: SharePoint Server 2010 Enterprise

In this article:

Cube sizing and recommendations
Use of measure groups
Use of Write-back table and considerations
Multi-user Write-back
Use of partitions and cube settings (MOLAP/ROLAP)
Use of proactive caching

Cube sizing and recommendations

Properly designed cubes will have many considerations taken into account. These considerations will impact the size and overall performance of the cube.

Avoid putting all logic into a single massive cube using all available dimensions. Not only does this make the cube unnecessarily large but it also makes it very hard to maintain and difficult to consume by IWs.

Use MOLAP partitions for non-volatile data.

Load data that is relevant to the planning process. Avoid loading all available data from a source system into your planning cubes. Separate out the data that is needed for the core planning with what is needed in reporting. Planning cubes will need to perform many what-if scenarios and the smaller the cube, the better overall experience for all IWs

Pre-calculate fact data whenever possible so as to avoid needing MdxScript rules to run and calculate. Reporting cubes are ideal candidates for pre-calculating the result directly to the fact table. This technique will lead to better query performance and scale

Use of measure groups

Measure groups are useful to group data that have the same dimensionality together within a single cube. For instance, data in the HR Budget cube is distributed across two measure groups, one for the budget data that have dimensionality of ‘Geography’, ‘Metric’, ‘Time’ and ‘Employee’ while the assumption data has dimensionality of ‘Pay Grade’ and ‘Time.’ Keeping the data at the dimensionality will result in better cube design, better and easier to manage rules and increased performance.

Use of Write-back table and considerations

In Microsoft SQL Server 2008 Analysis Services (SSAS), write-back tables with MOLAP storage have been improved to deliver faster data updates from user interaction. The write-back table will store a running delta for each cell update made by the IW. The write-back table will store all user updates to the cube including an audit trail of who submitted what and when.

To configure your measure group to have a partition for writeback, set up a MOLAP partitioning dedicated for write-back scenario. (For more information, see Planning modeling and reporting guide for BI planning solutions and scenarios.)

Value_0	MemberId_1	MemberId_2	MemberId_3	MemberId_4	MemberId_5	MemberId_6	MS_Audit_Time_7	MS_Audit_User_8
82.27	1	20100500	2	12	210	1	11:11:34 PM	CORP\jeffwan
82.27	1	20100500	2	13	210	1	11:11:34 PM	CORP\jeffwan
-12997.73	1	20100500	2	14	210	1	11:11:34 PM	CORP\jeffwan

Multi-user Write-back

Multi-user write-back is supported with writeback tables in SQL Server Analysis Services. The behavior of having multiple IWs writing data into the same slice is last person wins. All data entry by IWs will have their transactions audited by the writeback table.

It is recommended that the data entry process is configured in such a way that each IW submits and updates data in their own unique slice of data within the cube. This will lead to better data accountability and an improved user experience overall as data submitted by one IW is not arbitrarily lost or overwritten by another IW’s submission.

Use of partitions and cube settings (MOLAP/ROLAP)

MOLAP storage for partitions will enables the best query time performance in SQL Server Analysis Services. MOLAP storage is ideal for data that is non-volatile, or to put in another way that is static and non-changing. Static data in this sense refer to the underlying fact values not changing from such processes as rule execution, data load and or user entry. Data that is ‘Actual’ and data that is considered ‘Historic’ are great candidates for storing together in a MOLAP partition. Static data can be processed once and will not requiring future processing unless there is a change to the partition’s data. This can be helpful when you process of large partitions can take considerable time.

Data that is volatile and requires near real time data updates should consider using ROLAP as the storage mechanism for the partition. ROLAP will give the most up to date data when queried upon. You can configure the storage mechanism of each partition to be different depending on the kind of data that it will store, whether static or volatile.

For more information about MOLAP/ROLAP partition settings, see Planning modeling and reporting guide for BI planning solutions and scenarios.

Use of proactive caching

In planning solutions, data can be updated in many ways, including the following:

End-user data submission
Data loads for new and updated data
Updates from business rule calculations done at the relational level

Here we will explore a useful feature in SQL Server Analysis Services that enables automatic data updates for the cube when data changes on the underlying data source. Proactive caching is a great feature that automates bringing in new updates to the cube. We will show how this can be configured on a partition of the cube to detect changes from our SQL Server 2008 relational fact table by using change notification.

Note

To configure proactive caching for planning cube, see Planning modeling and reporting guide for BI planning solutions and scenarios. For more information, see Proactive Caching (Partitions).