Connect Xcelsius Dashboard to OBIEE

Posted on May 15, 2011 by Ron Keler

Oracle and SAP are two of the three leading BI vendors in the BI marketplace today. With Oracle just released OBIEE 11.g, and SAP is not far behind with BI 4.0 Both platforms are robust, enterprise grade, top of the line BI suites, and you could really not go wrong with either one. With that said, they have their strengths and weaknesses. OBIEE admin tool provides an exceptional way to manage semantic models and meta-data about your reporting environment. SAPs Xcelsius (SAP BusinessObjects Dashboards in 4.0) is an outstanding data visualization tool. Well, what if you could combine the power of OBIEE with the flexibility of the Xcelsius presentation…? Here’s how I did just that.
Xcelsius can connect to XML feeds very easily through Excel XML Maps. So all I needed to do was to figure out a way to get an OBIEE Answers report data as an XML stream. Fortunately, OBIEE has robust web services support that can be leveraged to accomplish exactly that.
First, I installed Tomcat on my OBIEE 10.1.3 environment. This version of OBIEE ships with OC4J, and it is running on my server, but I am not that familiar with it, and with deploying applications on it, so it was just as easy for me to install Apache Tomcat (I used Tomcat 6), which runs on port 8080 by default and used that for running my code.
Next, I spent most of the time figuring out which libraries I needed to add to Tomcat in order to instantiate and use the OBIEE web services classes. After a lot of experimentation, I found all I needed is packed into a single JAR file, sawsoapstubs.jar, this is of course not documented anywhere. Once I had a Tomcat server running with the correct lib, the rest was a piece of cake.
Of course, more can be done to integrate security and generalize the query access as needed, but the fundamentals are laid here.
So, here is what I did, and what you would need to do if you wanted to reproduce this in your OBIEE environment and produce stunning Xcelsius dashboards as part of your OBIEE implementation:
1.  Find sawsoapstubs.jar in your OBIEE installation (..OracleBIwebbinsawrepaj) and copy it to Tomcat lib directory

2.  Create a new web directory in Tomcat (I called mine xcelsius)

3.  Login to OBIEE and create an Answers report that contains all the data you want for your Xcelsius dashboard. Then, go to the report Advanced tab and copy the OBIEE SQL Issued

4.  Open the obiee_xlf_data_conn.jsp file (link to all example files is at the bottom of this post, also the obiee_xlf_data_conn.jsp code is below) and edit the file to reflect your host name, OBIEE username/pwd to use, and edit the query to be the one copied in the prior step. Access the edited .jsp file via a web browser (http://localhost:8080/xcelsius/obiee_xlf_data_conn1.jsp) and make sure you are getting results
obiee_xlf_data_conn.jsp:

<%@ page import=”java.net.URL, com.siebel.analytics.web.soap.v5.*, com.siebel.analytics.web.soap.*” %>
<%

try {

 SAWSessionServiceLocator awsessionservicelocator = new SAWSessionServiceLocator();
 XmlViewServiceLocator  xmlViewServiceLocator = new XmlViewServiceLocator();
 SAWSessionServiceSoap m_Session;
 URL url = new URL(“http://localhost:9704/analytics/saw.dll?SoapImpl=nQSessionService”);
 m_Session = awsessionservicelocator.getSAWSessionServiceSoap(url);

      XmlViewServiceSoap xmlService = xmlViewServiceLocator.getXmlViewServiceSoap(
      new URL(“http://localhost:9704/analytics/saw.dll?SoapImpl=xmlViewService”));
   String m_sessionID = m_Session.logon(“Administrator”, “Administrator”);
   String query = “SELECT Products.Brand saw_0, Periods.”Year” saw_1, “Sales Measures”.Dollars saw_2, “Sales Measures”.Units saw_3 FROM Paint ORDER BY saw_0, saw_1″;
   QueryResults results = xmlService.executeSQLQuery(query, XMLQueryOutputFormat.fromString(“SAWRowsetData”), new XMLQueryExecutionOptions(), m_sessionID);
   String xmlResult = results.getRowset();
   out.println(xmlResult);
   m_Session.logoff(m_sessionID);

  } catch (Exception e) {
   out.println(e);
   e.printStackTrace();

  } 

%>

5.  Now, open Excel and add a new XML map from the jsp url (make sure you refresh the data at least once from Excel. You can delete it after the refresh, to make sure you are getting the data from the server, but I had issues with excel 2003 maps not working unless I did this initial refresh inside excel). If you are running this on the obiee server and created an Xcelsius directory as did I in Tomcat, the XML Map location will be http://localhost:8080/xcelsius/obiee_xlf_data_conn.jsp

6.  Import the Excel file into excel, model as usual and add the xml map data connection

7.  Export your html/swf from Xceslius, and reference their location using an embedded content component in OBIEE Dashboards

And… voila. OBIEE Never looked better! You can download the sample files here

Posted in OBIEE, Xcelsius | Tagged , , , | 6 Comments

Delay on next post…

Posted on May 4, 2011 by Ron Keler

I have not been able to post any new blog entries for the last week and a half due to a family medical problem i am attending. I will post new content soon, stay tuned, I promise my next post will be very interesting…! Thanks -Ron

Posted in Uncategorized | 4 Comments

Why Isn’t Data Enrichment A Standard Practice In Every Data Warehousing Project?

Posted on April 27, 2011 by Ron Keler

Data enrichment is a terrific way to increase the value proposition of your data warehouse, and yet, it is implemented quite rarely. With data enrichment you can almost overnight provide your sales team with additional mountains of information about the demographics of prospects, provide finance with accurate client addresses and other contact info, provide marketing with competitive information about companies in related industries and help manufacturing by assigning standard attributes to parts used in manufacturing. So, why is Data enrichment not a standard phase for almost any data warehouse project?
Let’s start by understanding what data enrichment is. By data enrichment, I am referring to the practice of adding additional data attributes that do not exist within company internal databases, to data that does. There are many companies that provide such enrichment services in almost any industry, probably one of the most well-known ones is D&B. So, for example, you could send Dun and Bradstreet (D&B) your client list and get in return the complete address, phone and some other stats about the companies in the list.

That sounds terrific, right? The premise is simple and is loaded with ROI. If you have the money to invest in such a process, why wouldn’t you? Well, the answers are typically related to process, trust and being able to step “outside-of-the-box”.
Strange as it may sounds, data warehouses are quite intimate affairs. They involve millions, or billions or details about the way individuals go about conducting their business as part of their day-to-day work.  As such they are considered a core part of the business and opening up this internal, often highly pervasive and massive database to an external, foreign data source can be a difficult proposition for both business line and IT managers.
There are also technical challenges involved in such integration, such as the ability to sync the historical changes in the organic company data with the historical changes (if any are tracked) in the procured data. Sometimes, there is also a need to provide feedback on such procured data, and a feedback loop/mechanism must be developed.
And finally, it does require some degree of “out-of-the-box” thinking to integrate an external data feed used to enrich company data into your most internal and intimate database.
If you are able to overcome the procedural, trust and “out-of-the-box” challenges though, you may find yourself rewarded with a wealth of information your business users would find extremely useful and that you would not have been able to obtain otherwise.
 

Posted in BI At Large, Data Warehousing | Tagged , | Comments Off on Why Isn’t Data Enrichment A Standard Practice In Every Data Warehousing Project?

Abusing the date dimension: when does it make sense to NOT use it

Posted on April 20, 2011 by Ron Keler

The date dimension is a dimensional model staple. You will not find a data mart, data warehouse or any kind of a reporting database that does not have one form or another of a date dimension. The benefits of using a date dimension are numerous, and it’s a great tool for report developers, who can simply reference columns in the date dimension rather than look to write complex sql functions to perform date gymnastics. However, when it comes to the semantic layer, I suggest being much more judicious when implementing the date dimension. Overusing aliases in your semantic layer to represent many different dates, or using contextual separation of dates, can add significant complexity to your semantic model and reduce its maintainability dramatically. And the value of using the date dimension with many of the dates tracked in your fact may not be significant.

So, when does it make sense to NOT use your date dimension? The first condition that should exist for you to choose not to use your date dimension is the use of “smart keys” for your dates. Smart keys were very popular 30 years ago, and were used to embed business rules and logic into a numbering system that represented the rows values. This sounded like a great idea, until these business rules began changing. As the rules embedded in the smart keys started getting more dynamic, database designers faced a whole slew of new problems and headaches due to the need to make changes in existing keys. To avoid these problems, data warehouses use surrogate keys (or synthetic keys). These are “dumb” serial numbers that are used to encapsulate the “business keys” (legacy keys) and make handling of changes much easier. This is an almost bullet proof technique and you cannot go wrong with implementing surrogate keys in any dimension, except, the date dimension. This is the only exception that merits a diversion. Dates are not “slowly changing dimensions”, and most implementations of a date dimension are implemented at the grain of a day, and do not require multiple rows per day. This kind of implementation allows the use of “smart” date keys. Keys that follow the convention yyyymmdd (for example 20110419) are numeric, easy to produce and maintain, and very importantly, will allow report developers to deduct the date, without using the date dimension directly.

And this leads me to the second condition under which date dimensions should be considered not applicable in the semantic model: date proliferation. If your semantic model looks like this, before accounting for anything else but dates, you may want to reconsider your use of the date dimension.

Many subject areas require the use of many tens or dates per fact. Especially with so called “factless facts”, or facts associated with domains that are more process oriented then measurement oriented, it’s very common to find dates upon dates that can be used to analyze the process. Think about your sales process and how it is captured in your CRM: your lead generation date, your initial call to the lead date, your second call to the lead date, your first meeting date, your first quote date, your revised quote date, your po date, your booking date, your revenue recognition date, and so on and so forth. Or imagine a ticketing system used to track tickets escalation in a highly regulated environment where each step of the escalation process goes through several approvals, all happening at different dates. So, while it is easy to imagine a fact of this sort with 50-60 dates, would you really want, or need to create these many joins and aliases in your semantic model? Would that be productive and add value to your business users? Do they find it useful to know the date, week number in month, week number in year, month number, month name, year, quarter number, quarter name, weekday name, etc etc for each one of these dates? Or would you simply want to know the actual date? The common answer is that there are probably a handful to a dozen of dates that can benefit from a join to the date dimension, and for the rest, all that is needed is simply the ability to report the date. So, if your date dimension has a smart key, as described above, it would be simple to convert it to a date with a simple sql function. If more is needed, it would also be simple to join it back to a date dimension alias and add any kind of date permutation on it. This approach is also known as eating the pie AND having it…

Posted in BI At Large, Data Warehousing | Tagged , , , | 3 Comments

Connect an Xcelsius dashboard to a public RSS feed using simple jsp proxy – work around cross domain problem

Posted on April 11, 2011 by Ron Keler

Xcelsius has several data connectivity facilities that make it relatively easy for dashboard developers to connect to live data. Web Services and RSS feeds can be used via XML maps, or directly. However, the practical limitation anyone who ever tried using those features undoubtedly runs into, is the fact that these sources will typically reside on a different server, and hence will cause a cross-domain error on the swf file deployed from Xcelsius.
To address this problem, I used a simple .jsp based proxy. Since Xcelsius developers are usually working close to a BusinessObjects implementation, and that in turn, is typically deployed on a J2EE server (usually Tomcat), a .jsp solution seemed appropriate and applicable to many folks. All this proxy does is connect to the RSS (can be adjusted to connect to a Web Service) , and then re-stream it. That way, rather than connecting the swf to some remote server, it can be connected to a local file, hence eliminating the cross-domain problem. In the XLF connection, rather than using the actual URL, I am using the proxy URL (http://localhost:8080/proxy.jsp?proxyUrl=http://www.guardian.co.uk/news/series/24hoursinpictures/rss – be sure to replace localhost with your server name).
To illustrate this solution, I created an Xcelsius based swf that goes out to the Guardian UK 24 Hours In Pictures RSS feed (http://www.guardian.co.uk/news/series/24hoursinpictures/rss), and displays the images, and other info about them.
You can find the XLF, proxy.jsp file and related html/swf here. After downloading, extract the .zip package, and place the jsp.proxy, .html and .swf file in your web app root (for Tomcat this is in TomcatHomeDirectorywebappsROOT). You could then open a web browser on the server and load http://localhost(:8080typically)/PicOfTheDay.html
I would love to hear about others use of this concept.

Posted in BI At Large, Xcelsius | Tagged , , , | 2 Comments

Google Public Data Explorer

Posted on April 6, 2011 by Ron Keler

These are exciting and busy times for Business Intelligence at large, and data visualization in particular. It’s hard to keep up with all the innovations in the field. From SAP Xcelsius and Explorer, Microsoft Silverlight, QlikView, mobile players like Roambi and many many others. And, well, Google. I reviewed the Google Charts in a prior blog, but recently also ran into the Google Public Data Explorer labs project.

This is a fascinating concept. Google create an open platform designed to load and explore data. I wanted to share some personal observations about this platform, which I found worth highlighting:

  1. It’s open. You can upload any data set (once you understand how to package the data and describe it using the DSPL – more on that in a bit) and presto, you get a free data visualization and mining application.
  2. It makes an attempt at standardization. Google labs engineers faced a difficult problem: how to let developers describe their data in a generic manner. Every BI vendor today solved this problem with proprietary tools and technologies: SAP (BusinessObjects) with Universes, Oracle (OBIEE) with the Admin Tool, IBM (Cognos) with the Framework manager and so on and so forth. Google’s approach is XML based. Google’s Dataset Publishing Language (DSPL) is an attempt at standardizing the way datasets can be described. It borrows from the dimensional modeling (dimensions, metrics) as well as from more abstract notions (topics, concepts, slices).
  3. It uses flash. The data visualization engine itself is flash based, which is, to say the least, strange. Flash is not an open technology, and since Apple won’t allow it to run on its iOS devices, many vendors are looking to stir clear from it to assure their solutions will work on iPads, iPhones, and such.

Regardless of all this, it is still a very worthy development, and I encourage you to try it out for yourself. It took me about an hour to parse out for myself the DSPL XML structure, and produce a sample set of data files that loaded into the tool. That’s not bad, and reflects well on the simplicity of the design and the well documented tutorials.

If you like to see my example, you can go here. You can mine the data, change visualization (Bar, Line, Bubble and Map charts), run the data across time, apply filter, pivot and more.

Posted in BI At Large | Tagged , , | 2 Comments

Why Data Modeling Fall Sideways?

Posted on March 31, 2011 by Ron Keler

One of the most surprising aspects I have come to expect during the years of data integration and warehousing consulting is how little data modeling is actually being practiced in this field. While it is hard to find a database technologist who would argue that data modeling is “bad” or “wrong”, it is simply not practiced. When I ask for the model of the existing database, the usual response is in the form of a 4-5 years old ER diagram that is completely outdated (at best) accompanied by an embarrassed shrug. It is one of the most basic and fundamental things to do when approaching any data problem, and yet companies are always impressed by this and are surprised by the huge benefit and value the model brings to the development process. Don’t they know how important data modeling is? Of course they do. So why don’t they practice it?

I think there are many explanations to this puzzling question, and I would like to offer a few. As you would expect the answers are not related to technical competence, or lack of appreciation for the fundamentals of good development practice. It has more to do with the reality of corporations, and the pressures of IT operations.

Lack of time to model

The fact of the matter is that you do not have time NOT to model.

The fire alarm doesn’t stop ringing. The users are constantly demanding increased functionality and services, “threatening” to replace internal IT services with external “Cloud” services or outsourced solutions. You need to deliver results every two weeks. In that kind of an environment, where you can barely plan how your day will end, you don’t have time to model. Or so goes the argument. It is not always spelled out and conscious, but it is a big reason why modeling falls sideways. The “lack of time argument”. You understand how good modeling practices can save you time in the long run, and keep your development streamlined and your environment maintainable, but you simply don’t have the luxury of long time planning right now.

While it’s absolutely clear why modeling saves you time in the long term, it will also save you LOTS of time in the short term. Databases are not built in vacuum. They are used to house information and data that will need to be consumed by users through applications. And unless you have only one person who does all the work, a data model is the ONLY way your various team members and participants in your data driven application will be able to make progress together and IN PARALLEL.

The data model becomes the contract between the application and the data it represents. It shields and separates the various parties that are collaborating to realize the latest initiative, and allows them to make progress in parallel, without needing to wait for each portion of the work to be completed before they can proceed.

Lack of tools

This does not come up often, but I still run into this sometimes. Enterprise modeling tools are typically expensive and are complex to learn and use. Licensing restrictions prevent many people in the organization from using them and lack of functionality, or training, causes database administrators and developers to eventually stop using them.

Keeping up the model can be a lot of work, but it is the only way to keep everyone informed about what is going on in the database, and how it evolves and changes over time. When it’s time to work on a new initiative, everyone can go to one place and understand the state of the information.

And while you will need a tool to store and maintain your model, especially the physical one, the most important modeling tool you can ever have is your whiteboard.

Modeling ALWAYS starts on the whiteboard. There is simply no better tool for collaborative data modeling then standing in front of a whiteboard with a dry erase marker, and a group of other talented database developers all standing or sitting around, shooting down approaches or embracing new ideas, erasing, tweaking and refining until they crystalize to a reality everyone can understand.

Lack of discipline

Modeling takes a lot of practice, and a lot of discipline. It is a bit counter instinctive for a super star developer. Their instinct, when presented with a technical challenge, is to “jump right in” and solve it in a blazing flash of genius. So the discipline to take time to see how this “fits the model” takes an extra effort upfront, which is very easily dismissed.

If you can embrace the ideas that modeling can help speed up your development cycles and can be done using simple tools like a whiteboard, you will be that much closer also accepting the fact you will need to nurture a culture that encourages discipline around modeling. Before you know it you will be moving out of a reactionary fire drill mode, and into a proactive and thoughtful mode, where you can help deliver information that matters to your organization most strategic needs.

Posted in BI At Large, Data Warehousing, Universe Design | Tagged , , | Comments Off on Why Data Modeling Fall Sideways?

Data Visualization Lesson from 6 Years Old

Posted on March 27, 2011 by Ron Keler

“Daddy, you have a lot of audio, not a lot of photos, a little more apps then photos, and you have free space more than the photos and apps, but not as much as the audio”. That is what my 6 years old son told me this morning. I was installing the new iOS upgrade on my iPhone and it was plugged into the computer, iTunes open on the big screen. He snuck behind me, and after looking at the screen for a few seconds, blurted out these specs. “It’s good that they have these colors at the bottom”, he added.
There you have it, I thought to myself. A data visualization lesson from a 6 year old. If Apple would have chosen to display my iPhone capacity like the table below I doubt my son would have been able to articulate what was being presented on the screen.

I often run into IT folks, database developers, engineers, and even some business users who question the need to visualize their data and prefer “raw numbers”. ”Just get me the data in table format, and I will do the rest. Who needs fancy charts or graphs, they just distract from the actual analysis and make it harder to understand the information”, is how their argument goes.
Well, I could probably walk my son through the table view and after several minutes of reasoning through the numbers in it, thinking about which is bigger and which is smaller, got to the same conclusion. But the ability to articulate the situation in a blink of an eye, is something that can only be accomplished with a picture. And it’s not just about speed, when the information is presented in a more appealing and compelling manner, the chances are it will be better adopted, used and actually serve its purpose: guide decisions and help improve processes.
So while I absolutely believe that there is room for “raw number” tables on reports and dashboards, their placement and functionality should be carefully considered. More often than not, I would suggest them to play a secondary role, acting in support of the stars of the show, the charts, graphs and other data visualizations.

Posted in BI At Large | Tagged , , , , | Comments Off on Data Visualization Lesson from 6 Years Old

The dilemma of the expanding and shrinking universe

Posted on March 24, 2011 by Ron Keler

One of the most fundamental questions you will need to answer as you begin designing your meta data strategy can be borrowed from astrophysics. In much the same way that our cosmos adhere to great expending forces that stem from the big bang and keep stretching and expanding it, it also adheres to gravity and forces that ultimately will cause it to shrink back again. Just like your meta data layer. Your users are putting requirements that call for combining information across any conceivable domain, related or not, and on the other hand require concise, simple and intuitive reporting capabilities. How do you reconcile these conflicting trends?

When using BusinessObjects universes as your semantic or metadata layer, you have a variety of tools to help you cope with this fundamental difficulty, contexts, aliasing, derived universes and other techniques that can be employed to manage data complexities. However, these techniques require know-how and decrease your universes maintainability.

So, which way should you go in your universe design: reach for the mega universe that engulfs everything your users want to know about in a single universe, or carve out the information into smaller mini universes?

Consider the first the picture below. It represents a “mega universe” that spans many different subject areas, contains 15 contexts and over 1000 objects scattered in deeply nested subclasses.

Is this a universe you can put in front of users? Will there ever be more than selected few analysts with very good understanding of the underlying database who will be able to use this? If the principal guiding you toward a mega universe was trying to encapsulate database complexity from the users and handle it in the metadata layer, you might want to reconsider it.

On the other hand, the simple and small universe depicted in the following image represents challenges as well. While it will be much easier and simpler to build, test and maintain, it will surely leave your users wanting to create reports that combine the information in this universe with information in others like it. So now, you have effectively “passed the buck” to your users in terms of the burden and complexity for combing multiple subject areas. This also may not go down well.

The fact of the matter is that there is really no easy solution to this problem of the mega vs mini universes. The decisions around how much information to include in a single universe must be very pragmatic and tailored to address the particulars of the specific situation and requirement you are trying to address.

You must consider the comfort level of your technical resources with a highly complex universe, and your ability to truly test it, with all the possible combinations of data it entails at a satisfactory level, and weigh that in lieu of your users’ community requirements, and your users’ sophistication and technical savvy levels. You will likely find that in most cases, resisting the initial urge to expand, and focusing on relatively small universes that address one or very few subject areas, and do so extremely well, will become a lot more manageable and effective than a single universe designed to handle your entire data landscape.

 

Posted in BI At Large, Universe Design | Tagged , , , , | Comments Off on The dilemma of the expanding and shrinking universe

How I got flash to run on my iPad and iPhone

Posted on March 20, 2011 by Ron Keler

Like so many others, I really like my iPhone and my iPad, but I get very annoyed when I try to browse to a web site that is flash based, or try to even pull up some of my blog posts that embed Xcelsius flash demonstrations. Well, I finally figured out a way to get swf to run on my iPhone and my iPad. It’s a very simple solution, that kind of bypasses the problem, and for the simple purposes of demoing dashboards on iOS or browsing with the iPad without having to get off the sofa to get the laptop each time you run into flash, this works very well. The idea is so simple: using RDP to remote desktop into a PC computer, and then using the web browser on that computer!
There are a number of free RDP (Remote Desktop) apps available for both the iPhone and iPad in the app store. I used pretty much the first RDP client that seemed good and was free I ran into. It’s called iRdesktop (http://www.irdesktop.com/). It was dead simple to setup. Just entered my host name, username and password, and I got a window into my PC. I pulled up IE, and began browsing.
First, I pulled a couple of dashboards on my iPhone. The screen is a lot smaller, so I certainly see a need to design the pages for such a small screen, but the flash itself came up with no problems and worked completely seamless. Here are a couple of screenshots from my iPhone:

Then, I pulled my blog with a couple of postings that have flash embedded in them in my iPad. They came up with no problems. I was able to activate the mouse, interact with menus and buttons on the dashboards, and the whole experience was completely seamless and blazing fast. Here are a couple of screen shots from the iPad:

 While I certainly wouldn’t claim this to be a solution to the problem of running flash on iOS, this is a very simple and practical work around that works on a small scale, and can be used in presentations, demos and small groups situations.

Posted in BI At Large, Xcelsius | Tagged , , , , , , | Comments Off on How I got flash to run on my iPad and iPhone