Data and metadata filters are used with Essbase to limit access to data in a cube. When you use data filters to restrict access to a certain set of dimension members in the cube, the end-user can see that the member exists, but can’t see any data associated with it. Metadata filters go one step further, stopping the end-user even seeing the dimension member if they’ve not got permission to view it. In the past, Essbase filters were defined using MaxL or Essbase Administration Services, and then assigned to individual users or groups using Hyperion Shared Services; however, if you install Essbase as part of OBIEE 11.1.1.7, these filters are assigned to users and groups using Fusion Middleware Security’s application roles and policies. So how does this process work?

There are three main steps required to set up an Essbase filter under this new Fusion Middleware security model:

1. Identify the user or group to which the filter is to be applied (or, as in the case of this note, create a new FMW user and add to an existing default group)
2. Create an Essbase Filter, typically using Essbase Administration Services
3. Set up the required policies within Fusion Middleware security, using either WLST scripting or more usually, Oracle Enterprise Manager Fusion Middleware Control. 

Creating a new Embedded LDAP Server User

To start by creating a new user, login to Weblogic Administration Console (eg http://localhost:7001/console/login/LoginForm.jsp) , and click on Domain Structure > Security Realms, like this:

On the subsequent screen, click on the Users and Groups tab.  Then, click on New to add a user.  On the resultant form, enter a user name (eg markcann) and a password.  Confirm the password, and then click OK to save those details.

 Back in the main users screen, click on the newly-added user, like this, so that we can then assign it to a group.

Then, in the resultant screen, click on the Groups tab, then select just the BIConsumers group, and press Save. This user is now part of the basic group that can access OBIEE, and will be able to run reports and other catalog content.

Create the Essbase FIlter

Now we’re going to create an Essbase filter to apply to this user. In this example, the Essbase test database EXA102_2.EXA102_2 contains the following dimensions:

The filter to be created needs to hide the bottom levels of the Product Hierarchy from view: For example PT1 within PC12 contains PI3, PI2, etc as lowest level values.  All such leaf nodes need to be hidden from user view for this dimension.

Although filters can be written / uploaded using MAXL, even though we are using Fusion MIddleware security with Essbase, they can still be created in Essbase Administration Services (EAS).   Once logged into EAS, navigate through the Enterprise View tree to locate the required database, then right-click on the database, and choose Edit > Filters.

In the Filter Editor screen, click New to create a new filter.  Assign the new filter a name (for example, “DropLeaves”), and add a MetaRead filter, the most restrictive of the two filter types.

@REMOVE(@IDESCENDANTS(“Product”),@LEVMBRS(“Product”,0))

 Click Verify to check the content of the filter code, then click Save to return to the main filter screen: 

Then finally, click Close.  The filter is now created and ready for assignment. 

Assigning the Filter using Application Policy Permission Objects

According to the OBIEE 11.1.1.7 Systems Administrator Guide’s section on Configuring Data-Level Security using Essbase Filters:

“An application role requires at least two policy store permission grants to access to a specific filter. You must give an application role permission to use filters within a specific scope eg

• oracle.essbase.application, /EssbaseCluster-1/Demo, use_filter

and

• oracle.essbase.filter, /EssbaseCluster-1/Demo/Basic/read_filter, apply”

This is true – an application role does indeed require at least two policy store permission grants to access to a specific filter. It requires a whole lot more to work if starting from scratch though.

It is important to recognise that the default groups set up by the install contain a lot of policies by default and they are nested and hence inherit policies from their ‘subordinate’ members. The three groups allocated and set up for the BI elements of FMW are set up thus:

• BIAdministrators
• BIAuthors
• BIConsumers

By adding our new user, markcann, to the BIConsumers group, it automatically assumes all of the rights of that group.  We can however overlay additional policies just to the markcann user that will not affect the other members of the group.  It is here that the two additional policies to effect the filter – ie to this user – need to be added.   

To do this, login now to Enterprise Manager Fusion Middleware Control, and in the left hand pane navigate to Business Intelligence > Core Application and left-click on it.  Once the right-hand pane has refreshed, click on the dropdown next to Business Intelligence, then select Security / Application Policies, like this:

In the resultant screen, select obi as the Application Stripe (if not already defaulted to this value), select User as the Principal Type, and then press the Create button. 

 In the resultant screen, click the top Add:

Change the Type field value to User, then locate the new user (click on the blue arrow on the right hand side of the form after having entered the first letter in the search box if required).  Highlight the user, then click OK.

Back in the main screen, the selected user will now be showing.  Click the bottom Add to start adding the required policy permission.

 In the resultant dialog, click the Resource Types radio button.  Choose oracle.essbase.application as the Resource Type, then click Continue.

Type in the cluster / app name (/EssbaseCluster-1/EXA102_2) as the resource.  Check only the use_filter permission action, then press the Select button.

Back in the main screen, the newly added application policy permission should now be showing. Click the bottom Add button again to add a second policy permission, following these steps:

1. Check the ‘Resource Types’ radio button
2. Choose oracle.essbase.filter as the Resource Type
3. Click ‘Continue’ to access the second screen

Enter the Cluster / App / DB / Filter (eg /EssbaseCluster-1/EXA102_2/EXA102_2/DropLeaves) as the Resource Name. Check the apply check box, then press Select to return to the main application policy screen for the user.

 Back in the main screen, with the two application policy permissions added now showing, click on OK. 

 The previously created filter is now effective for the markcann user. 

Testing the New Filter

To test the filter, I now log into Smartview, initially as the main administrator user ‘weblogic’. To test access without the filter applied, I access the relevant database (in this case, EXA102_2.EXA102_2), place the Product dimension in the ‘row’ position, and perform a Zoom In / All Levels operation.  Note that level 0 values of product ARE visible:

Now I re-connect to the database via Smartview using the new markcann user and repeat the orientation / Zoom actions.  Note that the level 0 values are no longer visible:

When actually drilling (zooming) from Smartview, double-clicking on (e.g.) Bread Clubs results in no change to the report, exactly as if it was the bottom level of the hierarchy.

Obviously in a real system you would not look to set up access like this on a user by user basis.  One approach may be to add access-based groups (UserGroup1, UserGroup2, etc) to the BIConsumers (or indeed, BIAuthors) group, assign the relevant filter (or filters….Essbase under FMW allows recognises multiple filters for a user, which is new) to those groups.  Provisioning (access to functions) would then be controlled at ‘Standard Group level, and Scoping (preventing access to data) controlled by the Custom Groups.

So – a bit of a different process than we’re used to with Shared Services, but it has the advantage of using the same users, groups, application roles and application policies as the rest of OBIEE. Remember also to ensure that any connection from OBIEE’s repository to the Essbase database uses the actual users’ credentials (i.e. :USER and :PASSWORD, or use the CSS Token feature) otherwise the filters won’t get applied to the users’ analyses and reports.

Last week I wrote a blog post on the topic of storing lots of detail-level data in Oracle Exalytics, not the primary use-case for the product but something we’re increasingly asked about. Most implementations of Exalytics use OBIEE’s Summary Advisor to recommend aggregations against large, data warehouse data sets with those aggregates then getting stored in TimesTen, but some customers are looking at their data set, seeing that its well-under 1 or 2TB in size, and wondering whether it’s possible to put the whole dataset in-memory, rather than just specific aggregations.

My initial conclusion on writing the article was that TimesTen, although fast for small datasets, wasn’t particularly suited to querying large, sparse datasets because it lacked common VLDB features such as parallel query and partitioning. Instead, I recommended readers looked at the other database engine that comes with Exalytics – Essbase, and in particular the Aggregate Storage Option, as an alternative way of storing this volume of data.

And Essbase ASO performed very well at the task, rapidly loading 130m source rows of data into an ASO database, and then responding to queries typically within a couple of seconds. The source Oracle database took up to a minute to return queries (having disabled any materialised views in the background), and then TimesTen database performed as expected, typically taking 10, 20 seconds to return answers. Except – having discussed the results with the TimesTen development team over the weekend, I’d missed out a crucial step, by not running TimesTen’s Index Advisor after some queries had run, to create indexes that suited the dashboards I was analysing. In yesterday’s post then I ran the Index Advisor on the two TimesTen databases, and as Oracle advised, the results were much, much better – in fact, faster than the Essbase ASO database that I’d been singing the praises of in the previous post.

But of course – now things aren’t being fair on Essbase, as there’s a similar step that you can perform on Essbase ASO databases, to run an advisor that recommends what are called “aggregate views”, similar to Oracle database materialised views, based on the structure of your cube and the workload on your system. And, of course, there’s the Summary Advisor that I can run on the original Oracle source database, which of course should make certain queries against this source run faster. So which one is best?

Before we get into any more testing though, it’s worth thinking about why we’re looking at this scenario, and what Exalytics is designed for – at least., the OBIEE parts of Exalytics. When Exalytics was put together, Oracle’s vision for the product was as a complement to technologies such as Exadata and Big Data Appliance, where the reporting dataset could typically be many, many terabytes in size. As such, you’re never going to get all of the data into the 1TB (now 2TB) of RAM that’s in the Exalytics server, so you’ve got two choices; either you take a detail-level subset of the entire dataset and put it into RAM (the SAP HANA approach, perhaps) or you take a slice of it – say, just the aggregates – and put those into memory instead. Going down the latter route means that Exalytics can work with source datasets many times the size of Exalytics’ memory, and the approach fits with the general way that data warehouse systems are optimised, by pre-aggregating and pre-caching data.

But some customers don’t fit this model, but still want to take advantage of the power of Exalytics. Some might buy Exalytics to give them their own, departmental analytics server, in the same way that companies used to buy Hyperion Essbase or Oracle Express to be able to “own” their own analysis server. In this case, it’s reasonable that they might want to put all the data required for analysis “in the Exalytics server”, but this isn’t a scenario catered for well by the Summary Advisor. Others might just have a reporting dataset that’s within the 1 or 2TB of RAM within the Exalytics server, and just say – let’s put it all in there. Another scenario might involve copying just this month’s data into TimesTen (“hot data”), and then use a feature such as OBIEE’s LTS fragmentation to get the hot data from TimesTen, and the older data from Oracle. Whichever way, Exalytics has to be able to handle both summary-level queries, and retrievals of individual sets of rows, right down to the grain of the dataset.

So the way that TimesTen, with the Index Advisor, speeds-up queries against large datasets actually serves this well – index access will suit retrieval of small sets of rows, and in fact the new indexes recommended by the Index Advisor in the example in yesterday’s post even seemed to speed up aggregations as well, which was a bonus. The equivalent advisor within Essbase concentrates on aggregations though, not single “row” retrievals, so how does that work?

Whilst you can create ASO aggregate views manually, probably the easiest place to create them is within Essbase Administration Services Console, where there’s a menu option against ASO databases for designing these aggregate views (remember, ASO databases automatically aggregate when queries run against non level-0 members, but this process is about pre-calculating more aggregates in order to speed up those queries).

Selecting this menu option brings up a wizard, that lets you either have the wizard pick and materialize the aggregations, or let you select and materialise them individually, based either on the structure of the ASO database or a query workload captured earlier.

Creating the aggregates, for both the Sales History and the AIrline Delays dataset, was surprisingly fast, and going back to the dashboards and running the queries again, response time as predicted, dropped.

So in-fact, from a purely response-time perspective, there’s not really much in it between TimesTen (plus the Index Advisor) and Essbase ASO (and the Aggregation Design Wizard). For good measure I also created some in-memory aggregate for the Oracle source using the Summary Advisor, which recommended a couple for each, albeit with the TimesTen one coming in at a whopping 10GB.

So – to answer the question – can I put my entire dataset into Exalytics’s RAM, either because in total it’s less than 1TB in size, or because I want all my reporting data on my own server, in-memory, which option comes out best? In truth, there’s no straight answer, with both TimesTen and Essbase ASO capable of doing the job, but each with their own limitations.

During the Essbase testing I’d been corresponding with Dan Pressman, author of the “How ASO Works and How to Design for Performance” chapter in the “Developing Essbase Applications” book, who was very bullish about Essbase Aggregate Storage Option in this type of scenario. In his opinion (and to paraphrase), Essbase ASO creates such compact databases (250MB vs. TimesTen’s 10GB) and returns both detail-level and summary data so quickly because:

• “It effectively “compresses” the metadata when it creates the bitmap (which could be described as containing an efficient surrogate key for each piece of metadata)
• The bitmap not only identifies each piece of data at the lowest level but includes the full ancestry within the hierarchy.
• The full alternate hierarchy ancestry (for all alternates) of each data item is additionally represented in alternate bitmaps, which while not physically in the data file is available to the query evaluator
  
• This compressed metadata is very much like a column store index. Since the full identity all of the way up the hierarchy (and on alternate hierarchies) is included all possible “additive” queries can be answered from this one compact representation.
• ANY aggregate can be generated dynamically by querying the compressed bitmap. And if it fits in RAM then ANY aggregate can be calculated at RAM speeds. There is no need to use a summary advisor and hope that you got the right Aggregates.
  
• Even though there is no need to use a summary advisor, one exists: the aggregation wizard. It creates summary aggregations which have all the advantages of the original metadata compression – simply limited to the upper portions of the hierarchies not summarised in the aggregation process.
• This means that an aggregation can serve many queries.”

In addition, Dan observed (correctly) that the queries I ran from the various dashboards were pretty simple, and in his words “if instead you ran a more complicated grid type query that required results from all query categories, I believe you would find Essbase would win on the UNAGGREGATED cube – as TT would need results that would have to come from a number of indexed queries that would then have to be correlated.  Essbase would be able to do all the levels in a single pass of the full 900k bitmapped rows.”

But one thing that’s worth also bearing in mind is that it was a lot harder to create the Essbase ASO dataset, even using Essbase Studio, than it was to replicate the tables into TimesTen using ttimportfromOracle. Its certainly possible to model a multiple-fact scenario in Essbase, but in practice reporting across multiple Essbase databases and modelling multiple “facts” is trickier in Essbase (within the OBIEE context), and I had to do lots of data-munging such as prefixing member names to ensure uniqueness, that I didn’t have to do with TimesTen. That said, the out-of-the-box performance with the Essbase ASO source was a lot better than the TimesTen one, and the RPD modelling part was easier as the import process creates all the RPD metadata out of the Essbase database outline.

And all of this assumes that you can get your full dataset into Exalytics’ 1 or 2TB of RAM. With the Essbase ASO database, even with the additional aggregate views, the dataset was tiny, whereas with TimesTen the tables I imported in came to around 10GB (with compression), fine for an Exalytics server but a slight warning that we’d have to carefully manage space when loading data into the server. And this is where the regular Summary Advisor comes into its own, taking just the aggregated data and storing that, leaving the detail in the source database with Exalytics just holding the aggregates.

Unfortunately, therefore, there’s no black-and-white answer as to which technology is best when you want to store all of your reporting data, not just the aggregates, in Exalytics. The good news is that they both work (in the end), but each approach has its benefits – Essbase is easier to setup and model, gives the best initial results, but requires Essbase modelling skills and may not scale-up to the point where you could reasonably model the entire BI Apps dataset in Essbase ASO, for example. TimesTen is a bit more fiddly, takes a bit of post-load tuning, but has the benefit of matching closely the structure and layout of the source Oracle database, making it perhaps a more realistic option when replicating a whole schema into RAM – as long as it all fits in.

For me though – in the short term, I’ll be presenting this along with Stewart Bryson at next week’s Enkitec Extreme Exadata Expo (E4) Conference in Dallas, Texas, as part of a wider “tips and tricks from the field” session on Monday afternoon. Beyond that, my next set of tests will be to revisit the work that Pete Scott and I did on accelerating Oracle BI Apps dataset using Exalytics and TimesTen, this time with the Index Advisor and with the hope that we’ll finally hit that goal of a data warehouse in-memory, with split-second response times. Watch this space, and maybe see one or two of you in Texas next week.