BI Blogs

A

Go to http://www.oracle.com/technology/software/products/middleware/htdocs/111110_fmw.html and accept the license.

Browse down to the section that says “Portal, Forms, Reports and Discoverer”. 
Currently there are downloads available for Windows and Linux:

Windows x86: Disk1 (File 1) , and Disk1 (File 2)
Linux: Disk1 (File 1), and

Search Engine Land has a post, Michael Jackson’s Death: An Inside Look At How Google, Yahoo, & Bing Handled An Extraordinary Day In Search, on how web traffic spiked at some of the web’s leading properties like Google, Facebook, and Wikipedia, as a result of Michael Jackson’s death.

All good and fine, and a sad day for fans of Michael Jackson, the king of pop as he was known as, but a sad day

It’s been almost two weeks since I attended Podcamp Ohio, and I’m determined to publish a post today summarizing the event. I’ve started with my notes from Twitter as a base, and added in lots of links plus a few edits. I hope you’ll find them useful.

Creative Commons and your blog: legally using other people’s stuff by Michelle Lentz. This was a usseful talk about where to find images, sound and other files that you can legally use on your site.

• search.creativecommons.org is an easy way to search all creative commons content on web.
• ccmixter.org has lots of free use creative commons sound files for use in podcasts.
• Creative commons rule of thumb: do onto others’ stuff as you’d like them to do onto yours.
• This creative commons video from creativecommons.org is good.
• Creative Commons licenses are non-revokable. anyone using your stuff b4 you pull it is grandfathered to use.
• Wow. Lots of different types of creative commons licenses are available.
• Rules for the non-commercial creative commons license have a lots of gray area.
• Commercial fair use test: does the use impinge on original artists’ right to make a profit?

index.php?/archives/533-What-I-learned-at-Podcamp-Ohio.html#extended”>Continue reading “What I learned at Podcamp Ohio”

If you see the Discoverer 11g Documentation library at http://download.oracle.com/docs/cd/E12839_01/pfrd.htm, you will notice the familiar set of docs, with one new addition. There is now a doc for the Discoverer Web Services. The “Oracle® Fusion Middleware User’s Guide for Oracle Business Intelligence Discoverer Web Services API”, 11g Release 1 (11.1.1), Part Number E10412-01 can be viewed at

• Last
  week we wrote about the reason for the existence of Spreadmarts, and the problems,
  costs and risks of doing business in this way.
• This week, we talk about a solution for reporting, planning and analysis without
  Spreadmarts and without the risks. A solution that delivers on the much discussed
  but seldom realized “one version of the truth” and one that still gets the day-to-day
  job done …

g>

The People Remedy
One very common dynamic driving spreadmarts is a lack of communication and
trust between business and IT. The business doesn’t adhere to the architectural standards
and processes designed to support its long-term interests, while IT doesn’t move fast
enough to meet business needs. Both business and IT need to recognize each other’s
strengths and weaknesses and realize the mutual rewards of synergy.

A Technical Remedy

It’s important to remember that spreadsheets (not Spreadmarts) are an important part
of any organization’s technology stack. Problems only arise when spreadsheets are
used as data management systems that house corporate data for decision making - reporting,
planning and analysis. The technical remedy is to manage and store data and logic
centrally in a uniform, consistent fashion and let end-users access this data using
spreadsheets and other client access tools. It’s the best of both worlds. IT collects,
integrates, and validates data … The Business analyzes, identify trends, creates plans,
and makes decisions. The presentation layer is separated from the logic and data.

Walking the Talk

This is easier said than done. Implementing a solution that resolves the Spreadmart
nightmare is rather elusive. More often than not, it’s a lack of a technical remedy
that rules out the people remedy.

Take budgeting for example. While most believe it is a crucial element of financial
management, it is often described as burdensome and time consuming. Line managers
typically see little benefit from their budgeting effort. In many cases, this is because
the underlying budgeting technologies are simply spreadsheets and email.

What if these line managers had a budgeting solution that eliminates the well documented
problems associated with spreadsheet hell? A system that significantly reduces the
time spent rolling-up, checking and correcting the numbers. A system where each line
manager will also have their financial results, a dashboard of relevant corporate
goals and their own KPI’s to refer to at the time of entering budgets.

CALUMO Performance Management solutions provide this and more. A solution that is
about analysis and insight, not just data. CALUMO synthesizes core capabilities that
embrace spreadsheets, business needs, a unified data management platform and the role
of IT.

• A single integrated reporting, analysis, dashboarding, planning, budgeting, and consolidation
  application built on a unified BI and performance management platform.
• Deep integration between Excel spreadsheets and where the data is stored.
• An extension beyond spreadsheet with a thin web client. Fast, secure, scalable and
  available anywhere. With all the benefits of an Excel spreadsheet, including writeback,
  but without any of the problems.

Benefits

• Solutions like these are typically associated with complex and costly deployments.
  The unique proposition from CALUMO combines a single integrated application, a unified
  platform, deep Excel and web capability and most critically, business value.
• Where organizations integrate business analytics and planning with governance, risk
  and compliance, they can significantly reduce the ongoing cost of compliance and gain
  a true competitive advantage.
  
  The real payoff is using the time saved to do better analysis, to optimize the plan,
  and make better, faster decisions.

If you create a SAS OLAP cube in 9.1.x and their is some some sparsity in the values (when is there not) then you will end up with lots of .’s when no values exist for an intersection.

This is not really a problem till you use Web Report Studio to report off the cube and then the user exports the report to excel in a non formatted format.

When they do this the . is passed to excel and then Excel refuses to apply formulas nicely when creating them on these cells (funnily enough the Excel sum formula does work for these).

So in this case you want to force WRS to show .’s as 0 and then they export as 0’s and excel is happy (or you can force the users to manually search and replace them each time they export if you are so inclined).

So the work around for us was to create a custom measure in the information map that changed all the .’s to 0’s.  the MDX for this was:

IIF(<<Measures.Cost>>.Value =  null , 0 , <<Measures.Cost>>)

Of course the down side of this is that the OLAP server has to do the calc each time it renders the values rather than a look up.

In hindsight we could have created a custom measure in the OLAP cube that did this and it would be calculated during the cube build rather than during the report rendering.

It was very exciting to see an email from the MVP Award program in my inbox this morning saying that I had been re-awarded as an MVP for another year. This will be my 4th year as an MVP and I am very honored  to have been re-awarded.

A few weeks ago I did a post introduced the ExecuteSQL .net stored procedure for SSAS. Chris Webb asked if this function can be called from Excel 2007 when it is set this up as a rowset action and I figured that this would make a good topic for a blog post. So the following screen shots show how you would go about setting up such an action. As a quick example I cheated a bit and set up an Rowset action that calls the sp_who2 system stored procedure. This way I did not have a depedancy on any particular database. You don’t have to use a stored procedure, you can use any sort of SQL command that returns a set of rows into a rowset action.

Below is how I setup the action:

When you right click on a cell in a pivot table you will see the following:

And clicking on the “Rowset Action” creates another sheet that looks like this:

Since I posted about the ExecuteSQL function I have made a slight change to the code I checked into the ASStoredProcedure project on codeplex. I have now moved it into a separate “SQLQuery” project so that it compiles to it’s own .dll file and does not have to be deployed with the same impersonation mode as the main ASSP assembly.

In the last 2 blog entries, i had covered 2 new features of EPM 11 Essbase. They were Format Strings and Varying Attributes. In today’s blog entry we shall see another good feature that was introduced in the EPM 11 release called as XOLAP. Though I have covered this before here, i  thought it would make sense if i introduce this again in the context of the BI EE – Essbase connectivity.

Prior to XOLAP, Essbase supported HOLAP (still does) wherein one can drill from an Essbase cube to a relational source(only on BSO cubes) thereby providing a drill-through. It also supported something called as LRO’s in BSO cubes wherein one can attach an artifact to a database cell. What was not possible though was visualizing relational and Essbase data together. For example, we might have Actuals loaded inside Essbase but Budget might be obtained directly from a relational source. In such cases HOLAP cannot be used directly(though some workarounds are possible). With the advent of BI EE – Essbase connectivity in the 10.1.3.3.2 release, such complex integration cases have been made possible within BI EE framework using conforming dimensions. For details on how this is done, check out the ODTUG white paper here that Mark and myself had created. But what if we want this kind of reporting in Excel-addin or smart view or any other downstream tools that use Essbase. This is where XOLAP can be very helpful.

For the sake of demonstration, i would use the Global schema here. Lets first start with building a XOLAP cube using the Essbase studio. Start with importing the data source and then creating the model.

Then build your hierarchies and deploy the cube as a XOLAP cube. Remember whenever a XOLAP cube is created, it is an ASO cube. Also, it gets created with “Duplicate Members” turned on.

Deploy this cube. Once the deployment is done, you can login to Excel-add in and view the data.

We now can report directly on a relational source through Essbase from Excel-add in. Our idea is to have a similar reporting structure but also have one more measure called Price which would be coming in directly from Essbase itself. In order to achieve this, create another ASO cube directly in EAS or in the Studio with a similar dimensional structure. It is not necessary that the ASO cube should have an exact dimensional structure as the XOLAP cube. But in our case for demonstration, we would create an exact similar structure. There would be another measure in the ASO cube called as Price.

Now load some data into Price measure alone and aggregate it.

Basically we have 2 cubes, one reporting on relational data using XOLAP and the other is a normal Essbase ASO cube. Now, in order to have a report with both Units and Price measure together, we need to create an additional ASO cube called GlobTarg which will be fed by the XOLAP and the ASO cube through transparent partition. So, lets first create the outline of GlobTarg first. Ensure that it has both Price and Units measures.

Now create 2 transparent Partitions,one with the XOLAP cube as the source and GlobTarg as the target and the other with the Price ASO cube as the source and GlobTarg as the target.

While creating the partition, map the corresponding source measures to the target measure. Once this is done, you can report directly on GlobTarg ASO cube. And you should be able to report both on the ASO as well as the relational source together.

Pervasive BI is on the rise, offering everyday business users access to
BI. Extending BI to frontline users opens up new venues such as rooting
business culture and enhancing employee training. In this expert
webcast, learn more about the pros and cons of pervasive BI solutions,
specific vendor offerings and ways to get executive buy - in.

>>>
Listen to my webinar on Pervasive BI Solutions

Use this link - http://www.oracle.com/features/hp/oracle-fusion-middleware-11g-launch.html - to register for the event.

Apart from the very important reason that Fusion Middleware is the technology platform and foundation of Fusion Applications, Fusion Middleware 11g is also the vehicle for the release of Discoverer 11g.

Last week saw the release of the latest version of CALUMO for SQL Server Analysis
Services, CALUMO 11.4.  This release has rolled up all the features of the CALUMO
11.3 cumulative updates as well as delivering a set of new features and performance
improvements.

Highlights

• Text Writeback architecture update
• Publishable Drop Lists and Date Pickers
• Excel VBA API
• New Installers
• Windows 7 and Windows Server 2008 R2 Compatible
  

k Architecture Update

This feature is a key differentiator within CALUMO 11 and in our recent work we took
a good look at the existing architecture and reworked it for performance and scalability.

Publishable Drop Lists and Date Picker

Our standard CALUMO drop lists have been enhanced to also support data write back.
Now drop lists can be used to enter data from a standard selection list.

Fig 1: CALUMO Drop Lists

The date picker simplifies selection of dates. For example, when budgeting the start
date of a new employee using the date picker, a date is written back. Budgets salary
and on-costs will calculate and budget from this date.

Fig 2: CALUMO Date Picker

Excel VBA API

These API allow automation and control of certain features of CALUMO from VBA macros
within Excel.

New Installers

CALUMO installation is already a very easy process. For example, users can install
or upgrade the CALUMO Excel client with a one button click from the status bar, thereby
synchronising their version with the CALUMO Server. Adding to this, our new CALUMO
installers are now smaller, faster and more reliable and can be automatically built
at any time for any revision of the CALUMO codebase.  This enables us to deliver
patches to the software as robust installers instead of as manually applied patches.

Other Features worth Noting

• Administrator can set a default start page a default view for a Cube.
• Administrator and Users can set default subsets for hierarchies.
• Sort and top/bottom count filters available in cube browse.
• We include a web based calculation engine supporting most Excel functions in CALUMO
  published web pages.
• Conditional formatted cells in CALUMO published web pages
  

CALUMO for TM1 10.3, the latest version of CALUMO for TM1 has been released. 
This release delivers deeper integration with IBM Cognos TM1 and includes many new
features and performance improvements.

Highlights

• Support for TM1 9.4.
• The CALUMO Excel 2007 Ribbon.
• CALUMO Sparks now bundled free with CALUMO for TM1.
• Cool UI updates like (e.g. page loading indicator for published reports making it
  easy to see a large report is actually loading).
• Many other enhancements, fixes and even better performance.
  

M1 9.4
CALUMO for TM1 10.3 supports TM1 9.4 and all other active versions still
supported by IBM. We note that you should install TM1 9.4 FP1 as IBM have identified
and resolved several critical TM1 issues in both the standard TM1 9.4 release as well
as the TM1 MR1 release.

CALUMO Excel 2007 Ribbon

Love it or hate it (and we love it), the “Ribbon User Interface” is one of the most
significant changes in Office 2007 which replaced the old menus and toolbars. The
CALUMO Ribbon is a task-orientated graphical user interface, making it easily to navigate
to the CALUMO capability you need.

CALUMO Sparks

Now part of CALUMO for TM1, CALUMO Sparks provides rich compact visualisations of
large amounts of data in a single cell. By combining charts and data together in a
table, we do away with the need to separately label axes and legends, or the need
to associate the table data with the chart information.

For identifying trends, comparing data and highlighting exceptions (with high density
information), CALUMO SPARKS makes the design of really compelling executive dashboards
easy.

CALUMO SPARKS provides Line, Pie, Bar, Bullet, Grid, Progress, Stripe, Trend, KPI,
Bubble, Heat Map & Harvey Ball charts. CALUMO SPARKS is an add-in for Microsoft
Excel.

Other Features worth Remembering

• Freeze Panes for Web Reports.
• Context Linking in Web Reports.
• SPF Discovery (Decomposition Tree) in Web Reports.
• Save Web Reports to PDF.
• Drill Through to Transaction in Web Reports.
• Dynamic Rows in Web Reports.
• Zero Suppress in Web Reports.
• Charts can now be published as either an image or ActiveX control.

Recent research conducted by the Economist Intelligence Unit on behalf of SAS illustrates both the scope of the proposed reforms and the scale of the challenge ahead in risk management. In March 2009, the Economist Intelligence Unit conducted a global survey of 334 senior financial services professionals, of whom 50 percent were C-level and all have responsibility for risk. The unit than carried out a programme of interviews with high-profile commentators including Alan Greenspan, former chairman of the Federal Reserve; Nassim Taleb, author of The Black Swan; and Peter Bernstein, founder of Peter L Bernstein Inc.

The new report, After the Storm: A new era for risk management in financial services, written by Phil Davis brings these two strands of research together.

When we started discussing the research process in January our main concern was to pluck subjects that would stay in the public domain from the middle of 2009 and beyond. As the popular media became risk experts overnight, this was going to be a challenge. We wanted especially to have Phil Davis interview key players in the financial market who would offer insight on the future direction of risk management, no mean feat in a very uncertain and fluid economic situation.

So at the start, the challenge was not so much operating with a crystal ball on the financial world, rather wondering whether the ball would be in play at all. In the end the survey gave us an excellent understanding of where financial services companies see the present situation and a view of the future.

With the global political focus remaining on re-establishing the creditability and integrity of financial services (at a local and global level), one of the most intriguing elements to come out of the survey was the lack of confidence the financial services community had in those (rating and regulatory) agencies tasked with benchmarking and challenging their approach to risk management. But many risk analysts did not have the culture to challenge their firms’ management to act on any blind spots in their approach to risk management that the risk team had identified. Alan Greenspan did make the point when interviewed by Phil:

The important lesson is that bank regulators cannot fully or accurately forecast whether, for example, sub-prime mortgages will turn toxic, or a particular tranche of a collateralized debt obligation will default, or even if the financial system will seize up. A large fraction of such difficult forecasts will invariably be proved wrong.

So we expect to see, in the near future, the recommendations that will take us in to a new era of global financial services, certainly around areas of (systemic, liquidity, stress testing, firm wide, Credit, Market and Operational) risk and capital management, regulated by the various governments and their agencies. Business confidence will be restored, slowly, but will lessons be learned and practical, appropriate measures implemented, at country, regional and global level?
Continue reading “Reality check: thoughts based on the recent risk management survey”

Data
integration suffers from an image problem. It has become synonymous with
extract, transform and load (ETL). Likewise, ETL has been regarded as a data
warehousing (DW) technology.  Both of
these viewpoints do not reflect current capabilities and greatly inhibit
enterprises in their attempt to integrate data to provide the information their
business needs.

Because of
this short-sightedness companies have lost opportunities to harness information
as a corporate asset. It increases the cost of integrating data, encourages the
creation of data silos and forces business people to spend an inordinate amount
of time filling the information gaps themselves through data shadow
systems or reconciling data.

>>> Continue to the rest of my article, Data Integration: Beyond ETL and Data Warehousing, on the Information Management website.

Back in February when I heard about Lyza, I thought right away of Tableau. Despite each one’s different strengths in data discovery and analysis, each appeals to the same broad group.

It’s an old group that’s getting new attention: creative analysts, or “cowboy analysts” to some. The like their data raw, not aggregated. They ask questions, forage, synthesize, analyze, and publish.

Joe Mako is one of them. Tomorrow, he’s launching a website for people like himself who use both Tableau and Lyza. Makometrics will publish every Monday morning and sometimes more often.

Joe is a network engineer at a Midwest ISP. He started at the tech support desk, where he saw how much help people needed looking at their data. “They didn’t understand exploring data,” he says. “They just don’t care.” But Joe cared enough to help with data analysis, and pretty soon someone gave him a tag line: “Make it happen with Mako.”

Posts he’s lined up so far:

• He’ll walk through data analysis problems from challenge to resolution. “I’ll be practicing something akin to the cycle of visual analysis.” (See the Tableau video “The Zen of Visual Analysis.”)
• Analysis of strengths and weaknesses of Tableau and Lyza
• Analysis of his “Visualizing Rambo Kills”: how he approached the dataset, and how he created the final result.
• Demonstrate sophisticated techniques in Lyza and Tableau. He’ll go into detail on such things as combining Lyza’s “previous” and “if” functions and the basics of summary functions like “sumcolumn” and “avgcolumn.”

Check it tomorrow (Wednesday, July 1): makometrics.com.