Planning modeling and reporting guide for BI planning solutions and scenarios

 

Applies to: SharePoint Server 2010 Enterprise

Planning modeling and reporting guide

Configure measure group with writeback partition

  1. Find Solution Explorer and select the Forecast cube.

  2. Locate the Partition tab.

  3. Right-click an existing partition and select Writeback Settings.

  4. Specify the name of the table for the writeback that will be created on the data source. Keep the selection to MOLAP for optimal performance.

  5. When you are finished with creating the writeback table, there should be a new instance of the partition that is dimmed.

After saving and processing the database, a data table will be created on the underlying relational database to store writeback data.

ROLAP/MOLAP partition settings

  • Right-click a partition.

  • Select Storage Settings.

  • Select standard setting and drag slider to ROLAP/MOLAP.

Enable “Ad-hoc Distributed Queries”

sp_configure 'show advanced options', 1

reconfigure

sp_configure 'Ad Hoc Distributed Queries', 1

reconfigure

Query OLAP from SQL

Select *

From OpenRowset

(

'MSOLAP'

,'Data Source=localhost;Initial Catalog=AdventureWorks Planning;'

,'select {[Measures].[Value]} on 0,

non empty

{

{DESCENDANTS([Account].[IncomeStatement].[REV], 10000, LEAVES), DESCENDANTS([Account].[IncomeStatement].[EXP], 10000, LEAVES)}

*DESCENDANTS([Geography].[Geographies].[All], 10000, LEAVES)

*DESCENDANTS([Time].[Monthly].[FY2010], 10000, LEAVES)

*{[Scenario].[Scenarios].[Fcst]}

*{[Product].[All_Product].members}

}

on 1 from Forecast'

Security and role setup

  1. Open Development Studio.

  2. From the File menu, select Open, select Analysis Services Database, and then connect to your instance.

  3. Locate the Solution Explorer pane.

  4. Look under the database node for a folder named Roles.

  5. Right-click New Role.

  6. For our solution, we will create a role for each user and define their security within it. In the following example, we will create a security role for a particular user and have their security limited to read/write ability onto the Seattle region of geography. All other geographies will be inaccessible by this user.

  7. You can define security on the cube level for broad granting of rights. For instance, here we are limiting data input for the Forecast and HR Budget cubes but not to the Financial Consolidation, Exchange Rates or HR Pay Rates cubes.

  8. Dimension security can also be defined by selecting the members in the hierarchy by using the check boxes to indicate what is included in the security definition.

See Also

Concepts

Basic planning scenarios in BI planning solutions and scenarios
Planning the data mart for BI planning solutions and scenarios
Planning modeling concepts in BI planning solutions and scenarios
Cube modeling for Write-back in BI planning solutions and scenarios
Performance considerations and approaches in BI planning solutions and scenarios
Cube modeling with Excel PowerPivot in BI planning solutions and scenarios
Create reports and forms for BI planning solutions and scenarios
Submit plan data for BI planning solutions and scenarios
Workflow actions, workflow diagram, and SharePoint workflow setup for BI planning solutions and scenarios
Audit tracking for BI planning solutions and scenarios
Administration for BI planning solutions and scenarios
Calculations for BI planning solutions and scenarios
Additional planning functions for BI planning solutions and scenarios
Migration for BI planning solutions and scenarios
Maintenance for BI planning solutions and scenarios
Corporate to subsidiary management for BI planning solutions and scenarios
Planning modeling and reporting guide for BI planning solutions and scenarios
Building planning functionalities guide for BI planning solutions and scenarios
Planning and budgeting calculation examples for BI planning solutions and scenarios