Export (0) Print
Expand All

Planning modeling and reporting guide for BI planning solutions and scenarios

SharePoint 2010

Published: January 27, 2011

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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft