Script to create dimension members for rule templates in Planning Business Modeler

To use a rule that you create with some of the predefined templates, you must create dimension members that will store the results of the calculations that the rule performs. To create the required dimension members, you can run the mnt_timedataview.sql script.

The predefined templates in the following list require the dimension members that are created by the mnt_timedataview.sql script.

  • Variance from last year template

  • Variance from last period template

  • Percent variance from last year template

  • Percent variance from last period template

  • Variance between two scenarios template

  • Percent variance between two scenarios template

  • Year To Date

  • Half-year to date

  • Trimester to date

  • Quarter to date

  • Month to date

  • Moving average

  • Moving total

In this topic

Script: mnt_timedataview.sql

Arguments

Return values

Remarks

Script: mnt_timedataview.sql

This script creates members in the TimeDataView and Scenario dimensions. The script is called by SQLCMD. It creates members that are specifically customized for certain predefined business rule templates.

The following command line shows the syntax for using this script

sqlcmd -i mnt_timedataview.sql -v RootDatabaseName = MyApplicationDatabase

Top of Page

Arguments

Element

Description

MyApplicationDatabase

Name of the application database that the script will run against.

Top of Page

Return values

The following table shows possible return values for this script.

Value

Description

'RootDatabaseName' scripting variable not defined.

Indicates that the name of MyApplicationDatabase was not provided on the command line.

Msg 911, Level 16, State 1, Server ThisServer, Line NN

Indicates that Planning Server could not locate an entry in sysdatabases for the specified database.

This error typically occurs when MyApplicationDatabase is not available on the server.

Changed database context to 'MyApplicationDatabase'.
(1 rows affected)

Indicates a successful insertion of members into the TimeDataView and Scenario dimensions.

Top of Page

Remarks

The following code contains the mnt_timedataview.sql script. You can copy this code to an instance of SQL Server on your computer and run the script.

** Copyright (C) 2004 Microsoft Corporation. All rights reserved. 
**/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'$(RootDatabaseName)')
USE $(RootDatabaseName)
/* add TimeDataView dimension member for MonthToDate template */
insert into D_TimeDataView
(MemberId, SourceMemberID, Label, Name, Description, OwnerId, ChangeDatetime, LoadingControlID, CreateDatetime, SequenceNumber)
values (5001, null,'MonthToDate','Month To Date','Month To Date',0,getdate(),5000,getdate(),null)
/* add TimeDataView dimension member for QuarterToDate template */
insert into D_TimeDataView
(MemberId, SourceMemberID, Label, Name, Description, OwnerId, ChangeDatetime, LoadingControlID, CreateDatetime, SequenceNumber)
values (5002, null,'QuarterToDate','Quarter To Date','Quarter To Date',0,getdate(),5000,getdate(),null)
/* add TimeDataView dimension member for HalfToDate template */
insert into D_TimeDataView
(MemberId, SourceMemberID, Label, Name, Description, OwnerId, ChangeDatetime, LoadingControlID, CreateDatetime, SequenceNumber)
values (5003, null,'HalfToDate','Half To Date','Quarter To Date',0,getdate(),5000,getdate(),null)
/* add TimeDataView dimension member for TrimesterToDate template */
insert into D_TimeDataView
(MemberId, SourceMemberID, Label, Name, Description, OwnerId, ChangeDatetime, LoadingControlID, CreateDatetime, SequenceNumber)
values (5004, null,'TrimesterToDate','Trimester To Date','Trimester To Date',0,getdate(),5000,getdate(),null)
/* add TimeDataView dimension member for Moving_Total template */
insert into D_TimeDataView
(MemberId, SourceMemberID, Label, Name, Description, OwnerId, ChangeDatetime, LoadingControlID, CreateDatetime, SequenceNumber)
values (5005, null,'Moving_Total','Moving Total','Moving Total',0,getdate(),5000,getdate(),null)
/* add TimeDataView dimension member for Moving_Average template */
insert into D_TimeDataView
(MemberId, SourceMemberID, Label, Name, Description, OwnerId, ChangeDatetime, LoadingControlID, CreateDatetime, SequenceNumber)
values (5006, null,'Moving_Average','Moving Average','Moving Average',0,getdate(),5000,getdate(),null)
/* add TimeDataView dimension member for VARIANCELASTYEAR template */
insert into D_TimeDataView
(MemberId, SourceMemberID, Label, Name, Description, OwnerId, ChangeDatetime, LoadingControlID, CreateDatetime, SequenceNumber)
values (5007, null,'VARIANCELASTYEAR','Variance To Last Year','Variance To Last Year',0,getdate(),5000,getdate(),null)
/* add TimeDataView dimension member for VARIANCEPRIORPERIOD template */
insert into D_TimeDataView
(MemberId, SourceMemberID, Label, Name, Description, OwnerId, ChangeDatetime, LoadingControlID, CreateDatetime, SequenceNumber)
values (5008, null,'VARIANCEPRIORPERIOD','Variance To Prior Period','Variance To Prior Period',0,getdate(),5000,getdate(),null)
/* add TimeDataView dimension member for VARPCTLASTYEAR template */
insert into D_TimeDataView
(MemberId, SourceMemberID, Label, Name, Description, OwnerId, ChangeDatetime, LoadingControlID, CreateDatetime, SequenceNumber)
values (5009, null,'VARPCTLASTYEAR','Variance % To Last Year','Variance % To Prior Period',0,getdate(),5000,getdate(),null)
/* add TimeDataView dimension member for VARPCTPRIORPERIOD template */
insert into D_TimeDataView
(MemberId, SourceMemberID, Label, Name, Description, OwnerId, ChangeDatetime, LoadingControlID, CreateDatetime, SequenceNumber)
values (5010, null,'VARPCTPRIORPERIOD','Variance % To Prior Period','Variance % To Prior Period',0,getdate(),5000,getdate(),null)
/* remove VarianceActual-Budget TimeDataView dimension member */
delete from D_TimeDataView
where label = 'VarianceActual-Budget'
/* remove VarPctActual-Budget TimeDataView dimension member */
delete from D_TimeDataView
where label = 'VarPctActual-Budget'
/* add Scenario dimension member for VarianceActual-Budget template */
insert into D_Scenario
(MemberId, SourceMemberID, Label, Name, Description, OwnerId, ChangeDatetime, LoadingControlID, CreateDatetime, SequenceNumber)
values (5011, null,'VarianceActual-Budget','Variance Actual-Budget','Variance Actual-Budget',0,getdate(),5000,getdate(),null)
/* add Scenario dimension member for VarPctActual-Budget template */
insert into D_Scenario
(MemberId, SourceMemberID, Label, Name, Description, OwnerId, ChangeDatetime, LoadingControlID, CreateDatetime, SequenceNumber)
values (5012, null,'VarPctActual-Budget','Variance % Actual-Budget','Variance % Actual-Budget',0,getdate(),5000,getdate(),null)

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×