Planning modeling and reporting guide for BI planning solutions and scenarios


Applies to: SharePoint Server 2010 Enterprise

Topic Last Modified: 2011-01-28

  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.

  • Right-click a partition.

  • Select Storage Settings.

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

sp_configure 'show advanced options', 1


sp_configure 'Ad Hoc Distributed Queries', 1


Select *

From OpenRowset



,'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)




on 1 from Forecast'

  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.