Maintenance for BI planning solutions and scenarios

Article
07/22/2014

Applies to: SharePoint Server 2010 Enterprise

Cube, dimension and hierarchy management is performed by using SQL Server Business Intelligence Development Studio (BIDS) and SQL Server Management Studio (SSMS).

SQL Server Management Studio (SSMS)

Use SSMS for the following:

Update dimension members and properties from dimension tables.
Update hierarchy tables for parent-child relationships.
Update fact tables.
Design schema relationships between dimension, hierarchy and fact tables via views.
Stored procedure development in T-SQL for business logic.
- Currency conversion rules.
- Custom spreading logic.
- Data versioning (copying from one slice of fact data to another slice).

From SSMS, you can directly update data tables by the following:

Right-click the data table that you want to edit.
Select Edit Top 200 Rows.

Or you can decide to script out the updates:

Right-click the data table that you want to edit.
Select Script Table as.

SQL Server Business Intelligence Development Studio (BIDS)

Use BIDS for the following:

Develop SSIS packages for ETL.
Design OLAP dimension, hierarchy, and cube.
Design MdxScript rules within cubes.
Design data partitioning within cubes.
OLAP security management via roles.

Update cube

There are many things that can be updated in a cube and it is very important for each kind of update to require a full test before rolling out to production. Some considerations to note when maintaining a cube that is already in production:

Updating a cube’s model dimension usage in measuregroup
- Removing model dimensions can break existing definitions for PivotTables and cause existing MdxScript rules to fail. In addition, the fact table should be reviewed about how to correctly address data that is sliced by the removed dimensionality based on the requirement of the business.
- Adding additional model dimensions will typically cause less immediate breaks on the overall system. However there are still many things to consider. The fact table must correctly default the values for the new model dimension on existing values or else may require a full reloading. Pre-submitted data must be re-entered by IWs if this new dimensionality cannot be properly defaulted, potentially causing a large IW process change. PivotTables and MdxScript rules should continue to work. However, it is best to review all MdxScript rules as the new dimensionality will most likely have altered how certain calculations should work, for example if there was a rule that requires all the dimension members to be leafs in the calculation scope.

Updating partitions in a measuregroup
- Adding/removing partitions should typically have no functionality effect on the IWs as this is a technical level change. Performance can be increased with intelligent partitioning scheme for the data and minimal effect should be experienced when unused partitions are removed.

Updating measuregroup usage
- Removing a measuregroup can effect MdxScript rules and existing PivotTable definitions. This is because there will typically be measures that exist on the measuregroup that is used in some capacity somewhere in the system, whether it is in rules or PivotTables.
- Adding additional measuregroups should have minimal effect on the IWs. It will only be adding new functionality without affecting existing behavior.

Updating MdxScript based rules
- Calculation updates will affect how the data is viewed by the IW. Once an update is made to the rules that give the correct business logic, it is very important to test their performance characteristics. Rules written in MdxScript can potentially cause significant performance degradations that for the SQL Server Analysis Services server if written in a non-optimal manner.

Update dimension and hierarchies

Dimension and hierarchies are often updated as new information is required by IWs to satisfy their business requirements. For instance, it is common to see the following requests:

IW require the addition of a new hierarchy view.
IW requires new member properties to show on their reports.
IW requires changes to existing hierarchies to reflect organizational changes.

How do we address some of these of these scenarios and what are the effects when they make such changes? To start, any changes that you made to a production system must first be thoroughly tested out in a test environment in order to better understand the full change impact. By using that said, there are some changes that are more expensive to perform when you compare it to others and we will explore them here:

Updating dimension member properties
- Adding new properties/attribute should have minimal effect on existing functionality. This is considered a low risk change.
- Removing properties or renaming them should be avoided in production environments. Pivot Tables, MdxScript rules and hierarchies that use the attribute in its definition can become broken.
- Updates to the dimension properties will affect any hierarchies that are built from the related attributes. See the following for more information.

Updating dimension members
- All dimension members can potentially have data that was stored against it in the cube. Thus, deleting any member from the dimension should also correctly handle the associated data in the fact table.

Updating existing hierarchies
- Parent-child hierarchies
- Be careful when you move hierarchy members around in a pc hierarchy as you might move a member from the lowest level of the hierarchy and make it into a parent member and vice versa. The implication is that there might be data sitting at intermediate levels that may appear incorrect from an IW’s perspective as it does not appear to be the sum of the children members.
- Level-based hierarchies
- When the dimension table columns are updated with new values, the attributes that are associated with those columns are also updated. Here, the level hierarchy will be automatically updated based on the updated attributes after a dimension process in SQL Server Analysis Services.
- General hierarchy change effect
- Updates to the hierarchy structure can cause PivotTables and MdxScript rules to break. Review MdxScript rules to see how they are being used and if the changes to the hierarchy structure will require updating the rules. Also, review any PivotTables to see how these will be affected.

Adding new hierarchies is low risk as it will be new functionality. Removing hierarchies will have the same implications as covered in ‘General hierarchy change impact.’

Maintenance for BI planning solutions and scenarios

SQL Server Management Studio (SSMS)

SQL Server Business Intelligence Development Studio (BIDS)

Update cube

Update dimension and hierarchies

See Also

Concepts

Additional resources