Do you know that you can use Essbase Substitution Variables in Essbase SQL Load rules? For example, you can use the substitution variables in the where clause of a SQL query to fetch only a specific year or month corresponding to the value of the variables. This is a very good feature if you want your SQL data loads to be controlled using the Essbase Substitution variables. Read further to see how this can be achieved.
The figure below shows a snapshot of the substitution variables as seen using EAS. Note the variables highlighted in red. Apart from the ‘CurrentYear’, ‘CurrMonth’ variable, there is also a variable called ‘OracleEnv’
Then, when you create an Essbase SQL Load Rule, you can use the variables as shown below. In the example below, I am querying the data view called ‘DATA_VIEW’ to extract data for the values pointed to by the ‘CurrentYear’ (value=FY15) and ‘CurrMonth’ (value=Jun) variables. Please note that you would need to update the SQL query if the year and month is not the same format as the value of your substitution variables (for e.g. 2015 instead of FY15 and JUNE instead of Jun), but this can be easily achieved using common string manipulation functions in the SQL Query.
In the above example, the format of the variable value (FY15, Jun) is such that I can use it both in the SQL Query and in Essbase Calc Scripts. There may be cases where the format may be different bcause of your query requirements. Say that you want to pull multiple months in the SQL Query, so your where clause becomes “where TRANS_MONTH in (‘Jan’,Feb’,’Mar’,’Apr’) “. If you assign the value (‘Jan’,Feb’,’Mar’,’Apr’) or ‘Jan’,Feb’,’Mar’,’Apr’ to the CurrMonth variable, this will not be acceptable to be used in an Essbase Calc Script. In this case I have resorted to using two variables, one which is used in SQL Query and one which is used in Essbase Calc Scripts.
Also note that I am using another substitution variable for SQL data sources called ‘OracleEnv’ the value of which is ‘ORCLPROD’ and corresponds to the name of the ODBC connection that I have set up to connect to a specific instance of Oracle. See snapshot below. This is very handy when migrating substitution variables from one environment to another. You would need to simply change the value of the ‘OracleEnv’ variable from ‘ORCLPROD’ to say ‘ORCLDEV’ (You would need to setup an ODBC connection called ORCLDEV). Instead of changing all the SQL Load Rules, you would now simply do one change in EAS and all rules will get updated.
Please note that when you are using MS SQL Server as your data source, you need to specify the database name in the “Connect” settings under “Database”. So the flexibilty of just changing the substitution variable value to a different environment may not be help when using MS SQL Server.