Undocumented New Filter Feature in .115

Recently I wrote a blog post about some of the new features in the .115 release of OAC-Essbase you can read about them HERE. In that post I mentioned there are options in creating dynamic filters  but it would have to wait until I could test them out and get some clarification on some of them. There are a few changes I had put in as enhancement requests and Oracle actually listened to me.

Well, I spent a little time with Oracle Development and played with them myself so I'm now ready to tell you about them.  I will say, this is probably not inclusive but does have some cool changes.

A little background

If you have not played with dynamic filters, they are a really great way to reduce the number of filters you have to write. they use a Connection and a datasource to do a lookup to see what access a person should have and apply it dynamically to the filter.  At one client using this we were able to reduce the filters from 100s to 4 filters.  If you want to step by step instructions On the OAC server under files->Gallary->Technical->Filters are a spreadsheet and file for testing it. The Efficient_Filters.xlsx is a dbx file to build a cube and has step by step instructions for testing the basic functionality out.

 

For my testing, I used Sample.Basic. In my testing I created a connection to a flat file, but it can also be a relational table. I created the following spreadsheet (Note you will see various versions of it in this post as I kept modifying it to try different things)

Once I had the spreadsheet,I saved it as a .csv file and I uploaded it into the Sample.Basic directory.

I then created a connection. Note, you can create the connection either as a global connection using the Sources card, or as an application specific connection by inspecting the application.  I did mine as a application level connection.

 

After creating the connection, I created the datasource from it. Notice the connection says SAMPLE.  that is important. It tells us this is a application specific connection. I used this information later when my filter was not working. 

 

The next couple of screen shots show the rest of the process. setting the column types and previewing the data. After all that I saved the datasource

 

 

Note:This version of the file had Q1 and Q2. In Sample Basic that should be Qtr1 and Qtr2.( Took me a while to figure that out )

Now the fun begins

Now that we have the datasouce I can build a filter.  I won't go into the details of using @Datasourcelookup with $LoginUser as they are documented in the sample.  So lets talk about hwat is new. 

Using prebuilt expressions

he first undocumented feature is the datasource can have prebuilt calc script expressions that will be passed to the filter row.  In my example, my column in my datasource is called EXPR

 

I created the filter to pull in the column for EXPR

 

and got back everything without the filter working. WHAT?  This is because you have to give users a minimum of read access to the database then if they are filtered it applies the filter. Remember, with access, the user gets the maximum access so the read overruled the filter. 

A slight change to the filter to set the initial access to none

 

and I get my expected results. 

What I did find is the Oracle examples all use Metaread which don't need the line to remove access. Certainly easier for testing, but less real world. 

Getting Group information

This is documented in the newest admin guide. In addition to $LoginUser, the new $LoginGroup will pick up all of the groups a user is associated with and us it for the lookup value. 

If we look at my  security file (corrected) we see in addition to the user TestUser, I have something called TestGroup.  This is a group that has TestUser into.   I think if I were going to redo this, I would have two columns USERNAME and GROUPNAME to make it a little less confusing.

 

The filter is now changed to use the new group lookup (with the metaread I really don't need the second line)

and I get back

Three things to note here

1. The filter used the Group name to get the access

2. The file had multiple rows for the same group and it merged the rows together

3. I got a result, I didn't expect. The filter merged the expressions into a single line so instead of getting Qtr2 for Colas and Qtr3 for Root Beer, I got Qtrs 2 and 3 for Colas and Root Beer.  This is because it is putting everything on a single line so in reality, I'm getting @IDescendants("100"_.@IDescendants("200"), @IDescendants("Qrt2"), @IDescendants("Qtr3") I've talked to Developement about an enhancement to have them as separate lines, we will see where that goes. 

I've apparently reached a size limitation in Blogger, so this will be continued in Part 2