Drillbridge: Enabling Upper Level Drill-through

In our last post about Drillbridge, we did the basic introduction and then ran through the steps to get it up and running. But, in the interest of simplicity, we kept it simple. Now we are ready to get a little more complex.

For today, a little more complex will be upper level drill-through.  This is one of those things that can make Essbase admin’s and DBA’s run from a building like it’s on fire.  Why?  Because upper level drill-through relies upon the end-users ability to not do something stupid.  As we all know…it’s a virtual certainty that they will.  This is where Drillbridge really shines.  We have several mechanisms to control the level of stupidity that we allow our users to engage in before we shut them down.

So let’s get started.  Log into your Drillbridge server and start by changing up the Deployment Spec.  If you remember from our first post, we started with this:

@RELATIVE("Year", 0),@RELATIVE("Measures", 0),@RELATIVE("Product", 0),@RELATIVE("Market", 0),"Actual","Budget"

We also checked the box indicating that this was Level-0 only.  Now let’s change that specification to this:

@IDESC("Year"),@RELATIVE("Measures",0),@IDESC("Product"),@IDESC("Market"),"Actual","Budget"

We have switched our Year dimension to use the IDESC function.  We have left the Measures dimension at level-0, since many times we can’t tie numbers out at upper level due to upper-level aggregation operators.  We have also switched Product and Market to IDESC as well.  We’ve left Actual and Budget alone since there is no upper level in the Scenario dimension.  We’ll also un-check the Level-0 members only option.  At the end, it should look something like this:

DrillbridgeUpper01This is our first method of preventing stupid user tricks.  We only allow drill-through that makes sense.  Now that we have our deployment spec configured, we can move on to the report.

Before we get too deep into the report, let’s first talk about how upper-level drill-through really works.  If a user chooses to drill-through on Qtr1 for instance, what does that mean?  Essbase is simply going to pass that value through, Qtr1.  But Drillbridge takes that member and uses the Essbase JAPI to determine the level-0 descendants of the member.  In our simple case, this is just Jan, Feb, and Mar.  But wait, we can’t use an equals sign in our SQL statement and evaluate multiple values, can we?  No, no we can’t.  So let’s look at a sample SQL statement that will enable our upper-level drill-through:

SELECT
	 DimMarket
	,DimMeasures
	,DimProduct
	,DimScenario
	,CONVERT(VARCHAR(10),CAST([DimYear] AS DATETIME),101) as [Date]
	,CONVERT(CHAR(3), CAST([DimYear] AS DATETIME), 0) AS DimYear
	,TransactionNumber
	,Amount
FROM
	[SampleBasic].[dbo].[FactSample]
WHERE
    DimMarket  IN {{ "name" : "Market",    "expression": "#Market", "drillToBottom" : "true" }} AND 
    DimMeasures = '{{ "name" : "Measures", "expression": "#Measures" }}' AND
    DimProduct IN {{ "name" : "Product", "expression": "#Product", "drillToBottom" : "true" }} AND
    DimScenario  = '{{ "name" : "Scenario",  "expression": "#Scenario"  }}' AND
    CONVERT(CHAR(3), CAST([DimYear] AS DATETIME), 0)  IN {{ "name" : "Year", "expression": "#Year", "drillToBottom" : "true" }}

In our last post, we didn’t really talk about the query, we just used the simple sample.  Let’s take some time now to actually understand how the query engine in Drillbridge works.  First, you’ll notice double curly braces in our where clause.  This is what tells Drillbridge to evaluate an expression.  Inside of our curly braces, we have three settings that we need to define to get back upper-level members.

First we specify the dimension that we want to pull back from Essbase.  This is the Name property.  Next we have the actual value and how we want to format it.  This is the Expression property.  If you take a look at the PDF that included with Drillbridge, you can get an idea of all of the possible expressions, but for today we’ll keep this part simple and just use #DimensionName.  Our last property is optional if we don’t care about upper-level data.  But, because we care, we must set the drillToBottom property to true.

This is the setting that tells Drillbridge to take Qtr1 and find all of the level-0 descendants.  Now all we need to do is modify our SQL statement to use an IN instead of an =.  And with that we should be ready to deploy our new report.  So let’s try it out.  Let’s start by drilling into level-0 for everything but our Year dimension.  For the Year dimension we’ll try an upper-level query:

DrillbridgeUpper02

Once we drill-through, we see that we have Qtr1 in our POV and multiple months of data:

DrillbridgeUpper03

Excellent!  We have upper-level drill-through.  But wait, what about stupid user tricks?  Let’s take a look back at our report:

DrillbridgeUpper04

Here we have two ways to prevent long-running queries executed by users.  First, we can set a row limit.  This is generally enough, but sometimes, a user can find a way to execute a query that returns a low record count, but it still takes forever.  So as a safe-guard, we can also add a query timeout.  This way we are protected on both fronts.  So let’s start with the row limit.  If we enter a row limit of 10 and try out the drill-through again, we get this:

DrillbridgeUpper05

Great!  Only 10 rows!  But wait…what about the other rows?  The downside to the Community edition is that it does not offer a message to users to let them know that additional rows were suppressed.  Bummer.  But don’t worry too much, as we have a way around this that I’ll cover in my next Drillbridge post.  We’ll also cover query time limits in a later post as I still have to resolve the issue I’m having.

And with that, you should now have working upper level drill-through with at least a row limiter.  Our next Drillbridge post will cover some of the advanced features.


PBCS April 2016 Update: FDMEE On-Premise Is Here, Kinda…

Another month…another update to PBCS.  This month we have a few very interesting items.  First, we have a new version of the EPM Automate Utility with a few new commands:

  • copysnapshotfrominstance – This command can essentially replicate one instance to another.  This could make migrations a bit faster and more streamlined and possible less error prone.
  • provisionreport – This provides a report of which roles are assigned to which users.  The cool part here is that it also shows the “sequence of inheritance”.
  • userauditreport – In case you wonder who is actually using PBCS, this report should tell you who logs in and when.
  • setsubstvars – Similar to the MaxL command that performs the same function, we can now use the EPM Automate tool to create and set substitution variables.

Now let’s talk about what really makes this release interesting.  PBCS now supports integration with on-premise FDMEE!  This is great news that we have all been waiting for.  But wait…it requires FDMEE 11.1.2.4.200.  This confirms the rumors we’ve been hearing.  So what’s the problem?  11.1.2.4.200 of FDMEE isn’t actually available yet.  On the bright side, hopefully this means it will be released by the end of April…before the May update of PBCS.

Of course we also have the long list of defects fixed in this release.  I’ll let you look at the PDF for the list:

RCD_PBCS_April


The EPM Week In Review: Week Ending March 26, 2016

Because this is a 3-day weekend for many, I’m posting this a day early.  Also, today is the last day for early bird registration for KScope16.  So what’s new this week?

Patches and Updates:

DRM 11.1.2.4.330 has been released.  This includes a ton of new features and patches.  This patch as previously been released, but has been pulled and moved around and required a password at one point.

FCM 11.1.2.4.102 has been released.  There are quite a few bug fixes, especially for ARM.

New Blog Posts:

This week I continued my series on PowerShell and Hyperion.  I focused on executing the outline load utility from a remote server.

Amit shows us how to encrypt our password using the EPM Automate tool.

Celvin has cracked the Hyperion Planning API…cool stuff.  He has also made some updates to his Essbase Member Operations utility.  Busy week for Celvin!

Eric gives us a lot of great information about Exalytics.

Kate walks us through the new features found in the recently released 11.1.2.4.200 patch for HFM.

Sarah (now over at Big Red) shows us how to find the REST URL in the Cloud.

With KScope16 rapidly approaching and presentations due in just over two months, Heli’s post about how long it takes to prepare seems appropriate.

Opal discusses the future of the cloud as it relates to Oracle and customers having the cloud on site!  It is very interesting…but not yet coming to the EPM space.  Good read either way.

John Goodwin continues his series on the FDMEE Web Services.

Other News:

Today is the last day to sign up for the reduced early registration price at KScope16.com.  Be sure to use the US-Analytics code of USA to save an additional $100.


Hyperion and PowerShell: Executing Remotely

In our last PowerShell post, we covered using the Outline Load Utility with Hyperion Planning from PowerShell.  But, now we have MaxL scripts doing data loads and Outline Load Utility scripts doing meta-data loads and they are happening on two different servers. We need a nice way to join all of this together in a single batch process controlled by one server.  This let’s us have a true start-to-finish process that we can rely on.

But wait…how do we do that exactly?  First we have some pre-work to get out of the way before we can actually execute a process remotely.  To execute a process remotely, PowerShell has an invoke command that will create a remote session on another server with specified credentials and then execute a script.  First, log into the server on which you will be executing remote commands.  Start PowerShell with Administrative rights and execute this command:

Enable-PSRemoting -Force

Because we are passing credentials, we need to keep our password out of plain text.  So, let’s start by opening up PowerShell and executing this command to generate an encrypted copy of our password:

"YourPasswordGoesHere" | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString | Out-File "PasswordFile.pass"

One quick note on passwords.  If you have special characters that cause login failures, use the ` as an escape character in front of your special character.  I’m using the password to my Hyperion Service account from my Hyperion domain.  This generates a nice new file that we’ll use to pass our password later.  For now, let’s focus on the reason you’re here…free code:

###############################################################################
#Created By:	Brian Marshall
#Created Date:	2/16/2016
#Purpose:		Sample PowerShell Script for HyperionEPM.com
###############################################################################

###############################################################################
#Variable Assignment
###############################################################################

$PowerShellUsername = "Hyperion\hypservice"
$PowerShellPasswordFile = "PasswordFile.pass"
$PlanningComputerName = "HyperionPL"
$PlanningCommand = {C:\Data\HyperionEPM\PowerShell\Blog2Sample1.ps1}

###############################################################################
#Create Credential for Remote Session
###############################################################################
$PowerShellCredential=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $PowerShellUsername, (Get-Content $PowerShellPasswordFile | ConvertTo-SecureString)

###############################################################################
#Create Remote Session Using Credential
###############################################################################
$PlanningSession = New-PSSession -ComputerName $PlanningComputerName -credential $PowerShellCredential


###############################################################################
#Invoke the Remote Job
###############################################################################
$PlanningJob = Invoke-Command -Session $PlanningSession -Scriptblock $PlanningCommand
echo $PlanningJob

###############################################################################
#Close the Remote Session
###############################################################################
Remove-PSSession -Session $PlanningSession

And there you have it…set our variables, create a new credential for our connection, use that credential to create a remote session, execute a job, and kill the session.  So what happens when we execute this?

OutlineLoad8

Uh oh…is the PG version of what I actually said when I received this nice little message.  But, upon some research, it appears that the Outline Load Utility uses more than 150 MB of memory on execution.  But, the default limit for a remote session is 150 MB.  So now we have to connect to our remote server and start up a administrative command prompt.  Once you’ve done that, execute this:

winrm set winrm/config/winrs @{MaxMemoryPerShellMB="2048"}

You should get something like this if it goes properly:

OutlineLoad9

Now then…we can try again…

OutlineLoad10

We have a winner!  We have officially remotely executed an Outline Load Utility script…on another computer.  Cool isn’t it?


The EPM Week In Review: Week Ending March 19, 2016

This was a bit of a slow week…until it wasn’t.  Things picked up late this week.

Patches and Updates:

DRM 11.1.2.4.322 has been released.  Fixes a few bugs.

Disclosure Management 11.1.2.4.200 has been released.  There appear to be quite a few bug fixes.

The FDM to FDMEE Migration Utility V1.1 has been released.  Seems to fix a few bugs, hopefully the one making your migration difficult!

New Blog Posts:

This week, I blogged about the removal of the delete plan types button and started down the software path of the Hyperion Home Lab.

Vijay tells us how to solve IE-related issues in DRM with compatibility view settings.

Glenn reminds us not to be Glenn…oh, and he also mentions something about issues in older versions not being the solution to problems in the new versions.  But really…just don’t be Glenn! 😉

Jason tells us what he is looking forward to a Kscope this year.  I’m going to pretend I made the list…

Harry has released a new version of his cubeSavvy Utilities.

Robert tells us about Pi Day and more importantly the NGCP.  Not exactly EPM related, but I’m all for getting girls more excited about STEM.

Ludovic tells us about the member selection feature in Smart View that allows you to select either vertical or horizontal display.

Kate shows us a DRM quick tip on tracking changes to properties.

Cameron take a look at Smart Forms in his on-going PBCS series.  I have to say…Smart Forms are pretty cool.

Vijay shows us how to hack the DRM repository to rename objects.

Other News:

KScope16 early bird registration ends in a few days! Register by March 30 and use code USA to save a total of $400 on your Kscope pass.


Public Service Announcement: No More Deleting Plan Types (11.1.2.4 and PBCS)

Someone asked me today…how do I easily delete Plan Type in Hyperion Planning.  I thought to myself…why would they ask that?  In 11.1.2.3 they added a cool new Plan Type Manager that allows you to add, delete, and rename Plan Types.  But hey, I’ll fire up Planning and take some screenshots.

First, let’s look at the 11.1.2.3 to make sure that Brian hasn’t lost his mind:

PlanTypeDelete01a

Fantastic…I may be losing my mind, but at least I’m not quite there yet.  Clearly you can rename and delete Plan Types.  So then I fired up 11.1.2.4:

PlanTypeDelete02

That looks totally different!  First, there are no text boxes to rename Plan Types.  Second, there is no delete button at all.  Interesting.  For grins, let’s look at PBCS:

PlanTypeDelete03

I guess I shouldn’t be surprised that they look the same…  So now let’s try to add a new Plan Type:

PlanTypeDelete04

It seems there is no reason to check the documentation.  They seem to have removed this functionality on purpose.  While I’m sure they had their reasons, I’m not a fan of removing functionality.  Just for fun, here are the release notes for 11.1.2.3 where they specifically call out the additional new features:

https://docs.oracle.com/cd/E40248_01/epm.1112/planning_new_features/planning_new_features.html

And if you scroll way down you should see this:

PlanTypeDelete05

When I have some more time, I’ll be posting an article on deleting Plan Types using the Planning repository.  And then when I have even more time…I’ll show you how to do it on PBCS.  In the meantime, I’m off to delete a Plan Type the hard way.


Building a Hyperion Home Lab: The Build and Installing ESXi

Before we go to far, let’s consolidate what we’ve done so far.  Here’s a list of the entire series:

Ok, now that we have all of this hardware, what do we do with it?  Build a computer!  I won’t go too much into the details, as you can find plenty of information about this using Google, but here are the basic steps I took:

  1. Get the case ready by moving the motherboard stays around to match up to your motherboard.  For me, I had a 4U case left over from another project, so I used that rather than the Norco from my guide.
  2. Install your motherboard.  For me, this was an ASRock Server motherboard from the guide.
  3. Connect your front panel wires: For me, this included the power switch, reset switch, power LED, HD LED, and USB ports.
  4. Install and connect any case fans.  For me, I used the one’s that came with my 4U case.
  5. Install your processor(s).  For me, this was a pair of E5-2670’s.
  6. Install your memory.  For me, this was 16 x 8 GB DDR3 modules.
  7. Install your power supply and hook it to the motherboard.  For me, I chose the slightly cheaper than my guide Seasonic M12II.  I may update the guide depending on how I like it.
  8. Install your heat sinks.  For me this was a pair of Noctua 4U compatible models.
  9. Install any PCIe cards.  For me, this included an Intel X520-DA1 10GB network card and an Areca 1880i RAID adapter.
  10. Install your hard drives.  For me, this included one Sandisk Ullra II 960GB drive along with four Samsung 840 Pro’s that I moved from another system.

Here’s a shot of my system during the build:

2016-03-06 11.40.08

Ok…we have the computer built…now what?  It’s time for software.  If you want to go the route of using your new computer as a multi-purpose system, then you will likely want to install VMWare Workstation or Oracle’s VirtualBox.  At this point, I would recommend heading over to Jake’s blog, as I have no desire to redo all of the great work he has already done.  You can find his entire guide here.  You can skip straight to the VMWare stuff here.

But what about ESXi?  To start our ESXi installation, we need a way to install ESXi.  We also need a place to install ESXi.  What if we could use the same location for both?  Sounds good…let’s do that.  First, we have a few prerequisites:

  • Download the installation ISO for VMWare ESXi.  I am using ESXi 6.0 with Update 1.  The name of my ISO is VMware-VMvisor-Installer-6.0.0.update01-3029758.x86_64.iso.
  • Download Rufus.  Rufus is a great tool for creating bootable USB thumb drives.  You can get the portable version of 2.7 here.
  • Purchase a USB thumb drive.  I use a 8GB SanDisk Cruzer Fit from Amazon.  I’ve used SanDisk drives for this purpose on two other systems.

So I’ve downloaded my ISO, I’ve downloaded Rufus, I’ve purchased my thumb drive and I’m ready to make an ISO.  Now what?  Try this:

  1. Fire up Rufus.  It may prompt you to elevate to administrator priveledges, so do that.  It may also ask you about updates, I just answer No and move on.  It should look something like this:ESXiInstall01
  2. It may have picked your USB device by default, if not, choose the proper device:ESXiInstall02
  3. Make sure that MBR partition scheme for BIOS or UEFI is selected:ESXiInstall03
  4. Select FAT32:ESXiInstall04
  5. Select 4096 bytes:ESXiInstall05
  6. Select ISO Image and then click on the ISO button:ESXiInstall07
  7. Select the ESXi ISO image and click Open:ESXiInstall08
  8. Enter a volume label:ESXiInstall06
  9. Verify all of your settings and click Start.ESXiInstall09
  10. Click Yes when it tells you that something is obsolete:ESXiInstall10
  11. Click OK to erase the selected device and create your bootable thumb drive:ESXiInstall11
  12. Assuming all goes well, it should complete and look something like this:

ESXiInstall12

Now let’s plug our new thumb drive into our new computer!  You may need to modify your BIOS/UEFI settings to make the thumb drive your primary boot device.  Once you do that, you should be ready to run through the installation.

  1. You can either hit enter or wait for the automatic boot to take you into the installer:
    ESXiInstall14
  2. The installer will load:ESXiInstall13
  3. And load…ESXiInstall15
  4. Press Enter to continue:ESXiInstall16
  5. Press F11 to agree and continue:ESXiInstall17
  6. Select your USB drive from the list and press Enter.  The capacity should give it away.ESXiInstall18
  7. Press Enter to confirm that you don’t have anything on the USB drive that you care about, because it’s about to be erased:ESXiInstall19
  8. Choose your language and press Enter:ESXiInstall20
  9. Enter a password for your root account, enter it again, andpress Enter:ESXiInstall21
  10. Press F11 to start to installation to your USB drive:ESXiInstall22
  11. Watch the progress bar, grab a cup of coffee, get a snack:ESXiInstall23
  12. After a reboot, you should be booting into ESXi from your USB drive and see something like this:ESXiInstall24

And now we have ESXi running on our new computer.  Now go build a Hyperion environment!  Once you get to this point, I’d highly recommend taking a look at Jake’s series on building out your own environment.  You can see the entire series here.


The EPM Week In Review: Week Ending March 12, 2016

Another busy week…maybe not as busy as last week.

Patches and Updates:

Oracle released Account Reconciliation Cloud Services (ARCS).  PBCS finally has a cloud-based sibling.

Oracle has officially posted the landing page for another PBCS sibling…Enterprise PBCS.  While it is not yet available, it does have pricing information.

Jason released version 1.5.5 of Drillbridge, now under the Applied OLAP license and with a few bug fixes.

New Blog Posts:

This week, I blogged about Drillbridge and then Drillbridge again.

Doug and Brian have started a series on Migrating from FDM to FDMEE: Into, FDM Migration Utility System Requirements, FDM Migration Utility Secret Sauce.

Opal walks us through the new FR Web Studio.  She also reminds us that PBCS requires Adobe Flash to properly use Calculation Manager (because sometimes Oracle thinks it’s still the early 2000’s).

Another FDMEE post from Dayalan discussing end-user access.

Eric has an introduction to the aforementioned ARCS here.

My long-time colleague at US-Analytics, Terry, provides a tutorial on PSE 22506614.  Clearly we need to work on his blog titles…

Another FDMEE post, this time from John Goodwin (who knows everything…seriously…everything) continuing his deep dives into the various Web Services.

Will starts off a series on loading data into Planning using the Outline Load Utility with an introduction to encrypting your password.

Other News:

Microsoft announced that they will be bringing SQL Server to Linux.  No, it’s not April 1st, this is real.  Also, Hell has frozen over.

We had a good lunch gathering here in Dallas on Friday at Thai Star in Addison.  You can still join the MeetUp group here in case you missed this one but want to catch the next one.

ODTUG has announced the Hands On Training for KScope16.  Get all the details here.


Drillbridge Aquired by Applied OLAP and Community Edition 1.5.5 Released

Applied OLAP has officially completed the acquisition of Jason Jone’s Drillbridge product.  This is great news for those of us that are fans of the product as it gives Drillbridge the backing of a company with a long-standing record of great support and other great products.  Jason will now be working directly for Applied OLAP.  Congrats Jason!  We’re all excited to see what comes next for Drillbridge.  His official update.

Speaking of what comes next, there has also been a new release of the Community Edition.  Version 1.5.5 is available here once you register.  Stay tuned for another post on upper level drill-through with Drillbridge!


Drillbridge: Introduction

***Updated 2016/03/10 for Firefox Issue Fix***

So you have an existing Essbase model and you are finally ready to provide your end-users with drill-through.  But wait…EIS is dead and buried and while Essbase Studio can be made to function with an existing cube, it is somewhat painful to actually make it work properly.  On top of that, it just isn’t very flexible in the way that it provides drill-through.  So what do we do?

I guess we could technically go build our own custom drill-through definitions and figure out a way to make it work.  There’s always FDMEE, but that’s not cheap and doesn’t offer upper-level drill-through.  Enter Jason Jones (a fellow Oracle ACE) and his amazing tool just for drill-through.  Drillbridge is one of the coolest third-party tools I’ve used in the EPM space.  So how do we get started?

First, let’s head over to the Drillbridge website and download the Community Edition (1.5.4).  This is the free version and a great place to start.  You can download 1.5.4 here.  You will need to register and login to the site to actually see the download link.  Once we have the ZIP file downloaded, we can move on to installation.

Before you install Drillbridge, make sure that you have Java installed and in the PATH.  You can verify this by opening up a command prompt and typing java -version.  Once we verify that Java is working properly, the installation is pretty straight forward.  We just unzip the file that we downloaded into a directory on our server.  I chose E:\Drillbridge.  You should end up with something looking like this:

Drillbridge01Open up a command prompt change directories to the bin folder and test Drillbridge by typing Drillbridge.bat console.  Assuming everything work, you should see something like this:

Drillbridge02

You can go ahead and stop the service and now we are ready to install the service by typing in Drillbridge.bat install.

Drillbridge03

This should create a new service.  We can run Services.msc to make a quick change to the service.  I like all of my services using my Hyperion Service account, so I right-click on the new service and click Properties.  Once on the properties screen, click on the Log On tab and enter your credentials.

Drillbridge04

Then click OK and you should be ready to start up your service:

Drillbridge05

Now we can log into the system and start our configuration!  Let’s fire up the website and take a look.  You should be able to navigate to [Servername]:9220 which should provide you with the splash screen:

Drillbridge06

We’ll start by creating a connection.  Click on the Connections button at the top of the screen and you should be prompted for a username and password.  The default username is admin and the default password is drillbridge.

Drillbridge07

Once you are logged in, you are ready to click the New Connection button:

Drillbridge08

In case you want to test this with everyone’s favorite Essbase application, I’ve created a SampleBasic SQL Server database for you to play with.  I’ve also included a SQL Essbase Load Rule to load data our Sample.Basic Essbase database.  The database includes a Fact table with 1.25 million records of data to load along with level 0 meta-data tables and a full extract of the meta-data from Sample.Basic courtesy of the Advanced Outline Extractor from AppliedOLAP.  You can get both here.  If you end up using my sample, your connection should look something like this:

Drillbridge09

Now that we have a SQL data destination for our drill-through, we are ready to configure the Essbase side of things.  First, we’ll add an Essbase server.  Click on the Servers button at the top of the screen and then click on New Server button.

Drillbridge10

Enter your Essbase details and click Save.

Drillbridge11

Now that we have our Essbase server configured, we can add our actual Essbase database.  Click on the newly created server:

Drillbridge12

Once you click on the server, you should see a new tab.  Click on the Cube Mappings tab:

Drillbridge13

Click on the New Cube Mapping button:

Drillbridge14

Enter the details for your Essbase application and click save.  I’ll be using Sample.Basic to go with my sample data:

Drillbridge15

Now that we have our database configured, we need to configure the deployment specs.  What’s a deployment spec?  Basically we need to specify the intersections in our Essbase database that will be enabled for drill-through.  We specify the deployment spec once because we may have multiple drill-through options for each of our intersections.  To add our deployment spec, click on Admin in the top right corner and then click on Deployment Specs.

Drillbridge16

Click on the New Deployment Spec button:

Drillbridge17

Enter a name and a set of intersections in the specification.  Also, check the box indicating Level-0 only.  We’ll start simple in this blog post and in our next post we’ll head to the upper levels where the fun stuff happens.

Drillbridge18

The code that I used basically gives us level 0 for all of our dimensions except the Scenario dimension.  I’ve just specified Actual and Budget as the Variance members are calculated anyway.

@RELATIVE("Year", 0),@RELATIVE("Measures", 0),@RELATIVE("Product", 0),@RELATIVE("Market", 0),"Actual","Budget"

We have all of the foundation ready to go, now all we need is a drill-through report.  Click on Reports and then click on New Report.

Drillbridge19

We’ll give it the report a name, choose a connection, and provide a query.  We’ll also select an Essbase connection and choose a few options at the bottom.

Drillbridge20

And here’s the code for the query:

SELECT
	 DimMarket
	,DimMeasures
	,DimProduct
	,DimScenario
	,CONVERT(VARCHAR(10),CAST([DimYear] AS DATETIME),101) as [Date]
	,CONVERT(CHAR(3), CAST([DimYear] AS DATETIME), 0) AS DimYear
	,TransactionNumber
	,Amount
FROM
	[SampleBasic].[dbo].[FactSample]
WHERE
    DimMarket  = '{{ "name" : "Market",    "expression": "#Market"      }}' AND 
    DimMeasures = '{{ "name" : "Measures", "expression": "#Measures" }}' AND
    DimProduct = '{{ "name" : "Product", "expression": "#Product" }}' AND
    DimScenario  = '{{ "name" : "Scenario",  "expression": "#Scenario"  }}' AND
    CONVERT(CHAR(3), CAST([DimYear] AS DATETIME), 0)  = '{{ "name" : "Year",  "expression": "#Year"  }}'

Once we create the report, we still have a few things left to do.  Click on the report name to modify the report.

Drillbridge21

Click on the Validate tab to verify that the connections to both Essbase and our Relational database are good along with the report definition.

Drillbridge22

Click on the test tab and enter an intersection and click Build It to see a preview of the drill-through report.

Drillbridge23

And here’s the sample:

Drillbridge24

Finally, we’ll choose our Deployment Spec and  Server and click the Deploy button to finish off our first drill-through with Drillbridge.

Drillbridge25

Now we can fire up Smart View and try it out.  We’ll drill into a level-0 intersection and make note of our value ($249,683):

Drillbridge26

And here’s our nice drill-through report courtesy of Drillbridge:

Drillbridge27

Our numbers up top tie (since this is pulled from Essbase…it should).  You can click on the Excel link and tie out the numbers.

If you are using Firefox as your default browser, beginning in 11.1.2.4, it requires the Oracle Smart View for Office 11.1.2.5.x extension.  If you don’t have this installed, you will likely receive something like this:

FirefoxFail

That was my error until I installed this:

FirefoxWin

And that’s it for today.  This is one very long post, but there’s no great way to break it apart.  Go grab Drillbridge and give it a try.  Exciting things are coming and I can’t wait to use this product at several of my clients.  As we continue to explore this tool, we’ll move onto upper level drill-through and then move onto some of the more advanced features.