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).

MDX: Cousin Function

I recently found a need to reference an alternate hiearchay in an MDX member formula in an ASO cube.

My first attempt was to utilize the Lag function as I had in many other formuals, but found that I kept getting results of zero.  Using the MDX Script Editor in EAS, I found that the Lag function was only valid within the hiearchy that the specified member was found in.  After reviewing the list of available functions, I took a look at the COUSIN function.  I found it was exactly what I needed. 

Here is an example of how it might be utilized:

Let's assume fort his example that you have two hiearchies within your period dimension.  Let's also assume that data is loaded on a weekly basis.  The first hiearchy provides your weeks, months, quarter, and year.  Your alternate hiearchy is a dynamic calc of the quarter to date as of each week.  So your two hiearchies look like this:

For each member you could do a unique formula, specific to each week:

• WK1 = [Wk1]
• WK2 = Sum(MemberRange([WK1],[WK2])
• WK3 = Sum(MemberRange([WK1],[WK3])
• WK4 = Sum(MemberRange([WK1],[WK4])
• WK5 = Sum(MemberRange([WK1],[WK5])

Or instead you could use the the same formula for each WK QTD member of a quarter:

• WK1 thru WK13 = Sum(MemberRange([WK1],Cousin([Period].CurrentMember,[Year])))
• WK14 thru WK26 = Sum(MemberRange([WK14],Cousin([Period].CurrentMember,[Year])))
• WK27 thru WK39 = Sum(MemberRange([WK27],Cousin([Period].CurrentMember,[Year])))
• WK40 thru WK52 = Sum(MemberRange([WK40],Cousin([Period].CurrentMember,[Year])))

It could be taken the next step so that the same formual is use for each WK QTD member:

• Sum(MemberRange([Period].CurrentMember.Parent.Parent.FirstChild.FirstChild,Cousin([Period].CurrentMember,[Year])))

Please note there are probably several different ways that you can set your the first member of your member range to by dynamic.  This is probably a rather crude way, but since this post is about the COUSIN function, I went with the simplest route that would validate.

In summary, the COUSIN function allows the flexibility reference a child member at the same relative position as a member from another ancestor.  This can be used within the same member hiearchy or across another member hiearchy.

ASO Partial Data Clears Using MaxL and MDX

The Essbase 11.1 release brought us the ability to do partial data clears within an ASO database.  Gone are the days of using report scripts or the Excel Add-In to clear data.  There a couple of posts out there on other sites about how to use MaxL and MDX to do a partial data clear, but most of them speak to the simple clear script, performing a restriction using one dimension.  What happens when you need to define your data clear a little more precisely?

Let's start with the basics of data clears in an ASO Essbase cube.  There are two types of data clears you can perform, a logical delete and a physical delete.  A physical delete removes the input cells in the specified region, whereas a logical delete creates a new slice with an exact offset of the input cells you are trying clear.  When you retrieve the data both slices are netted together and you get zeros.  The technical reference notes that a physical delete completes in a time proportional to the size of input data while the logical completes in a time proportional with the size of data being cleared.  For this reason, a physical data clear is suggested when removing large slices of data.  It is also warned that a logical delete will increase the size of your database.  Both of these should be taken into consideration when doing a partial data clear.

Now on to the fun stuff…

The MaxL Script Editor can be used to perform the clear.  The syntax to be used is:

alter database . clear data in region '{MDX set expression}' [physical]

Your appname and database name should be placed in single quotes.  The MDX set expression is the definition of the data you want to clear.  Physical is an optional keyword that should be used when performing a physical clear.  The most difficult part of this syntax is the MDX set expression.

First and foremost, a set must have curly brackets around it.  Second, it has to be one of the prescribed MDX functions that return a set.  Remembering those two things, building the MDX set expressions becomes easier.

Using the ASOSamp application here are a few examples progressively showing you how you can restrict your script more and more precisely:

• If you wanted to remove all Qtr4 data (regardless of year or any other dimension) your command would look like this:

alter database 'ASOsamp'.'Sample' clear data in region '{[Qtr4]}' Physical

·         If you wanted to remove only Qtr4 for current year (regardless of any other dimension) your command would look like this:

alter database 'ASOsamp'.'Sample' clear data in region 'Crossjoin({[Qtr4]},{[Curr Year]})' Physical

·         If you wanted to remove Qtr3 and Qtr4 for current year (regardless of any other dimension) your command could look like any of these:

o   alter database 'ASOsamp'.'Sample' clear data in region 'Crossjoin({[Qtr3]:[Qtr4]},{[Curr Year]})' Physical

o   alter database 'ASOsamp'.'Sample' clear data in region 'Crossjoin({[2nd Half].Children},{[Curr Year]})' Physical

o   alter database 'ASOsamp'.'Sample' clear data in region 'Crossjoin({MemberRange([Qtr3],[Qtr4])},{[Curr Year]})' Physical

·         If you wanted to remove Qtr3 and Qtr4 units for current year (regardless of any other dimension) your command could look like this:

alter database 'ASOsamp'.'Sample' clear data in region 'Crossjoin(Crossjoin({[Qtr4]},{[Curr Year]}),{[Units]})' Physical 

·         If you wanted to remove Qtr3 and Qtr4 units for current year (regardless of any other dimension) your command could look like this:

alter database 'ASOsamp'.'Sample' clear data in region 'Crossjoin(Crossjoin(Crossjoin({[Qtr4]},{[Curr Year]}),{[Units]}),{Descendants([Online])})' Physical

Keeping in mind the basics of MDX scripts is the key to getting your clear script defined down to a more granular level.  It is also important to note that the more complex your MDX is the longer your clear script may take to run.  If the data set being cleared is going to be very small you may want to use a logical delete as the increase to your database size will not be as noticeable.  If you are going to delete a large chunk of data, the physical delete may make more sense as a new large slice may take up a lot of memory and impact performance.

Difference Cube for Data Validation

On occasion throughout my career with Essbase, I have heard of difference cubes.  I understood the concept behind them, but until recently didn't see how they could be useful.  I don't know that I am using a difference cube in the sense that others have spoken of them, but I did find a great way to test large amounts of data using Essbase and the concept is still largely the same.


Recently we decided to upgrade one of our cubes by adding some new dimensions.  To do this we needed to update our history to include these new fields via a set of SQL update queries to our source table.  We had the the correct data in a separate set of PeopleSoft source tables.  We needed to ensure that our table updates populated the correct data.  We are talking more than 5 million rows of data so testing in Excel is not exactly a viable solution.  So what I did instead was load my data from my PeopleSoft tables into a copy of my cube into a scenario called "Actual."  Next I took my SQL updated table and and loaded it into the cube into a scenario called "Test."  I then had a dynamic scenario called "Difference" which did a simple calculation of "Test" - "Actual."  Once each of the data sets was loaded, I was able to set up a high level retrieve sheet for each of the drilled out new dimensions for the "Difference" scenario.  Anywhere, where I saw something other than zero was something that needed to be researched, as it was a variance between my two data sets.  I was also able to further drill down on my other dimensions to determine exactly where the variance was occurring.  I was able to completely validate my data in hours rather than the days or weeks it may have taken me if I had been forced to use a set of queries and Excel.


This was just the immediate use I found for an Essbase difference cube.  Taking just a few minutes, I can think of various other situations where this type of cube might come in handy.  Just a couple of examples would be that it might also be useful in:

• Verifying a new calculation for a cube
• Comparing a back up of an initial cube load to a new cube load
• Validating a new cube to an existing similar cube

A difference cube isn't always going to be something that makes sense, but it is definitely a tool that should be included in your tool box that can be drawn upon should an ideal scenario arise.  I don't see a difference cube as a daily use, especially for a functional user.  But for someone who develops cubes on a regular basis or works with large amounts of source data, there is definitely a functionality of this type of cube that is appealing.

As a note, if it were so necessary, you can also keep a copy of your difference cube as documentation of your validation for audit or SOX requests.

ASO vs. BSO Calculations

There are a lot of reasons why Essbase owners should have the majority (or all) of their cubes as ASO now. ASO loads faster, calcs faster, have a smaller footprint, allow for far more dimensions, and a few other things that others have already written a lot about. Prior to the current version of Essbase there were some concerns and it wasn’t quite as user friendly, but the Fusion edition (11.x) has addressed many of those concerns. Now with incremental loads, partial data clears, and ability to write back to level 0 members there is a lot more flexibility.

The reason why I write this today though is because I found another great reason to have ASO cubes instead of BSO. Some have written that BSO cubes allow for more complex calculations, which may be true in some regards. I have found that MDX in ASO cubes provides greater flexibility and allows writing of complex calculations in far few lines of code in a member formula.

For example, we have one BSO cube remaining that we use for planning purposes. A recent report request required a formula be added to the cube. The formula in and of itself is not complex. What is complex is that there are multiple IF scenarios that have to be embedded to ensure that the right calculation takes place for the right period of time. While writing this formula I received the following error message:

I never knew I was limited to a formula size. It makes obvious sense; I had just never run up against the limit before. I ended up having to remove some of my IF scenarios to get the formula down to the appropriate size. It’s not a huge deal as I guess no one is likely looking at the period over period growth rate from 3 years ago, but it was frustrating none the less.

So this got me thinking, we have this same formula in other cubes, ASO cubes. I was curious how they compared because I know it wasn’t as painful to write the ASO version as what I went through writing the BSO version. This is what I found:

• A fully written BSO formula was 1242 lines and 75,493 bytes
• A fully written ASO formula was 78 lines and 4,445 bytes

(I won’t bother showing you the 2 formulas as who wants to look at that many lines of code if they don’t have to)

And I think it is interesting to point out the BSO formula was written to calculate at the Month, Quarter, and Year level only. The ASO formula is written to calculate the Week, Quarter, Month, and Year levels. So technically, the ASO formula is doing even more than the BSO formula.

The difference is that in MDX there are some wonderful functions that allow you to select your data more succinctly and apply the desired calculation. I will be the first to admit, MDX isn’t always the easiest to read and sometimes building a complex calculation using multiple MDX functions embedded with each other can be frustrating to put together, but in the end I find that almost always my formula is shorter and therefore easier to maintain.

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.

MDX - Layering Formulas

As most Essbase users know, ASO has become the future of Essbase. There are some great advantages of ASO over BSO, but it also means having to learn MDX to create member formulas. MDX can be daunting at first, but with practice and persistence, formulas can be created to reflect almost anything that needs to be calculated. You can create a more complex formula by layering formulas together, but to do this you need to know what formulas are available. Fortunately, Oracle does a pretty good job of listing the available formulas and their syntax in their Technical Reference Guide. The 11.1.1 version can be found at: http://download.oracle.com/docs/cd/E12825_01/epm.111/esb_techref/frameset.htm?mdx_grammar_rules.htm.

Below is an example of a fairly complex formula that was created using MDX.

For this example, the Sample Basic database has been converted to an ASO database. Dimension changes include the addition of weeks for each month, using a 4-4-5 calendar, Work Days was added as a member within the Measures dimension, and Fiscal Year was added as a dimension. This is what the new outline looks like:

Let’s say for budgeting or trending purposes you need to need to look at the 4 week average of Sales, but Sales is impacted by the number of work days in the week. You want to exclude any weeks that were not full 5 day work weeks for your calculation. Add a member to the Measures dimension called 4 Wk Avg Sales with the following formula to get the correct calculation.

Case
  When IsEmpty(([Year].CurrentMember,[Measures].[Profit],[Product].[Product],[Market].[Market]))
    Then 0
  Else
  (
  Case
    When (IS([Year].CurrentMember, WK1) OR
               IS([Year].CurrentMember, WK2) OR
               IS([Year].CurrentMember, WK3) OR
               IS([Year].CurrentMember, WK4) OR
               IS([Year].CurrentMember, WK5) )
      Then Avg (Tail(Filter(Crossjoin (Union (Crossjoin (MemberRange([Year].[WK48],
                     [Year].[WK52]),
                     {[Fiscal Year].CurrentMember.Lag(-1)}), Crossjoin (MemberRange([Year].Wk1,
                       [Year].CurrentMember),{[Fiscal Year].CurrentMember})),{[Measures].[Sales]}),
                      ([Market].[No Market],[Product].[No Product],[Work Days]) = 5),4))
    Else Avg (Tail(Filter(Crossjoin (MemberRange([Year].Wk1,[Year].CurrentMember),
                {[Measures].[Sales]}), ([Market].[No Market],[Product].[No Product],[Work Days]) = 5),4))
    End
    )
End

So what does this formula do exactly?  Let's break it down into pieces.

The first part of the formula is a test to see if there is Profit at the very highest levels. If Profit is zero, then it will bring back zero as the 4 Wk Avg. This will prevent the formula for calculating for future weeks or prevent it from not calculating at a lower level, when there is no Profit at that lower level.

    Case
      When IsEmpty(([Year].CurrentMember,[Measures].[Profit],[Product].[Product],
         [Market].[Market]))
        Then 0

The next part is the calculation that will be performed whenever there is Profit at the highest level. The first part of this piece looks at the first 5 weeks of the year and the second part looks at the remaining weeks in the year.

Although it is a little backwards let’s look at the second part of the Else statement first. So for all weeks that not WK1 through WK5 we have the following formula.

    Else Avg (Tail(Filter(Crossjoin(MemberRange([Year].Wk1,[Year].CurrentMember),
                  {[Measures].[Sales]}),
                  ([Market].[No Market],[Product].[No Product],[Work Days]) = 5),4))

This statement is filtering WK1 through the current week Sales based upon where there are 5 Work Days and averaging the last 4 weeks.

The Crossjoin gives us a set of all the weeks Sales.

   Crossjoin(MemberRange([Year].Wk1,[Year].CurrentMember), {[Measures].[Sales]}

The Filter gives us a set where there Work Days is set to 5 for the week.

    Filter(Crossjoin(MemberRange([Year].Wk1,[Year].CurrentMember),
          {[Measures].[Sales]}),
          ([Market].[No Market],[Product].[No Product],[Work Days]) = 5),

The Tail gives us the last 4 members of the set where there Work Days is set to 5 for the week.

    Tail(Filter(Crossjoin(MemberRange([Year].Wk1,[Year].CurrentMember),
          {[Measures].[Sales]}),
          ([Market].[No Market],[Product].[No Product],[Work Days]) = 5),4)

The logic is similar for the first 5 weeks of the year, except we have to look at both current year and prior year to determine our 4 weeks.

    Case
      When (IS([Year].CurrentMember, WK1) OR
                 IS([Year].CurrentMember, WK2) OR
                 IS([Year].CurrentMember, WK3) OR
                 IS([Year].CurrentMember, WK4) OR
                 IS([Year].CurrentMember, WK5) ) 
        Then Avg (Tail(Filter(Crossjoin (Union (Crossjoin (MemberRange([Year].[WK48],
                  [Year].[WK52]), {[Fiscal Year].CurrentMember.Lag(-1)}),
                  Crossjoin (MemberRange([Year].Wk1,[Year].CurrentMember),
                  {[Fiscal Year].CurrentMember})), {[Measures].[Sales]}),
                  ([Market].[No Market],[Product].[No Product],[Work Days]) = 5),4))

This time we first want to Crossjoin the weeks to the Fiscal Year for both current year and prior year and since we want to look at both, we are going to use Union to join these two sets together.

    Union(Crossjoin (MemberRange([Year].[WK48],[Year].[WK52]),
             {[Fiscal Year].CurrentMember.Lag(-1)}),
             Crossjoin (MemberRange([Year].Wk1,[Year].CurrentMember),{[Fiscal Year].CurrentMember}))

Now we can Crossjoin the Unioned sets to Sales.

    Crossjoin (Union (Crossjoin (MemberRange([Year].[WK48],[Year].[WK52]),
             {[Fiscal Year].CurrentMember.Lag(-1)}),
             Crossjoin (MemberRange([Year].Wk1,[Year].CurrentMember),
             {[Fiscal Year].CurrentMember})),
             {[Measures].[Sales]})

From here we apply the same Filter, Tail, and Average logic to finish the statement.

Here is sample output showing the formula results:

Note:  A similar formula can be used in the time dimension Year, but it would only be for the current week, with no historical values stored.  The formula would also have to be modified for the current year to be from WK1 to a substation variable that is set up to be the current week.