For the last plus year, I have been wanting to enable Drill-Through reports using Essbase Studio (build Essbase ASO cube model(s) plus create reports). The main and only reason that I was interested in using Studio for the cube build and deploy was for the ability to provide Drill-Through reports using the SQL-type reports!
Why? If you are not familiar, SQL-type Drill-Through reports run the SQL and 'spawn' a new worksheet of the SQL results in that worksheet in the current workbook! Truly an amazing, brilliant and mind blowing feature!
If you are not familiar to what the drill-through report feature is, a brief explanation is this... Once you create your cube (model) in Essbase Studio, hierarchies, dimensions, Alias, etc. and finally cube deployment, you can also create Drill-Through reports.
In creating reports, there are 3-options (types) in the Essbase Studio client:
1 - Relational-type (SQL)
2 - URL-type
3 - Java Method-type
I am going to go over the URL-type and only mention that I have not been able to wrangle up any viable examples and/or documentation on how/why/where/who of the 3rd, Java-type is being used or is in use.
URL-type drill through reports. These are pretty much as you would think. When creating the report, you define the grid (POV) context for the report.
In the screen captures (below), I am passing 2 contexts;
for Time (Period & Year).
This is pretty straight forward.
In short, you are defining the intersections to be enabled by Smart View for drill-through. The intersections can be defined by either Levels or Generations in the cube model.
You can either click the 'Add' button or simply drag and drop the desired hierarchy from Metadata Navigator into the 'Intersections' window.
The "Advanced Settings..." is where you can, in addition, define the hierarchy Generation or Level for each dimension context.
In this case, both hierarchies are set at Level-0.
You then go to the next tab, 'Report Contents', select the URL-type radio button.
In this example, I am using the Sample URL Template (Google search). This is actually a good way to validate that the drill report contexts are getting passed properly to the report being executed.
You can see from the syntax, created for you, that the 2 contexts on the previous tab where placed directly into the URL drill path as needed plus in the proper syntax format just the same.
The context, as stated in the Essbase Studio guide is as follows:
$$Period-VALUE$$
$$Year-VALUE$$
By using this, the drill-through report will run against these members from your Smart View reporting grid (POV) and the specific intersection of data that is being called on for the drill-though report.
Now, the last and final step, is to associate the drill-through report to the desired cube (model).
Simply make sure that the checkbox is checked for the cube or cubes that make sense to enable the desired drill-through report.
Click 'Save'.
Now, that might still leave you without seeing your drill-through report in Smart View! Don't get frustrated. I will try and save you the time, as I was able to breakthrough and find this workaround that worked for me.
The workaround:
## Note you do NOT have to re-deploy the entire cube ##
Only for the hierachy(s) that you have used as a context.
You can use the Cube Deployment Wizard to do a partial cube build. While retaining all of the data already loaded.
From the Metadata Navigation window, Expand the Cube Schema and Right click on the Essbase Model.
You should now see a menu list.
Select "Cube Deployment Wizard"
Make the selections accordingly:
Server, Application, Database
Click Next
Build outline
Check the box "Incremental load"
Click Next
This is important assuming that you don't want to rebuild the entire cube and redeploy again.
Make sure to select the radio button:
"Update or rebuild selected hierarchies"
Choose the dimensions that are associated contexts in which the drill through report(s) were created.
Make sure if you want to retain any data already loaded in the cube that you 'Preserve: All Existing Data'. Update or Rebuild, click-one of the buttons; your choice.
Click 'Finish'
#######
Going back and a little bit of background on the setup.
- The Subject Area was created first (Pivotal Greenplum)
- The report/analysis was created in OBIEE to be able to launch the Drill-Through report as either 1-SQL or 2-URL based report but also passing the proper context(s) from Smart View
- The Essbase cube (model) was built & deployed, verified, validated, etc.
- Then create the drill-through reports in Essbase Studio were created
- Workaround: Redeploy hierarchy-dimensions as needed to 'enable' reports in context
