Date Dimension Display Problem Solution

I previously wrote a post about dates not displaying correctly in SmartView and Financial Reporting Studio when using 11.1.1.x (http://whatsallthehype-essbase.blogspot.com/2010/04/date-dimension-display-problem.html). Recently, I have seen a couple of posts about this exact issue on the forums so I thought I would write another post detail one alternative for addressing this issue.


To review, this was identified as a bug by Oracle that was fixed in 11.1.2.x. For those of us that are not yet on that version, this is still an issue. As noted in my previous post, the date is stored correctly, but just being displayed one day less than the date stored. For example: 2/11/2013 displays as 2/10/2013. I had suggested loading the data to one member and then using formulas to display the correct dates.

So to expound upon my previous suggestion, here is how we are going to handle the date display issue.

Let’s say we have an effective date that needs to be stored. We are setting up 2 date measures:

1) Effective_Date – Date type member within account dimension where we will load and store the date

2) Effective Date – Date type member within account dimension where we will perform a calculation to roll the date forward by one day so that the date displayed is correct.

The formula in our Effective Date member utilizes the DateRoll MDX formula. The formula would be:

DateRoll([Hire_Date], DP_DAY, 1)

Now we have our data stored correctly in one member(Effective_Date) and have a member that can be used for reporting purposes (Effective Date). For end users, we will likely set up a filter that provides them no access to Effective_Date so there isn’t confusion as to which date is the correct date. In addition or as an alternative, you can create a small rollup within your account dimension that is for the stored dates. You can even label it as a do not use. It is really preference.

The beauty of this plan is that once we do upgraded to 11.1.2.x, we can remove our Effective Date formulated member and add “Effective Date” as an alias to our Effective_Date member. This will allow the process to be seamless to our users as they almost always use alias table when doing their ad hoc analysis. So having “Effective Date” on one of their retrieves, while pulling a different member, should still return the same results, eliminating the need to update any retrieves where this data was being utilized. Note: If you have applied a filter to limit access to these members then you will just need to remove that filter. If you decide to add a node to “hide” the stored members in prior to upgrade, just remember to move them back into the main hierarchy so users can easily find them.

As a note: The DateRoll function can be used to roll a date backwards or forwards by any part of the date (Year, Quarter, Month, Week, or Day).

SQL and PL SQL Loading Via EAS

We have been doing quite a bit of SQL and PL SQL loading using EAS as of late. I have found the user interface can be confusing and the user guide to not be of much assistance. Below is what we have found to work for our needs.

First step is to get the ODBC set up, which the user guide can walk you through if you aren’t aware how to.

Once that is set up, within EAS you create a new load rule or modify and existing load rule if you prefer. Then navigate to File->Open SQL. You will first be prompted to select your Essbase Server, Application, and Database.

At first the next screen looks straight forward, but really there are a lot of extra fields that you may or may not need to use.

 
For instance to load via PL SQL, from a source such as PeopleSoft, you will want to select the Oracle Wire Protocol option from the SQL data sources section. You will enter your SQL statement by either using the boxes provided or by entering the entire statement into the Select section. Either of these methods will work. The next step is to select OK/Retrieve at the bottom of the screen. Note: You do not need to use the Connect section at all. Trying to fill in the Server, Application, Database, or Dictionary path in the Connect section will result in an error stating “Failed to Establish Connection With SQL Database Server. See log for more information.”
 
To load via SQL, you will want to select the SQLServer Wire Protocol option from the SQL data source section and enter your SQL statement into the provided boxes or into the Select section. Depending on how you have identified the tables in the from section of your query, you may have to provide the database name in the Connect section. If the database is included in the table description (ex: <>.dbo.<<>>) you will likely be able to move straight to selecting the OK/Retrieve button without issues. If you have not identified the database name in the table description, place the database name in the Connect section to move forward.  
Once you have selected OK/Retrieve, you will be prompted with the SQL Connect box where you are prompted for a user name and password, which will be your PL SQL or SQL user name and password. As a note, we found we had to have SQL authentication for this to work. Using Windows authentication produced errors and we were unable to connect. The Essbase Server, Application, and Database should default to your previous selection.

After selecting OK, your data will populate into your Data Prep Editor screen. From here you can create a load rule as you normally would. You can create both load and build load rules to run off of these data sources.

If you still receive a Failure to Establish Connection error message and you are sure of the passwords you are using, then you will want to look at the SQL next. Here are things to look for:

• Make sure you have not placed the word “Select” into the SQL box – It utilize the Select from the header on the box
• Remove table and column aliases – If you have multiple tables that share fields, you will need to put the entire table name in front of the field throughout your query
• Check your more complex SQL functions to make sure they are compatible with the SQL loader within EAS – Try removing the complex statements and see if you can connect
• Remove anything you have commented out – Leave the SQL statement as purely the SQL statement to be executed

When I have had an issue with my query I have stripped the SQL down to the most simple statement first. Sometimes, I have even gone so far as to have it be Select * from <>. Then adding each piece back in until I get the entire statement in place. It helps in identifying which part of the code EAS is having problems with.