Enabling NativeSQL/NativeMDX Rules

In order to enable Native SQL and Native MDX rules you must perform the following two steps:

• Enable Native SQL/MDX Rules in the Administration Console
• Have a DBA (Database Administrator) activate the rule in the back end

The above steps cannot be performed by a user who only has Business Modeler rights. In order to perform these steps the user will require Global Administrator (GA) rights.

Enable Native SQL/MDX Rules in the Administration Console

The first step can be performed when creating a new PerformancePoint Application or after the Application has been created. It is quite possible that Native SQL/MDX rules have not been enabled for the Application your are using because the default setting is set to false. To enable these rule types the (GA) needs to access the Planning Administration Console and select the appropriate planning application. In our case I will select Alpine Ski House:

Now select Edit to edit the application settings:

The Alpine Ski House Application is currently not configured to use Native SQL/MDX rules. Simple select the check box and then click OK.

At this point the GA can now inform the Business Modeler that they can use native SQL/MDX rules.

Activating the Rule in the Database

To demonstrate how to do this I will create a very simple Native SQL rule (it doesn't get much simpler than this). The rule below will provide no functionality to the Alpine Ski House Application (that will be a topic for a future post) but it will allow me to show you the necessary steps required to enable a native rule. Here is the SQL statement we will use for the rule:

select 1

Now we need to create the rule in the Business Modeler. I going to create a new rule set in the Corporate Costs model in the Alpine Ski House Application. Make sure the model is checked out and select the Business Rules tab. Now select Create a Rule Set. Enter Dev for the Name and Label. Make sure the rule set is Procedural and that is a Root rule set:

Select OK. Now we can create a native SQL rule. Right-Click the Dev Rule Set and select Create a New Rule.... . Enter Test SQL for the name and label of the rule. Set the rule type to Assignment and the implementation to Native SQL.

The first thing you will probably notice is that the rule has a status of Inactive. Do not change the status of the rule. After you have created the business rule you must save the rule with the Inactive Status or you will get an error. Now we can enter our simple business rule:

select 1

Once the rule has been entered you can now save the model site. At this point you will need a GA or a DBA to activate the rule in the Application Database. The GA or DBA will use SQL Server Management Studio to logon to the Application database and look for the rule in the RuleSetsOrRules table. Use the following query to find the rule we created in the Alpine_Ski_House_AppDB:

select * from dbo.RuleSetsOrRules
where RuleSetOrRuleLabel = 'Test SQL'

When you run this query you will notice that the IsActivated field is set to 0. We need to update this value to 1 so that the rule can be activated and executed from the Modeler. Now run the following query to update the IsActivated field to 1.

update dbo.RuleSetsOrRules
Set IsActivated = 1
where RuleSetOrRuleLabel= 'Test SQL'

Total row affected for the update query should equal one. If you want to check that the value was updated you can run this query again:

select * from dbo.RuleSetsOrRules
where RuleSetOrRuleLabel = 'Test SQL'

The IsActivated field will now contain the value of 1:

Now the GA or DBA can inform the Business Modeler that the rule is Active. Once the rule has been activated the Business Modeler can execute the rule. One thing to note though is that if you are creating Native SQL/MDX rules you will want to thoroughly test them outside of the Business Modeler because every time you modify and save the rule PerformancePoint will set the Status flag of the rule to InActive. This means that you have to save the rule and then get the GA or DBA to change the status to Active. Even if you are a GA or DBA rights this task would become tedious. Therefore it is a good idea to use a tool like SQL Server Management Studio to create your native rules. If you only have Business Modeler rights then you will want to work closely with your DBA to develop these rules.

Lets try running the rule. When you refresh the Business Modeler the rule Status will change from InActive to Active. You can right-click the rule and select Execute Rule Set or Rule... . You will probably be prompted to Deploy Rules and Variables. Answer yes to this. Once the rules and variables have been deployed the rule will be executed. If everything was setup correctly you should see the following message box:

Troy

Technorati Tags: performance point, performancepoint, budgeting, planning, business intelligence, microsoft