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
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
Arguments
| Element | Description |
| MyApplicationDatabase | Name of the application database that the script will run against. |
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. |
| Indicates a successful insertion of members into the TimeDataView and Scenario dimensions. |
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)
No comments:
Post a Comment