Direct Database Request -Part 2

In Part 1 of my story on OBIEE’s Direct Database Request (DDR), I walked through the basic process of how to use DDR for directly querying a database mapped ino the RPD without using logical SQL, bypassing the metadata layer and going straight to the connection pool. In Part 2, I’ll be pointing out what works differently in the Analysis editor (Answers) as a result of using DDR, what’s going on behind the scenes, a few tips for configuring and using DDR, and what the target use case should be for using it.

To talk about how Answers is different when using DDR, we need a slightly more sophisticated query than what I used in Part 1:

Now that we have multiple attributes and multiple measures, we can start to test most of the features in an Analysis and see what we find. Let’s start with views:

I was impressed that two fairly new features: Performance Tiles and Trellis Charts, both work with DDR. While I can’t say I pushed the limits on a Trellis Chart, both the Simple and Advanced versions worked as shown above. I didn’t expect a problem with Pivot Tables and most of the Pivot Table features worked, except a minor thing I’ll point out later. As a bit of a tease, you can see the full list of views we’ll be previewing in the Views list above. Moving onto our second Compound Layout:

 

Again, good to see that a newer feature, the Waterfall chart, works. While again, I didn’t push the boundaries here either, it’s good to see a more sophisticated chart work within DDR. I didn’t have any concerns about the other chart types working, but I was skeptical that a feature like the section slider would work properly and it does, demonstrating the ability for DDR to be interactive (assuming you like the slider). Moving on:

 

I didn’t have any concerns here because if DDR could handle the Trellis and Waterfall, Line/Bar Combos and 100% Stacked Bars should be easy peezy lemon squeezy (yes, I just used that phrase in a blog post — one more thing to check-off the list). Moving on:

 

As you can see, the Ticker and Narrative both work (and everyone lets out a collective sigh of relief), while the View Selector has no problems assembling views, just like normal. I didn’t expect any problems with the latter, but you never know. So while I didn’t show a screenshot of every possible view that does work (looking at you, Scatter, Bubble, and Funnel), here is the list of views that don’t work, or do work but don’t really add any value for reasons stated:

• Map: If you know anything about setting up this view, you’ll know there is Subject Area configuration to be done. Without a Subject Area in the Analysis, there is no way for this view type to work. Interestingly enough, you can put the Map View on a compound layout and it will show a Map with nothing on it. So if you’re interested in teaching your users about geography, feel free to throw it on a Compound Layout so they can learn where Qatar is (hint: Persian Gulf).
• Gauge: Sir, the possibility of successfully creating a Gauge view is approximately 3,720 to 1. I’m sure some odd combination of settings will create a successful Gauge, but I couldn’t find one and inexplicable errors abounded.
• Filter: Nothing to see here. In fact, it doesn’t even appear in the list of views. So if you were hoping that Answers would magically transform your WHERE clause into a Filter view, keep dreaming.
• Column Selector: Nothing to see here, for a slightly different reason. Much like the Map view, the Column Selector really requires you to select from a Subject Area to create it, though like the Filter view, one could hope that Answers would enable this functionality using the SELECT statement, but we’ll keep dreaming here, too.
• Selection Steps: If you like telling your users blatantly obvious things, then put this view on your Compound Layout and it will tell them there are no Selections in the Analysis, because well, you can’t put Selections on a DDR Analysis. I think it’s an oversight on Oracle’s part that this view isn’t removed from the list like Filter and Column Selector.

Dear Kevin, is that the full list of things that work differently in an Analysis using DDR? Signed, Curious from Omaha.

Well, Curious from Omaha, sadly it’s not. In fact, there is a pretty significant list of things that work differently. The good news is, most of these things are pretty minor in the grand scheme of things, especially when you think about why a user might want to use DDR in the first place (more on that later). But since you asked, here is the-99.9-percent-complete-list-of-things-that-work-differently-using-DDR (with less commentary this time):

• Conditional formatting on columns (it’s greyed out)
• Selection Steps (again, greyed out)
• Writeback (the tab isn’t greyed out, curiously, but you can’t check it)
• Interactions: no drilling, conditions, or actions passing context
• Presentation variables — you need to use Boris Dahav’s tips here
• Prompts: Dashboard or Analysis
• Sorting on table columns (curiously, Pivot Table sorting works)
• Create Group (greyed out)
• Create Calculated Items (not greyed out; errors abound)
• Add/Remove values
• Display/Hide Running Sum (though running sum does work on Pivot Tables)
• Pivot Table: changing aggregation rule
• Add to Briefing Book

But let’s not be all negative and stuff. There are a lot of things that do work, starting with this:

 

So to quote Coldplay, “Everything’s not lost.” Here are a list of things that do work quite well:

• Master Detail
• Navigations (just can’t pass context)
• Most formatting
• Moving columns around both as a developer and user
• Add Grand/Sub Totals both as a developer and user
• Hide/Exclude/Include columns
• Conditional Formatting on Charts(!?)
• Most Report Links
• Saving to the catalog

So while there are a few things that don’t work, you can help developers and users with a few tips:

Tip #1: If you are going to publish a DDR Analysis for end users to consume either through a folder or a dashboard, turn certain Interactions features off using the Analysis Properties as shown above. The ones checked above dowork and the ones unchecked don’t, so for something like sorting, users won’t be frantically clicking on the column headers trying to re-sort.

Tip #2: If you have DDR users who will be only DDR users and have little reason to develop an Analysis from a Subject Area, turn off certain views like Map and Gauge in the Administration > Manage Privileges area of the front-end. This will help hide things they won’t need and reduce calls to the help desk.

Speaking of Manage Privileges, there are a few setup steps required to enable DDR for your users, and because of those steps, you will likely want to create at least one Application Role for your DDR users, as shown below:

You may have sophisticated needs that require more than one role, but here we will assume all DDR users are alike and we have no reason to differentiate between them. When creating this role, it’s a good idea to copy the BIAuthor role, but you may want to make sure other BIAuthor members don’t have the DDR role if you need to distinguish between regular BIAuthors and those who have DDR.

The next step to enable this feature for your DDR users is in the aforementioned Administration > Manage Privileges area of the front-end. Here you need to enable three privileges, as shown below:

Now, it’s unclear why Oracle split this into three privileges, because without the two in the Answers section, the one in the Admin:General section doesn’t give you access to choose DDR from the Subject Area listing. The two in the Answers section do serve a purpose, though, as Execute Direct Database Analysis will let you run a DDR Analysis (and thus you may need to give it to your BIConsumer role) where Edit Direct Database Analysiswill let you build one from scratch. Curiously enough, the latter without the former allows you to start building your DDR request, but as soon as you go to the Results tab, your report will just spin and spin and never come back.

At this point your setup work is technically done, however it’s time for a few more tips:

Add a separate Connection Pool for DDR users.

Tip #3: Create a second, or additional, Connection Pool for DDR users, give it a good name, and list it after the main Connection Pool that most normal OBIEE requests process through. Honestly, this should be a no-brainer, since DDR users need to know the name of a Connection Pool to use in a DDR request, you don’t want to give them the main Connection Pool name. In addition, you may have different database logon requirements for your DDR users, be it a different shared logon or some form of individual database security. Putting this Connection Pool second (or third or last) in the list insures that OBIEE doesn’t use it regularly and giving it a friendly name means users won’t have to remember cryptic IT code like NAUSPRODDWOBI1. And if you’re concerned that a user-friendly name will be too easy to guess, fear not, as we have another tip:

Secure the DDR Connection Pool to your DDR Application Role.

Tip #4: Secure the Connection Pool for your DDR users. Now, your security requirements may be different from the ones shown in the screenshot to the left, but the general idea is that you should secure the connection pool in such a way that only those who need it can actually use it. This is done using the Permissions button in the Connection Pool properties, which you can access by simply double-clicking the Connection Pool in the Admin tool.

I feel like I’m on a roll with tips, so here are a few more:

• Tip #5: As a developer, finalize your SQL statement before editing Column Properties, column aggregation rules, and adding views to your compound layout. If users of DDR don’t have access to a traditional SQL tool like SQL Developer, which is entirely possible since you want them using OBIEE and not a SQL tool, then the typical development process will probably include switching back and forth between the SQL statement on the Criteria tab and the Table view on the Results tab while you fine-tune your SQL. After every change to the SQL statement, you must use the Validate SQL and Retrieve Columns button, which will clear any previous formatting applied to the result columns. THIS CAN BE VERY ANNOYING IF YOU ALREADY APPLIED A TON OF FORMATTING! (Another thing to check-off: use a complete ALL CAPS sentence in a blog post because it shows how serious I am).
• Tip #6: Use Usage Tracking to monitor queries submitted to the database through DDR. Yes, ladies and gentlemen, Usage Tracking does capture DDR requests and if you’re on the right OBIEE version (the exact patch is escaping me at the moment), you can even see the physical SQL!

That last tip is important, so let me show you a few quick things about how DDR works. First, your query in a DDR request is still being processed by the BI Server. It is an actual request, as shown below:

Above you can see that the BI Server is using an EXECUTE PHYSICAL function to process the DDR query, and it even gets a logical request hash. So for all intents and purposes, a DDR request isn’t all that different from a regular OBI request. As such, the query is tracked in Usage Tracking, as shown below:

You can see that while the Subject Area Name field is null, both the Logical SQL and Physical SQL columns are populated, and even the Physical Sources columns shows us what tables were used in the query. Sadly, the table aliases are included, requiring some string processing to do some reporting on just the base table names, but the data is there. Why is this information useful? Well, do you recall in Part 1 how I said that many people are concerned about using the DDR feature because it doesn’t stop users from writing bad queries? Well, Usage Tracking is a weapon for you to help them write better queries because you can see exactly who is writing what and when. Whether you use this information to help them write better physical SQL or use it as a means to drive future metadata development work, the bottom line is that as an organization giving out this feature, you’re not left in the dark about what people are doing with it.

So what will people do with it? Where is this feature most useful? This part gets a bit subjective, but I believe in this day-and-age, DDR should be more widely used. We’ve learned from the mistakes of thinking that everythingneeds to be in a Data Warehouse, that everything needs to be in a metadata layer, and that we have the ability to deliver everything in a timely fashion when the users need it. With that in mind, DDR is a great way to bridge the gap. More specifically, you can give savvy users access to an ODS that might be too complex for you to map into the RPD (or you don’t know where to start), you can use it to replace dying SQL-driven tools like Hyperion Interactive Reporting or Oracle Discoverer, and you can use it against a new data source when you don’t know the full requirements of what users will need. While you wouldn’t buy OBIEE just for this purpose, companies already owning OBIEE have a great way to expand its usage. As long as your users are pretty savvy writing SQL, and perhaps they already know how to use the Analysis editor against a Subject Area, then you’ve got a good place to start.

So there you have it. Everything* you ever wanted to know about the OBIEE DDR feature, but were afraid to ask. While it’s a feature that should be approached with caution, it’s not a feature we should be ignoring at all costs.

*Kevin McGinley’s Medium Collection makes no guarantees that he’s actually covered everything because Christian Berg will probably point out something that Kevin missed.