BI Blogs

One of the OLAP-Aware features of BI EE 11g in Answers is the introduction of Selection Steps for additional analysis. At a high level, Selection Steps provide additional analysis capabilities that are not available directly from the Criteria tab. This provides us with more control on the SQL generated.

Till 10g, to achieve a complex report, one had to rely on some complex formulas and understanding how BI Server will push those formulas in SQL. We did not have a control on whether we wanted a filter or a calculation to be done as part of the query itself or outside of the query. In 11g, we now have that ability through selection steps. At a high level, the diagram below shows how Selection Steps work

Lets try to understand this through an example. One of the common requirements while doing a retail product analysis is to find out the Top 10 products that have been sold in a day. The requirement is to show the Top 10 performing products and also group every other product which is not in the top 10 into a common category called Others. A very common requirement as shown in the screenshot below

This seems to be a very simple example as all we want is to group certain products based on their Rank. We cannot use TopN function for this as TopN applies a filter and hence cannot group the remaining records. So we start of, like in 10g, with using a simple case statement as shown below

CASE WHEN RANK("Sales"."AMOUNT_SOLD") < 11 THEN "Products"."PROD_NAME" ELSE 'Others' END

Lets try to use this in the report and see what happens.

As you see this has not worked for us as BI EE does not know that the Rank has to be done first and then CASE statement has to be applied. You will basically see the same behavior in 10g as well. So, how do we go about solving this requirement without making any change to the repository. This is where Selection Steps can come in very handy.

So we start off with including Product Name, Rank and the Sales column in the report as shown below

As a next step, we include the same case statement that was used above. This time it will work as we are at the Product grain.

Then we need to basically sum up the sales of all products that fall under the Others category. This is achieved by modifying the Measure to use the formula as shown below

SUM("Sales"."AMOUNT_SOLD" BY CASE WHEN RANK("Sales"."AMOUNT_SOLD") < 11 THEN "Products"."PROD_NAME" ELSE 'Others' END)

Till this step we can do the same thing in 10g as well. But if you look at the report, we now have to show only the first 11 rows as that is what is required by the end users. So what we want is a capability to apply a filter on top of what we have created in the report. To do that we start with all member in the selection step and then create a new Selection Step condition as shown below

In the condition specify a filter of the Rank column to be less than 12 so that only the first 11 records are chosen.

Now hide the first product column. This will provide us with the desired output.

If you look at the query generated, you will notice that the Selection Step filter is applied on top of the reporting query.

WITH
SAWITH0 AS (select sum(T44322.AMOUNT_SOLD) as c1,
     T44287.PROD_NAME as c2
from
     SH.PRODUCTS T44287,
     SH.SALES T44322
where  ( T44287.PROD_ID = T44322.PROD_ID )
group by T44287.PROD_NAME),
SAWITH1 AS (select 0 as c1,
     D1.c2 as c2,
     case  when Case when D1.c1 is not null then Rank() OVER
( ORDER BY D1.c1 DESC NULLS LAST ) end < 11
then D1.c2 else 'Others' end  as c3,
     Case when D1.c1 is not null then Rank()
OVER ( ORDER BY D1.c1 DESC NULLS LAST ) end as c4,
     D1.c1 as c6
from
     SAWITH0 D1),
SAWITH2 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     sum(D1.c6) as c5
from
     SAWITH1 D1
group by D1.c1, D1.c2, D1.c3, D1.c4),
SAWITH3 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c6,
     ROW_NUMBER() OVER (PARTITION BY D1.c3, D1.c2
ORDER BY D1.c3 DESC, D1.c2 DESC) as c7
from
     SAWITH2 D1),
SAWITH4 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     sum(case D1.c7 when 1 then D1.c6 else NULL end )
over (partition by D1.c3)  as c5
from
     SAWITH3 D1)
select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5
from
     SAWITH4 D1
where  ( D1.c2 in ('17" LCD w/built-in HDTV Tuner',
'18" Flat Panel Graphics Monitor', '5MP Telephoto Digital Camera',
'8.3 Minitower Speaker', 'Envoy 256MB - 40GB', 'Envoy Ambassador',
'Home Theatre Package with DVD-Audio/Video Play',
'Mini DV Camcorder with 3.5" Swivel LCD',
'SIMM- 16MB PCMCIAII card',
'SIMM- 8MB PCMCIAII card', 'Unix/Windows 1-user pack') )
order by c1, c4 NULLS FIRST, c2

Selection Steps provide more flexibility and at the same time providing opportunities to create more complex reports.

There are two ways to search for texts in mappings. The first one is to use the “Find in workspace” tool inside Informatica (CTRL+F). The problem with this tool is that it’s looking for ports and transformation names. What can you do when you need to know in which transformations you used your unconnected lookup? For this sort of questions, you can use the other way: Export the mapping to XML file (Repository -> Export Objects) and search inside the file. There, you can search inside expressions, source qualifiers overrides, lookup filters and so on.

When using the date picker in OBIEE, the date format that it works with is DD/MM/YYYY. In out organization, oracle is tuned to use another date format, so it causes trouble. What we did to solve this is very simple. In the OBIEE Administration tool, go to the physical layer and open the connection pool. In the connection script tab, enter the following into the “Execute before query” section: alter session set nls_date_format = ‘DD/MM/YYYY’
This statement will execute before every sql the OBIEE sends to oracle and tell the
DB to use the DD/MM/YYYY format.

After a long break from blogging which caused by a lot of work in my organization,
it’s time to come back. I learned a lot in the last months and I have a lot to write.
This time I’ll write about working with static parameters
in Informatica (version 8.6.0 HotFix 3).

Let’s start from the beginning: Why should you work with parameters?

The answer (at least the main answer) is simple: It prevents hardcoding. For example,
let’s assume you write the following formula in your mapping: IIF(MyPort > 5, TRUE,
FALSE). As we know, in the real world everything can change and now the formula needs
to have 6 instead of 5. Changing this will make us open the mapping, edit it and worst
of all - install it in the production environment, and as we know it can always
make a lot of trouble. If the number 5 was stored in parameter file, all we needed
to do is to edit the parameters file (in the production, of course) and that’s it!
Another good reason is that sometimes you can use the same mapping several times,
each time with different parameters and that can ease the development for you.

How can we make it? We’ll do it step by step (by the way, in order to learn this thing
create a small workflow just for training):

Step 1 - Using Parameters in the Mapping

In your mapping, go to the Mappings menu and choose “Parameters and Variables…”.
There, you can create and edit your parameters (only within this mapping, of course).
The name of the parameter should start with $$. (For example: $$MyParam). These are
the properties you can edit for every parameter:

• Name - Like I mentioned, should start with $$. Make the name as much descriptive as
  you can.
• Type - Parameter can’t be changed during the session and variable can. In our scenario
  we’ll use parameters which are taken from parameters file, so choose Parameter.
• Datatype - There’s no much to explain.
• Precision - If you’re using string, make the precision at least 100 chars. It will
  prevent troubles when your parameters contains expression.
• Scale - comes along with precision, where it relevant.
• Aggregation - Relevant when you use multiple partitions in the pipeline. We’ll leave
  it for now as the default.
• IsExprVar - Very important. It determines if the parameter is an expression or a static
  value. Practically, if you use it inside an expression transformation, it should be
  True. Otherwise, it should be false.

After you defined your parameters, you can use it in your mapping in several ways:

erride the whole SQ or add a source filter from parameter.
• Lookup - you can override the lookup’s sql or just its filter.
• Expressions - Using a parameter as a placeholder inside expression is quite difficult
  because Informatica adds “” before and after the expression. Coming back to the example
  above, that’s why we can’t store 5 in the parameter. What we’re doing to solve that
  is to store the whole expression in the parameter (the whole IIF(…)). After doing
  that, we’ve find out that this makes the parameter file clearer.

Step 2 - Build your parameter file

The parameters file, as its name suggest, holds the values of the parameters for the
whole workflow. There are three types of lines in the param file:

• Comment - starts with #. Very helpful for describing the other lines and for making
  order in the file.
• Section headers - The first section contains the global parameters which are relevant
  for all the params in the workflow. The section starts with line contains only: [Global]
  (this should be the first line in your param file). After this (first) line, write
  all the global parameters. The other sections are session-specific, meaning that the
  params in these section will only affect one session. These section headers will look
  like this: [MyInformaticaFolder.WF:MyWorkflow.WT:MyWorklet.WT:MyInnerWorklet.ST:MySession].
  The parameters that will come after this header will only affect the session called
  MySession which is inside the the worklet MyInnerWorklet and so on.
• Parameters - each line in the file will contain only one parameter. The syntax is:
  $$MyParam=value. Don’t write space between the equality sign (=) and the value because
  it will enter into the parameter itself.

As you can see, it’s quite simple. Now, let’s end this with the last step.

Step 3 - Attaching the parameter file to the workflow

Very easy. In the workflow manager, open the workflow and go to Workflows -> Edit
-> Properties. Insert the parameter filename (with the path, of course) and that’s
it.
Now, for some important tips that will save you a lot of time:

• The integration service reads the param file only when you run the workflow.
  Re-running a session or worklet after changing the file won’t affect the results.
• Unline what they write here,
  you can pass parameters to the mapplet. In order to do so, this
  is the correct syntax in the param file: MyMappletName.$$MyParam=TheValue. When this
  line is under the session header, it will only affect the mapplet called MyMappletName
  which is inside the session/mapping.
• Don’t use initial values for parameters. You won’t know when the param file is not
  correctly attached to the session.

There’s much more to discuss - using Informatica, you can use dynamic parameters and
variables. You can get a value out from a session and use it in other sessions. I’ll
leave it for now for future posts.

My latest article for Oracle Magazine is now available online, and this month it’s a joint effort between myself and Venkat.

“Using Oracle Essbase Release 11.1.2 Aggregate Storage Option Databases” takes a look at the new calculation capabilities in Essbase 11.1.2, in particular looking at how data can be aggregated, derived and allocated within 11.1.2 Essbase cubes and persisted in the database, opening up ASO databases to the kind of planning and budgeting applications previously restricted to BSO databases. If you’ve heard that aggregate storage is the strategic direction for Essbase but wondered how it could handle the sorts of calculations provided by block storage, you may well find this article interesting.

I’m writing this on the plane back from Hyderabad, sitting opposite Jonathan Lewis who like me has just finished presenting at Sangam’10, the annual conference held by the All-India Oracle Users Group. I was invited over by Murali and the AIOUG conference committee to do a presentation on OBIEE 11g, which turned out to be a nice opportunity to road-test one of my Open World presentations before delivering it again in a few weeks time. The event itself was pretty good, with a welcoming team from AIOUG and a great audience at the actual talks. Here’s the introduction by Murali, which was followed by Oracle talking about the wider user group community and the new features in the Oracle platform.

Jonathan was of course the main attraction, running one of his one-day tutorials over two half days and with most of the audience attending both days. The room was pretty packed-out for both of his sessions, and here he is on the first morning talking about the pros and cons of analytic functions.

Given that the audience was mostly DBA and database developers, I was pleasantly surprised at the attendance on my BI session, which was on RPD data modeling techniques and new features in 11g. As most people were new to OBIEE and mainly worked in database maintenance and support, I tried to put RPD modeling in this context and in particular, how the features in the RPD could be used to complement and add-to the base data warehousing capabilities in the Oracle database. If you were there and would like a copy of the slides and accompanying white paper, they should be on the Sangam’10 website, and I’ve uploaded them to our Articles page as well.

After my presentation we held a joint BI and EPM SIG meeting, a “round-table” event where we discussed the various Oracle BI and EPM products and answered questions from the audience. One major difference I found in this event, compared to say SIG meetings in the UK and USA, is that the audience were mainly database administrators and would typically need to maintain an Oracle BI installation, rather than develop it from scratch. As such, they were interested in comparisons with competitors tools such as Cognos and Business Objects, and what advantages it gave customers compared to just running SQL reports against the base data warehouse. As such, some of the product capabilities that were of interest included the federated query capability in OBIEE, the packaged analytics that come with the BI Applications, and the ability of products such as Essbase and Oracle OLAP to make user queries run faster. In all, a great session and I was pleased I’d been able to get over to the event.

So whilst I’m on my way home now, I’m back over in November to run our three-day Oracle BI Training Days event in Bangalore, where we’ll be rolling out our new OBIEE 11g training materials and taking delegates through an end-to-end tour of the features in this new platform. We’ve still got a few places left, so if you were at Sangam’10 and you’ll like to get some hands-on OBIEE 11g training, or you’re just interested in skilling up on this new release, drop us a line at trainingdays2010@rittmanmead.com and we’ll reserve you a place.

There are numerous strategic risk issues that might keep a chief risk officer or chief executive officer awake at night. Particularly since the financial meltdown, many are asking themselves (and anyone who will listen), “Do we need to do more in risk management?”

Evaluating your program is an intense process, but you can start with some basic information. These questions, taken from The new risk era: Capturing the whole picture, are a wonderful exercise to help you evaluate your risk management strategy. Take a moment to honestly gauge your organization’s efforts surrounding risk management.

If you can answer “No” to any of the following questions, your organization can benefit from establishing a formal enterprise risk management approach or reevaluating its existing framework:

• Are your risk and compliance requirements properly integrated with your business priorities?
• Is your risk management process aligned with your strategic decision-making process and existing performance measures?
• Is your risk framework properly calibrated to recent market developments?
• Does your current risk framework drive timely, top-notch decision making?
• Can you proactively analyze the changes in exposures to your financial performance?
• Is your risk management process coordinated and consistent across the entire enterprise? Does everyone use the same definition of risk?
• Is risk data accessible to anyone who needs to see it, in a form they need to see, when they need to see it?
• Are you ahead of your competitors in meeting regulatory expectations about firmwide risk reporting and monitoring?

With these questions answered, you’ve developed a roadmap that you and your team can follow to begin the education process. rs, SAS’ Global Product Marketing Manager in Risk, gives three recommendations to deploy an enterprise risk management program: 1.) Embed risk management as part of your organizational culture; 2.) Integrate a governance, risk and compliance program; and 3.) Immerse yourself in the evolving world of risk. As a first step toward immersing yourself in the evolving world of risk - take a look at the thought leadership articles and examples on the Risk Management Knowledge Exchange.

Hereby my plea for Debra, who is in need for a little bit of help.
To finish up here presentation(s) for Oracle Open World see would really like to hear from you, in this case about using Oracle Fusion Middle Ware.
So how can you help…???
As Debra Lilley described it in an email to me:
As you […]

It’s almost that time again, and Rittman Mead will once again be attending and presenting at Oracle’s Open World conference and exhibition, running in San Francisco from September 19th-23rd 2010.

Our team are running thirteen sessions over the week, covering such topics as OBIEE 11g, Fusion Middleware 11g, Oracle Data Integrator, Oracle Golden Gate, Oracle Database 11gR2 and Oracle Exadata. Here’s the run-down on our sessions, and who’s presenting:

• Sunday 12.30pm, Moscone West 2002 : Mark Rittman, “Oracle Business Intelligence Enterprise Edition Dashboarding/Reporting and More”
• Sunday 12.30pm, Moscone West 2012 : Stewart Bryson, “Introduction to the ODTUG BI/DW Sunday Forum”
• Sunday 2pm, Moscone West 2020 : Jon Mead, “Oracle Exadata/Oracle Business Intelligence Enterprise Edition Retail Case Study”
• Sunday 4.30pm, Moscone West 2012 : Mark Rittman, Jon Mead, “Oracle Business Intelligence Enterprise Edition Panel”
• Tuesday, 3.30pm, Moscone West 3016 : Jon Mead, “An Oracle Exadata Case Study in the Retail Sector”
• Tuesday, 3.30pm, Moscone West 3020 : Borkur Steingrimsson, “Oracle GoldenGate In-Depth : What’s All the Fuss About?”
• Tuesday, 5pm, Moscone South 308 : Peter Scott, “Implementing a Retail Data Warehouse on the Oracle Exadata Platform : Lessons Learnt”
• Wednesday 10am, Moscone West 3010 : Mark Rittman, “Oracle Business Intelligence Enterprise Edition Architecture Best Practices”
• Thursday 9am, Moscone South 300 : Stewart Bryson, “Resuming, Restarting, Restoring : Three R’s of Data Warehouse Fault Tolerance”
• Thursday 11am, Hotel Nikko/Bay View : Stewart Bryson, “Real-Time Data Warehousing with Oracle BI and Oracle Database”
• Thursday, 12pm, Moscone West 3010 : Mark Rittman, “Oracle Business Intelligence Enterprise Edition Data Modeling Best Practices”
• Thursday 1.30pm, Moscone West 3010 : Venkat Janakiraman, “Oracle Business Intelligence Enterprise Edition / Oracle Fusion Middleware”
• Thursday 3pm, Moscone West 3020 : Venkat Janakiraman, “Oracle Data Integrator : ETL Loads on Oracle Essbase and Oracle Hyperion Planning”

We’ll also be at the various events, getting to as many sessions as possible and meeting up with our friends and customers within the industry. If you’re going to be there, stop by one of our sessions and say hello. We’ll also post the various presentations and papers on our website once the conference is over.

Vlamis Software Solutions, an Oracle Parner, along with NAVTEQ, are hosting a BIWA SIG (that would be the Business Intelligence, Warehousing and Analytics Special Interest Group) web seminar - BIWA SIG TechCast - Sept 8 - Noon Eastern (US) - Information Visualization Using Maps in Oracle Business Intelligence 11g. Use this link to register for the seminar.

There have been some changes happening on the Oracle Technology Network. As a result, the link to the Oracle BI EE 11g Certification Matrix that I had posted in an earlier post no longer works.
Use this link instead: System Requirements and Supported Platforms for Oracle Fusion Middleware 11gR1 (XLS format)
Bookmark this page for reference in either case: Oracle Fusion Middleware Supported System

The installer of version 1.6.10 (and 1.6.8) of TortoiseSVN has a bug that leads to an incomplete update. You must run the installer a second time and choose the second time the ‘Repair’ option for a proper upgrade. One of the symptoms of an incomplete installation is an exception when trying to compare office files […]

One of the questions discussed in the book that Marco, Alberto and I wrote last year, “Expert Cube Development with SSAS 2008” (available in all good bookshops, folks!) was whether, if you have multiple fact tables, you should create one big cube with multiple measure groups or multiple cubes each with a single measure group. While I still stand by what we wrote then, I recently took part in an interesting debate on this subject in the MSDN Forum with Akshai Mirchandani from the dev team about the pros and cons of each approach where some interesting new details came to light:

http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/681e59bd-93ca-4a91-9f26-8ed96e825553

Here are the main points you need to consider when deciding whether to use the single cube approach or the multiple cube approach. In favour of the multiple cube approach:

• Having multiple, smaller cubes may result in faster query performance than one large cube in some cases, especially if your fact tables have very different dimensionality. This was true in SSAS 2005, and while it’s less obvious in 2008 it’s apparently still there. This was what I’d previously not been sure about: I’d heard rumours about this, and seen it happen in some cases myself with 2005 - although in other cases when I’d tested this out I’d seen no difference in performance – and I wasn’t sure what the situation was with 2008. You’d need to test the two approaches yourself on your cubes and queries to be sure. Here’s what Akshai says on the matter:
  
  

  if you keep adding lots of dimensions to a cube, then the virtual space of the cube grows — it does not add to the storage cost, but it does hurt formula engine performance in some scenarios because the cell coordinates are based on the number of attributes in the cube space. Increasing the number of attributes in the cube space will start costing performance in lots of small ways and result in performance regressions. Adding lots of unrelated measure groups would result in you adding lots unrelated dimensions to the cube space and cause a performance slowdown — if you had 10 measure groups and they all shared lots of common dimensions, then one cube makes the most sense…

  …As I already explained… it affects the sizes of data structures inside the formula engine that are based on the number of attributes in the cube space. When those data structures get larger, there is an incremental cost that can add up (depending on your calculations and query patterns).

  For example, you see the Query Subcube Verbose events in Profiler — they show you the subcubes that are used for accessing measure groups. There are similar subcubes that are used for calculating formulas and cell coordinates — all those subcubes get wider and wider as you start adding more attributes into the cube. The cost of accessing and indexing those data structures is what we’re talking about here. If adding new measure groups doesn’t require adding new attributes/dimensions, then there is no problem…

  We had measured the difference before 2005 shipped for some real customer cubes and found there there was a noticeable performance improvement to split up into multiple cubes…

• to apply dimension security to the Measures dimension, it is much easier to allow or deny access to a cube with the multiple cube approach than it is to apply security to all the measures in a measure group using the single cube approach.
• Having multiple, simpler cubes can be much more user friendly than one monster cube with loads of dimensions and measure groups. If you have Enterprise Edition you can of course use Perspectives to counter this, but if you are using Standard Edition then Perspectives aren’t available.
• Maintenance can be easier and less disruptive with multiple cubes: if you need to make changes to a cube while users are querying it, you might end up invalidating users’ connections and dropping caches. With one cube the chances of this disruption affecting more users increases.
• It’s easier to scale out with multiple cubes: if you find your server is maxing out, you can simply buy another server and distribute your cubes equally between the two. With a single cube approach you end up having to look at (admittedly not that much) more complex scale-out scenarios like network load balancing.

re the arguments in favour of the single cube approach:

• If you ever need to work with data from two fact tables in the same query or calculation, or if you think you might ever need to in the future, you should go with the single cube approach. The two options for cross-cube querying, linked measure groups and the LookUpCube MDX function, should be avoided. Linked measure groups are a pain to manage, carry a slight query performance overhead, and can result in the same MDX calculations being duplicated across the original cube and the cube containing the linked measure group (which means maintenance becomes more difficult). The LookUpCube function is probably the worst MDX function to use in a calculation from a performance point of view and should be avoided at all costs. So a single cube is the only feasible option.
• Even if your users tell you they will not ever need to analyse data from two fact tables in the same query, be prepared for them to change their minds. In my experience, SSAS projects have a tendency to grow in complexity over time, and cubes that start out simple in a first release often grow lots of new functionality as time goes on - and the more successful the project, the quicker things get complicated. As soon as your users see what’s possible with SSAS they will start to have new, more ambitious ideas about the kind of analysis they want to do with their data, and it’s very likely that they will realise they do need to do cross-measure-group queries and calculations. If you started out on the multiple cube approach and then this happens you will have no choice but to use linked measure groups, and as I said this can make maintenance difficult; using the single-cube approach from the start means you won’t have this problem.

l preference is to use the single cube approach by default, and then move to multiple cubes if there are pressing reasons to do so, for example if query performance is a problem. This might seem a bit strange given the number of reasons I’ve given for the multiple cube approach, but frankly the need to support cross-measure-group querying and calculations trumps them all. As I said, if you need to do it (and 99% of the time you will), or you even half suspect you might need to do it sometime in the future, you have to go with the single cube approach. That said, I know other people are more inclined to the multiple cube approach than I am and to a certain extent it’s a matter of taste.

Self-reliant business users would like to use analytics more often to address changing market conditions and make quick decisions. Take, for example, a marketer or customer support specialist who might want to generate analytics and apply results to a variety of business issues, including:

• Determine which inbound customer interactions are best candidates for an up-sell or cross-sell or a retention offer.
• Recognize which customers are likely to respond and include them in the new campaign.
• Measure the propensity for an active customer to churn.

quote>Should that marketer have to rely on statisticians, modelers or data mining specialists to create predictive models every time? Are they frustrated because they have to wait for answers? Do they have to apply above issues to hundreds of product or service offerings? You bet! On the other hand, business analysts often do not understand all aspects of data mining and statistics.
Continue reading “Self-service predictive analytics is here. Are you ready?”

This question is asked many often. However there is no one answer. Reading a lot of reactions – in one of the Linkedin community groups – I noticed that the answer is strongly related to the (test) experience of the responder. A number of the reactions endorse this question and of course other people have […]

 Now that I have installed Oracle BI EE 11g, what I like to do is to create a another user with administrative privileges that I then use for my work. In this case, to satisfy a narcissistic urge I use my name as the user name - “abhinav”.

To do that, I first need to login to the Administration Console of Oracle BI EE. which on my computer’s installation is http://aagarwa-lap.idc.oracle.com:7001