BI on a napkin

Posted on February 10, 2011 by Ron Keler

One of the surest ways, tried and true, to achieve BI happiness, is to do it in a bar. Especially with a nice drink, in the evening, and a good companion. Don’t believe me? Just try it.

Well, that is exactly what I was doing the other night. I was in a bar with a friend, and after a couple of drinks, we began discussing, what else, BI. I was trying to explain what is the BI space, what are the main components of the architecture of a typical BI implementation, and came up with this napkin drawing (disclaimer: this is not the actual napkin used in the bar, the original didn’t survive the night).

 

On the left side, you have the various source systems. These are the operational software packages your business runs on. Your ERP, CRM, e-Commence, the numerous Excel spreadsheet you could not close your books without, etc. These all get loaded up into the Data Warehouse. The Data warehouse can be called a reporting database, an mart, an ODS, or many other things, the concept is typically the same, you rather do your reporting off a database that is separate from your operation one, so it does not impact performance, and you can combine data from different systems as well as organize it so its suited for reporting. The process of moving the data from the source systems and reorganizing it is called ETL: Extract Transform and Load. On top of your DW sits your BI vendor meta data layer that describes the database and allows isolation of reports from the physical DB layer. All major BI vendors have such a layer and there are many benefits to this architecture, not only shielding your reports from changing databases, as well as ensuring consistency and allowing ad-hoc reporting capabilities. Finally, out of your meta data layer, you get your plethora of reporting capabilities, reports, dashboards, BI apps, web services, etc, etc, etc… And that is it in a nut shell. You can add a couple more circles for data governance and process encompassing the whole thing, and you pretty much got an enterprise BI system in place.

Of course each and every single component on this diagram is a complete eco systems with a huge variety of software/hardware options to choose from, complex implementations and so forth, but I’ll leave that for another napkin…

Posted in BI At Large | Tagged , , , , , , | Comments Off on BI on a napkin

Roll your BI Project on two tracks

Posted on February 5, 2011 by Ron Keler

One of the most challenging aspects of any BI project lies in the tension between the “behind the scenes” data work, and the “high visibility” BI application. Everyone will see the new marketing and sales dashboard you are tasked to produce, but no one will have a clue about the mammoth data integration effort it took to consolidate opportunities from 12 different systems across the globe to produce a meaningful “company pipeline”. To make matters worse, office productivity software, and popular websites like amazon, google, or financial web sites turned your users to a very sophisticated bunch with extreme expectations. “Why would it take you two months to create this calculation? I can do it in excel in 2 seconds..”, is a typical example that illustrates the lack of understanding and appreciation for the complexity of modern data integration work. Faced with this reality, as you start planning your project, you may like to stack your work in a linear fashion: first, focus on the data exploration and integration work, and then, when you actually have the data, report back on what it can support and produce the visuals, reports, dashboards etc, to illustrate these findings. If this sounds like a good idea, well, it’s because it generally is, but unfortunately, this approach is rarely possible in light of the harsh reality of your business. A normal complexity data integration effort will take 8-12 weeks to complete, between the exploration phase, the modeling phase, the ETL phase, adding robustness and error handling and testing. During that time, you will not be able to show your users any progress, with this approach. All this hard work by your data architects, data modelers, ETL developers is completely obscured from your business users. It’s way too technical and requires a very high level of specialization to appreciate and your users, for the most part, will not be able to follow the details. Especially in these days of Agile methodologies and iterative approaches, it’s very hard to explain three months of work and budgets burnt, with nothing to show. So, what can you do? Well, you will have to work in parallel, on both tracks.

 Your reporting application and data work will have to progress in parallel, rather than serially. But wait a minute, you will ask, how can I design reports and dashboard without the data to put into them? The key to doing that is your reporting data model. As long as you can agree at a relatively early phase of the project on your application requirement, functionally, navigationally, overall data scope, drillability and graphical representation, you will be able to produce a data model that will be able to support those requirements in principal. Once you have done that, you can use assumptions and deductions to fill this data model with “dummy” or “test” data, loaded from spreadsheets, manually entered, or inserted from well understood sources. This will allow your visual track to start making progress without needing to wait for all the data work to be completed. Sure, there will inevitably be some rework on both fronts due to misunderstandings, changes in requirements as the project progresses and some wrong assumptions. But, for the most part, if your model is solid and well thought out, you will be looking at MINIMAL changes or rework and MAXIMAL utilization of time. Now, instead of producing the report in 6 months (3 to build the data, 3 to build the reporting application), you can deliver your reporting application in about half the time.

Posted in BI At Large | Tagged , | Comments Off on Roll your BI Project on two tracks

Navigate your BI product to success by considering… Navigation!

Posted on February 1, 2011 by Ron Keler

Whether you are working on a simple report, several reports, a dashboard or a complex BI application, one of the first things you need to consider as part of your design is navigation. The navigation describes the usage pattern, or usage scenarios of your BI application. Call it story boarding, wire framing, use case or map, in order to produce a coherent and high quality product, you will need to understand how users will make their way through your product. It is especially important to consider navigation, since you will most likely be providing some level of data filtering in your BI product, which will add additional complexity to the navigation use cases. Your navigation map may be as simple as:

  1. Landing page: prompt for region selection
  2. Regions sales report

Or as complex as this:

 

But if you get it all laid out and understood, you will have no problems producing intuitive quality products that users “get” and enjoy using.

 

Download XLF here.

Posted in BI At Large, Xcelsius | Tagged , , , | Comments Off on Navigate your BI product to success by considering… Navigation!

Designing BusinessObjects reports for mobile devices

Posted on January 31, 2011 by Ron Keler

Mobile devices are moving very quickly to replace computers and laptops as the new productivity tools for execs and employees. We all use our iPhones and BlackBerrys constantly. We check email, play with apps, chat and talk, whether we’re in the office, or outside. We are constantly connected. The BI vendors have been slow to respond to this trend, but are gaining traction and stepping up. BusinessObjects faces a particularly delicate problem as its main dashboarding platform (Xcelsius) is Flash based and will not run on Apple iPhone and iPad devices.

To respond, BusinessObjects released an iPhone/iPad app that is based on its Explorer product. It’s a great and intuitive way to explore data. However, it lacks the ability to configure navigation, or control high level of refinement, as you would expect from any dashboard or BI application, mobile or not.

I have spent some time exploring alternative options to the Explorer web app, and none proved to be very easy to use and effective. Web Intelligence, the reporting flagship product for BusinessObjects has many interactive features that would have made developing mobile applications terrific, but many don’t work on the iPhone platform. For example, using a table block as an input control to filter another report block, did not work on iPhone. After much experimentation, I found the best approach for designing your own reports based mobile application to be using Crystal Reports. Crystal Reports provide a nice drill down capability “out-of-the-box” that works on iPhone. The charts are image based, so will display on any mobile device, Apple’s included. And using report part navigation along with group down drills, you can create some very nifty reporting capabilities, “on the go…”

 

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

Xcelsius Step Chart – Combine charts to create a new kind of chart

Posted on January 30, 2011 by Ron Keler

While BusinessObjects Xcelsius has many chart components that provide very robust variety “out-of-the-box”, more often than you like, users ask for a chart that does not exist as a preconfigured component. One of the ways to answer such needs is combining multiple charts by leveraging component properties such as transparency, the ability to sync (bind) scale and values, and the the size of position of components, relative to each other (in absolute, not relative terms. The lack of relative positioning in Xcelsius is a bit miss). Here is an example of a “Step chart” that was created to illustrate movements from opening to closing balance across several “steps”.

Download XLF Here.

Posted in Xcelsius | Comments Off on Xcelsius Step Chart – Combine charts to create a new kind of chart

Documentation – the difference between a great consultant, and a great consultant who cares…

Posted on January 29, 2011 by Ron Keler

A great consultant comes in, completes an impossible task with flying colors, wins the day for their sponsor, and leaves. A great consultant, who cares, also leaves behind documentation.

Documentation is most definitely the step child of any IT project. Unfortunately, most developers are either not thoughtful enough about their work to leave behind documentation, or simply don’t like it. Junior developers will document their work, but at a very rudimentary level, which basically equates to going through their code/product and unwinding it. Senior developers are too busy developing interesting “stuff” to be bothered with actually documenting it at a useful level of detail.

Well, as a consultant, I find it highly rewarding to see how much clients appreciate the fact that I always look to leave proper documentation behind, and also take the time to go through it with them. It’s a very simple actually: by leaving behind documentation that can actually help support the work I leave behind, I show that I care. I care about the client, and their long term success. I care enough to put together a deliverable that can serve to explain why I’ve done the things I’ve done (when there’s no doubt there were other ways to do them), how those things actually work, what to do to keep them up and how to extend them.

The same way you wouldn’t want to buy a consumer product with no documentation that explains how to use it, especially if it has any level of complexity bigger than a toothpick (and even those come with instructions on the box), why on earth so many IT, and especially BI, projects end with no proper documentation?

Well, a lot of it has to do with misunderstanding, or uninformed judgment. BI work is often perceived as “creating a report”. Well, modern reports are much closer to applications, then to letter size paper printed reports. They almost always include interactivity and functionality (from prompts to drill downs and navigation), they often rely on complex data that is sourced from multiple sources, they contains code (even reporting application that are GUI driven completely generate code to interact with a data source at some level) as well as visual designs, they incorporate business logic and are expected to address multiple (in some cases hundreds!) or use cases. When you lay all this out, the level of complexity is obvious and obviously merits a diligent effort of documentation, right? Well, the typical response is unfortunately still: “what do you mean you need 4 weeks to document a report???!!!”

Since BI is considered more of an IT discipline then actual software development, it is perceived as more utilitarian and commoditized then the R&D work that any perceives is involved in developing new software. However, BI work is often a lot closer to software development then wiring CAT 5 cables or setting up wireless access points. It always follows some sort of SDLC, includes large cross functional teams (business users, infrastructure, database developers, reports/applications developers…), and a high degree of creativity. Especially since most BI projects start out with high level vague requirements and evolve as they progress.

So, next time you work on a BI project, be sure to set aside some time to properly document your work. If for no other reason, your clients (internal or external) will love you for that, and will certainly take that into consideration when thinking about working with you again.

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

Control the size of your Xcelsius dashboard in InfoView

Posted on January 28, 2011 by Ron Keler

If you ever tried to publish an SAP BusinessObjects Xcelsius dashboard to InfoView by using the “Export” option to “Enterprise”, you surely ran into a sizing problem when viewing the dashboard. As the dashboard designer, you pay great attention to the dashboard canvas size and try to maximize each and every pixel on the canvas for optimized viewing and information delivery. However, when you go to open the dashboard you exported from to InfoView from within InfoView, you will most probably be surprised to see fonts reduced to sizes that make words illegible, components overlapping and other display problems. That is because InfoView opens the swf file you exported to the BusinessObjects repository without specifying the height and width attributes you created for your canvas. The swf scales to the user browser window size. It will likely be too small if opened inside the InfoView portal, or too big if opened in a separate window, and in either case, it will certainly not look exactly like you want it. So, what can you do? One work around you can use is provide you users a hyperlink objects inside of InfoView, rather than the actual swf object, as a way to open the dashboard. The hyperlink can direct the user request to a web page on the local BO server that will embed your dashboard with the proper dimensions, as well as handle the user authentication and embedding the user session token into your dashboard, in case you are connecting to a BusinessObjects universe via QaaWS or LO. You can follow the example below for the solution implementation details, this example is based on a BO system deployed using the standard Tomcat app server on windows.

  1. Create a directory inside the InfoViewApp web app directory to house the files you will create to redirect the user request and open your dashboard file. For example create a directory called dashboards in the InfoViewApp directory, like so: Tomcat55webappsInfoViewAppdashboards
  2. In this directory, you will host three files:
    1. an html file that contain a simple javascript command to open a new browser window. This is the file you will create a hyperlink to in InfoView. Its purpose is to guarantee that the dashboard will open in a new window, regardless of the user viewing preferences in InfoView.
    2. a .jsp file that will “harvest” the user session token, pass it to the Xcelsius swf file, and open the swf file using the width and height specifications you used in your canvas
    3. the actual Xcelsius dashboard swf file
  3. Finally, create a hyperlink in InfoView to the html file that will “popup” the dashboard in the desired size. You can use a relative url in your hyperlink (for example: ../../InfoViewApp/dashboards/pop_dashboard.html) which you can migrate through the import wizard without needing to change a host name on the server you will migrate this link to.

You can download an example, here. In the example, I also added the javascript to resize the dashboard, so not only it will always open in the correct size, users with larger screens can resize it if they wish (as described in this post). To use the example, extract the .zip and place the files in the directory you create in the InfoViewApp folder in step 1.

You can also download an example for adding a resize function to your dashboard here.

Posted in Xcelsius | Tagged , , , | 8 Comments

Some keys for a successful BI project

Posted on January 26, 2011 by Ron Keler

As you set to start working on a new BI project, you will need to address many technical issues sooner or later. Picking technologies and vendors, assembling teams, and working across the corporate silos to bring together information that does not come together under regular circumstances.. Well, before you get deep into the weeds of selecting ETL technologies or dimensional modeling, I would like to suggest you must consider whether the following conditions exist for your BI project to succeed:

Executive sponsorship and direction – “it sounds great, but honestly, I got to run to my next meeting”

Real commitment to process improvement through better information and analysis is a very difficult to find. This is a lot harder than it sounds…  Almost any manager will tell you these are very important goals, and they will be happy to support them, but few can sustain a long term commitment to such goals in lieu of the daily grind of operational issues. Fires emerge everyday across the company, clients, investors, vendors, internal politics and so on and so forth, are all looking for immediate attention to their current burning issue. Production, manufacturing, supply, logistics, these are all aspects of your company that contribute directly to your bottom line. When you put BI in competition with shipping products, there’s no question who will win. Put on top of this a complex domain that sprawls across departments, technical and business resources and is not cheap to boot, and you start thinking it’s surprising any manager in their right mind would even want to hear about BI, right? Well, obviously not.  But being able to look above the “problem of the day” to get real understanding of the business is the single most important thing you will need management to embrace to be successful with a BI project.

Organizational culture that can accept change – “the dashboard is stunning, but I need to download it to excel to finish my close process”

Business Intelligence content is typically an agent of change. Providing transparency into information that is typically kept away from most employees (and even managers), allowing analysis, historical trending, and the ability to make informed observations and deductions about the efficiency of various processes, can be very unpopular with those who prefer to keep things as they are, preferring their “job security” over improvements. If you don’t think the audience of your BI project is willing to accept what it may reveal, don’t bother getting started. If no one wants to hear about it, it doesn’t matter how impressive your data warehouse, report or dashboard will be, they will simply not use them. Which brings us back to the first point, you will need someone at the top who can MAKE people change, even if they don’t like to…

Content driven methodology – “we need to consolidate client data from 123 source systems to deliver accurate reports, we should have that by mid next year”

Your Business Intelligence project or initiative must tell a story. The story needs to be compelling and exciting. It needs to be a game changing story for your company, allowing people to invent new ways of doing their work, enabling them to dream up and exercise opportunities for improving processes and work flows. These stories are your BI content, and your execution, or methodology has to evolve around telling these stories, delivering this content. This focus can get lost very easily when faced with the enormity of the data integration or enterprise data warehousing efforts that will be required to produce the stories. But don’t lose sight of the end result. The warehouse is just a place to store things. It’s the insight that is stored in it that people are looking for, and that insight has to remain the focus of your initiative. If you spend three years building your warehouse without delivering actionable and relevant insight, or stories, not only you will likely find yourself looking for a new job, you will also likely learn that the warehouse you built is no longer relevant. Delivering a new BI capability every three months is not only doable, but it is the only way to succeed with a BI initiative.

Posted in BI At Large | Tagged , , | Comments Off on Some keys for a successful BI project

Google Charts – the elephant has entered the room..

Posted on January 25, 2011 by Ron Keler

Google chart example

As a developer I always had a knack for open source. So, when I started working with Business Intelligence I naturally began looking for opportunities to leverage open source software. Of course, MySQL was a great RDBMS choice (i used it for several years in very large DM implementations, with terrific results, maybe more on this in some future blog posts..). I began searching for BI tools that are open source. I ran into a few, Pentaho, Jasper, Birt all came up at some point or another. I got excited about Pentaho earlier on, but lately I think the company has resolved to switch to a more commercial direction, and I found it very difficult to continue using its non-enterprise commercial software.

Well, enter Google.

Of course, whenever Google enters a market, you know all the existing players in the field start shaking in their boots, and for good reasons. Google is innovative and produces quality products that other companies can only envy. Its Google Charts offering will surely rock the BI world sooner rather than later.

Google charts offer an astonishing variety of charts that are completely customizable, can connect to live data, and are surely to continue evolve. And there’s more than charts: with tables, grids, text components and other great looking components, Google charts (http://code.google.com/apis/chart/) is moving surely into the dashboarding and data visualization space.

Stay tuned to this topic, I am planning to explore it at depth over the coming weeks, and I’m sure we will all hear a lot about Google charts in the near future.  

Charts API gallery (http://code.google.com/apis/chart/docs/gallery/chart_gall.html)

Interactive Charts Examples (http://code.google.com/apis/visualization/documentation/examples.html)

 
Posted in BI At Large | Tagged , , | Comments Off on Google Charts – the elephant has entered the room..

Handle semi aggregate measures in universes

Posted on January 25, 2011 by Ron Keler

While relatively simple data marts can be designed as true simple stars, more often than we like, our reporting data models contains snowflakes. Whatever you want to call these entities, they represent one basic problem for your reporting tools: Cartesian products. Let’s see why. In a simple snow flake model, your fact and dimension tables are joined in a “one way” relationship: one dimensional attributes can have zero or many facts. That’s it. This is a nice and clean design, and it makes aggregation of measures across facts a walk in the park. You never have to worry about cartesian products when you join facts and dimensional attributes, since your facts cannot be double counted by joining them to the dimension. Structurally, the joins look like this:

And they will yield data like this:

John Doe had two transactions, totaling $50 and in total the 4 transactions sum up nicely and with no issues to $85.

When you snow flake, you have to account for the possibility of creating a Cartesian product. Now, each dimensional row has a one-to-many relationship with an additional table, in effect creating a many-to-many relationship in your model. Now, your model looks like this:

And when you join addresses in with sales and clients, you get wrong numbers as you sum things up:

Since Mr. and Mrs. Doe moved from CA to NY, when we join in this information, our relational DB will make it look as if their 3 transactions are 6 and the $85 we earned is $145, because it’s double counting transactions. This may be good news for the sales team, but the auditors will give you a bit of a hard time.

As much as you will try to avoid this problem, complex models often require this kind of snow flaking for all sorts of good reasons. In this example, imagine if you are dealing with many millions of customers, with addresses as multipliers for each customer, I doubt you will be looking to add an “address” dimension in your sales fact. Slightly normalizing your model and adding the kind of relationship illustrated in the second model is perfectly reasonable.

Different tools have different solutions to this problem; BusinessObjects Universe meta-data layer provides you with the tools to address this problem in the form of aliases and contexts.

First, create your universe and setup your joins. You can try running the universe Check Integrity tool, but it will not detect any problem. The structure looks correct from a simplistic perspective, and as long as you are not trying to aggregate, you will not see problems.  But we know better…

To solve the problem, we can force business objects to issue two separate queries when users request to get data from all three tables, and maintain the correct aggregation level for the sale measures. Here’s how:

First, we create an alias table to the sales table. We will point the sales object to the alias and leave the rest of the table attributes as mapped to the base representation of it.

Next, create a join from the client table to the sales alias. Finally, create two contexts, one for the join path that includes the address table and the sales table, and the other for the client and sales alias only.

That’s it! You are now ready to handle a variety of query scenarios without needing to worry about your universe users accusing you of “cooking the books”… Here’s what will happen:

When your users select the client name and address objects, webi will issue a single query based on the join path/context specified as CLIENT_SALES. When they add the address zip code, webi will issue a SECOND SEPARATE QUERY to get that data and will stitch the result sets together in its micro-cube.

Posted in Universe Design | Tagged , , , , , , , , , , | Comments Off on Handle semi aggregate measures in universes