Archive for March, 2011

Why Data Modeling Fall Sideways?

Published by Ron Keler in BI At Large, Data Warehousing, Universe Design on March 31st, 2011 | No Comments »

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.

Tags: , ,

Data Visualization Lesson from 6 Years Old

Published by Ron Keler in BI At Large on March 27th, 2011 | No Comments »

“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.

Tags: , , , ,

The dilemma of the expanding and shrinking universe

Published by Ron Keler in BI At Large, Universe Design on March 24th, 2011 | No Comments »

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.

 

Tags: , , , ,

How I got flash to run on my iPad and iPhone

Published by Ron Keler in BI At Large, Xcelsius on March 20th, 2011 | No Comments »

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.

Tags: , , , , , ,

Defuse the tension between designers and technologists – educate on your BI tools capabilities upfront

Published by Ron Keler in BI At Large, Xcelsius on March 19th, 2011 | 7 Comments »

When working with designers, or users who are interested in designing their dashboards, you will undoubtedly run into tensions between what “should be” and what “can be”. While you can argue that with software there are really no limitations, and any requirements can be met given unlimited time and money, none of our projects enjoy that kind of a situation. We are always working within the constraints of time, budget, resources, etc, and the designs are bound to the possibilities of the technology that is being used. Your users and designers may want to incorporate charts that are not available in the technology of choice, develop complex navigational schemes that cannot be met, or want a very particular interaction driven by a certain type of fly-out, swirly, fade-in, color changing menu that you simply cannot produce.
If you are using Xcelsius as your dashboard technology, you can use the following file as a way to help mitigate this tension. This file lays out all the key components of Xcelsius, all the chart types, selectors gauges and sliders, and some of the other components, in a way that can be articulated and demonstrated to designers and business users. While each components, and certainly combinations of components are highly configurable through their properties sheets, in general, this type of an “upfront review” will give your users and designers a good understating of the capabilities of the tool and help set the stage for a productive collaboration around the tool capabilities.

Tags: , ,

Good to Great Dashboard

Published by Ron Keler in BI At Large on March 15th, 2011 | No Comments »

In his famous book “Good to Great”, Jim Collins describes his research into eleven great companies that presented consistent outstanding results, compared to other companies that were in seemingly similar situation, but failed to achieve sustainable greatness. Collins describes the framework that he found in his research to have been at the heart of the transformation from good, or in some cases not so good, companies, into great ones. This framework, as you would expect, transcends companies and businesses in general, and can be extended into any field where excellence can be pursued.

At the heart of the transformation from good to great, argues Collins, you will find not technology, or fads, or superstar power. At the heart of greatness, you will find great people, who expect greatness from themselves and their surroundings, and who have the discipline to execute on a core idea for as long as it takes to achieve greatness. He uses colorful terms such as hedgehog concepts and flywheels to bring his ideas to life.

Collins makes two points that really caught my attention in relation to BI:

  • Technology, his research argues, is never a sustainable change agent in the transformation from good to great. Technology, he found, can be an accelerating factor, but in essence, is merely a tool, used by disciplined people to achieve great results.
  • The good to great companies had systems that allowed them to “raise red flags” and present not just information, but information that cannot be ignored. Thus, allowing management and employees to “face the brutal facts” and take a real and honest look at the reality of their business, in order to make real and good decisions, directly related to their reality.

I find the combination of these two points to be the essence at the heart of the dashboarding concept. Great dashboards are used by disciplined managers who look for factual data about their business in order to make decisions that keep their flywheel spinning in the right direction. This discipline drives them to not just commission dashboards to be constructed and showcased, but to actually use them, as often as needed, to understand their reality. It is a tool to raise red flags, literally, around problems that arise, and present the brutal facts, as derived directly from the underlying data, unfiltered or arbitrated.

When I look at my car dashboard as I’m driving, I make sure my sight is not diverted from the road for more than a split second. In that split second, I look for one, two or three pieces of information:

  • how fast am I going (actually, how fast am I going in relation to the speeding limit, which our cars are not yet telling us),
  • I might also look to see if I am getting close to being out of gas,
  • and occasionally check if the engine is overheated.
  • I also expect my dashboard to flash some red flag if something wrong is happening.

Modern car dashboards also house entertainment centers, GPS navigation systems, A/C and heat controls and much more. But these, and all the other controls built into the car dashboard are add-ons, and always come after the fundamentals are in place. So, when trying to create a great dashboard, consider these factors:

  1. Do you have a disciplined audience that would use the dashboard? What can you do to help instill this discipline?
  2. Do you have the “fundamentals” covered: how fast (in relation to the “speed limit”), gas situation, heat situation and other red flagged enabled
  3. Once you cover the basics, what other controls do you need to add to make the dashboard more functional and attractive

For example, maybe your business speed should be measured in terms of your pipeline. You need to understand what is your current sales pipeline (in relation to your delivery capabilities.

Your gas situation might be measured in your cash flow. Are your billings and collections at pace with your bookings and revenues?

Maybe your business temperature can be measured by your churn. How many people join or exit your company, what are your recruiting levels, and how does attrition look like?

And perhaps you should configure your dashboard to flash red flags in case certain operational measurements such as key vendor costs, or certain types of expenses breach a certain threshold.

If you manage to bring the key concepts of your business to life using a dashboard, you will likely achieve a great dashboard. And once you created a great dashboard, you can continue to add controls, navigation systems, entertainment centers and a whole slew of tools to help manage and tweak different aspects of your business, as long as you do it in ways that do not subtract from your kep concept.

*Good To Great is a registered trademark of Jim Collins.

Tags: ,

Scale matters

Published by Ron Keler in BI At Large, Xcelsius on March 12th, 2011 | No Comments »

Charts have become an invaluable way to communicate information and articulate data. Charts help us not just convey data, but to also “tell a story”. With Charts, we can highlight certain points and help the people who need to act on the information we provide quickly understand it. Many considerations go into selecting appropriate charting types for your information. But in almost any conceivable chart type you plan to use, you will need to be thoughtful and plan how to address the scaling issue.

When I think about what can go wrong with chart scaling, I imagine a massively obese and happy guy standing smiling on a scale, next to a sad and stunned thin guy, who is also on a scale. Both of their scales only go up to 120lbs. You could argue this is great for the fat guy and bad for the skinny guy, but the fact is this is terrible for both of them, because they are both looking at a wrong reflection of their reality that will most certainly guide them to make the wrong choices.

One piece of advice you should consider up front and save yourself a lot of headaches down the road: if you plan to have any sort of charting capabilities in your BI application, report, dashboard, etc., make sure you understand how chart scales are handled. Most tools handle scaling automatically, and do not provide a lot of flexibility in terms of scale options. This would probably be ok most of the time, but I guarantee you will run into a data situation where these automatic settings would simply not be the right way to handle the picture. So, all you can do is try to be prepared.
Here are a few examples for some of the considerations you need to take into account when thinking about your charts scale.
Consider a relatively simple requirement: you are asked to create a report that will depict performance across regions for a national company. The company has presence in all US regions at this point, and is growing rapidly. It recently expanded into the Southwest. Performance is measured using two key measurements: revenues and margins.
You might start out with collecting the needed information and placing it in a table.

This is pretty good. It’s a clean simple table, it contains all the relevant numbers management would be looking for in a concise format. However, it does not tell a story. It’s difficult to analyze. It provides information, but it is not information management cannot ignore. You may decide at this point that a chart would do wonders to bring this information to life. 

This is the first example of how scale, when not handled, can turn into a major problem. This chart plots both the revenues and the margin across the same axis. Furthermore, the scale was hard coded, based on prior months results, to go from 50-150, so the Southwest region is simply not represented in the chart and the Southeast is cutoff. The picture depicted here is incorrect and misleading.

The next attempt is much improved. Instead of trying to plot revenues and margins on the same scale, you add another axis on the right, and you increase the scale limits to accommodate the data. The Southwest is not in the picture. Nice. However, something is still a miss. It is represented as a “blimp” compared to the other regions, and management looking at this depiction may not notice that the ratio of revenues to margins in this new region seems to be different than in other regions.

This final version plots revenues on the left vertical axis and margin percentages on the right vertical axis. In this picture, the newly added Southwestern region immediately draws attention. It dominates the margin picture. While revenues are still relatively low, something very interesting is happening down there, helping grow profits, and management will have a much easier time not being able to ignore this picture.
BusinessObjects Xcelsius charts have a good variety of scaling options, including primary and secondary axis, linear and logarithmic scales, and the ability to calculate min and max values for the Y (and Z) axis. This is a relatively understated feature of the product, but in fact, not many charting tools have this capability. It’s an invaluable tool and you should always keep an eye for the right choice of scale in your charts.

Controlling your charts scales will also allow you to come up with new charts types, overlay different types on top of each other to create interesting combinations. In a prior post I explored such concept of a Step Chart, made out of several synchronized scale charts. You will be able to handle a wide variety of data and filtering combinations that would otherwise result in misleading pictorial depictions.

Tags: , , ,

Broken windows theory applied to BI – clean up your reports so they don’t end up in the trash

Published by Ron Keler in BI At Large on March 9th, 2011 | No Comments »

The broken windows theory is a social sciences theory. It claims that maintaining the form of the environment prevents crime and social disorder. Malcolm Gladwell discusses this theory in his book The Tipping Point and examines how it was used to address the crime “epidemic” in New York in the mid 1980’s. This theory was put to the test in other areas of the country and succeeded every time. The concept is simple: if a burglar walks down a street and sees a broken window, he is more likely to enter through it. Or in a simpler form, if you walk down a dirty sidewalk, you are more likely to drop the dirty napkin you are left holding after finishing your hotdog, then holding on to it until you see a trash can.

 The idea is that the details of the environment affect how we perceive it and ultimately interact with it and with others that surround us within it.
While this makes perfect sense and passes most people “reality check”, others find it highly controversial since it implies that our behavior is more influenced by our external surrounding then our inner characteristics. While I understand this point of view, and like to think of myself as someone who generally “does not litter”, I have to be honest with myself and admit that walking down that filthy sidewalk, I might accidentally drop the dirty napkin…
What does all that have to do with BI?

The concept of attention to detail is what makes the difference between a crime ridden neighborhood, and a safe one, the same way that it does between a widely adopted and trusted report and one that is almost never used.
In a prior post, I wrote about  “The two prerequisites for any BI project: It has to be right. It has to be pretty”. Another way to phrase this same concept is: you must pay attention to the details of your BI project and assure that every tiny aspect of the design is addressed in order to be successful.
If your report does not reflect utmost thoughtfulness and care for the basic usability and aesthetic scenes of your users, It will not be adopted, and even if it is a marvel of technology, it will fail.
If the report labels have spelling mistakes, unnecessary complex formatting and coloring, bad use of screen/page realestate, etc, your users will get that “dirty sidewalk” feeling and the fate of the report is likely to be the same as that dirty napkin.

Tags: ,

Spin the bike wheel and track your performance

Published by Ron Keler in BI At Large, Xcelsius on March 7th, 2011 | No Comments »

While working out at the gym today, I thought up this little Xcelsius dashboard to track biking performance. While the hardware components needed are a bit out of reach for me right now, the illustration below would have to suffice for now… J.

Spin the front wheel with your mouse, and track your progress with a line chart on the top.

Tags:

Automatically Close Xcelsius Dashboard When User Is Idle

Published by Ron Keler in Xcelsius on March 4th, 2011 | No Comments »

A colleague approached me the other day with a requirement to “Logoff” users when they have not been active in an Xcelsius dashboard for a certain amount of time. I am sure there are many approaches and ways to accommodate this. The one that strike me first was to use an External Interface Connection (EIC) to capture all the user inputs in the dashboard, and use a counter to check how long they have not changed.

This approach is relatively simple to implement and requires little custom coding. It relies on Xcelsius EIC functionality, which is easy to configure and deploy, and works very well.

All I needed to do was concatenate all the user input cells in an Excel function in the Xcelsius model, point an EIC connection to this cell, and then write a quick timer java script function that resets each time the EIC changes. If the counter reaches 10 seconds (meaning the user has not interacted for 10 seconds), it hides the dashboard. Below is a screen shot of the example, you can also download it here

Keep clicking to keep the dashboard alive...

 

This is what happens when you stop clicking for more then 10 seconds...

Tags: , ,
© BI HAPPY
CyberChimps