Quiz January, 2012 – The Dreaded Time Zone Question

WARNING: This month’s quiz is harder than it seems at first!  Are you up to it?

We recently worked with a client that does business all around the world. They capture millions of transactions which they then bring into a dimensional data warehouse for analysis.

The problem is that this company, and their customers, need to analyze their data indexed to any time zone. So, for example, if a transaction takes place in Mumbai at 0200, it might be analyzed in the Mumbai time zone as 0200 or the New York time zone as 1530 or as any other time zone in the world.

Now the question; assuming a standard relational database, what is the best way to model this data to provide correct time and date results as well as adequate query response time?

HINT: In formulating your answer, consider the impact of date, not just time. 

 

Appfluent for Monitoring BI & DW Usage

What parts of your data warehouse are really important and which are never touched?  A few weeks ago I and some colleagues here at Dataspace saw a brief demo of Appfluent.  Affluent is a tool that monitors and reports on the use of database objects and, in some cases, BI tools.  It can answer important questions such as:

• Which tables are being used and which aren’t?
• Which queries are run most often?
• Which queries gobble up the most resources?
• etc.

Armed with information like this you can do things like:

• Eliminate unused tables
• Develop strategies to improve the performance of troublesome queries
• Determine if your warehouse is really being used at all

In the end, monitoring like this can help you improve and, perhaps, lower the cost of your data warehouse.  You might want to give it a try.

Not to Offend, But You Are Thinking All Wrong About BI (Probably)

What’s the first step in building a data warehouse or a business intelligence system?  Defining the key performance indicators (KPIs), right?  Wrong!  KPIs are certainly great to know and can definitely apply when you’re developing BI applications like management dashboards.  But, BI is NOT about management dashboards or data warehouses or query and reporting or QlikView or Business Objects…

Business intelligence is simply about capitalizing on data that was captured for other purposes.

I was struck by this thought over the Thanksgiving holiday when speaking to my brother in law.  He’s an executive with an up-and-coming, private-equity-financed distributor.  One night, his CEO had an epiphany

We know what people buy but we don’t know if what people buy is generally correlated with other things that they buy, or should buy.  If we knew this, we could encourage folks buying one thing to also purchase its natural accompaniment.

For those of us who have been in the BI business for long enough, this is a take on the classic ‘beer and diapers’ data mining example (click here for more on this BI ‘fable’).

This is certainly a use of BI, of capitalizing on data assets but it doesn’t really work in the way that folks expect BI to work:

• It doesn’t really start with a definition of KPIs
• While it may use a data mining tool, it doesn’t use dashboards or query tools or other, common, reporting tools
• It may use a data warehouse but it may, also, just use data assembled for a one-time analysis

In any case, when thinking about BI, the vast majority of companies need to stop thinking about BI.  BI is not the point.  Doing something extraordinary is the point and, if using data assets gets you to extraordinary, then BI is the mechanism.

Creating a Data Culture

You see companies that really thrive on their BI systems and you see companies that invest in BI and get no return. What’s the difference?  There’s probably a lot of things separating these companies but a big difference is culture.  Some corporate cultures just seem to emphasize data as a strategic differentiator.  Think of a company like Amazon.com.  They have a ton of data and they are always thinking of new things to do with that data - recommending items based on past purchases is just one example.

Others companies, on the other hand, are stuck in a mindset of data simply as a necessary byproduct of operations.  Once they’ve recorded the transaction they can pay their taxes, publish their financials and then be done.  They throw it into a data warehouse because that’s “what everybody does nowadays” but no one really uses it thereafter.

How does your company’s culture treat data: do you have data because it’s necessary or do you see data as an asset, constantly thinking of how you might capitalize on it?

Is It Time to Rethink the Concept of the Data Warehouse?

Infinite MIPS, Or How Your Hardware Vendor Let you Down

The Concept of Data Warehousing is Fundamentally Flawed

 

 

Ever step back, think about what you’re doing and then ask yourself, “Why?”  Ever ask it about the concept of data warehousing?  Let’s grow up and face a fact here – while it may be necessary, the concept of data warehousing is flawed.

Think about it.  We already have all the tasty data we need in our operational systems.  So, let’s chow down.  Hey, wait a minute… Y’know what would be great fun? Let’s design a completely new database called a data warehouse.  Then, let’s write programs to bring all of that data into our warehouse.  Along the way, let’s integrate it all so we get a business-view of it, rather than a source-specific view.  Hey, let’s also make sure it’s clean.  And, let’s make sure we’ve built all the infrastructure necessary to schedule jobs, trap errors, verify totals, …  Oh, and let’s ask our managers and shareholders to pay for all of this.

OK, is it just me or, when you step back, does this sound insane?

 

What Is the “Right Solution”?

So, what’s better?  Well, in a really good world, all your data and systems would be integrated from the start AND you’d be able to report directly from them.

In a perfect world you wouldn’t have to integrate the data from multiple systems, you would have only one system and it would support all of your operational and informational (i.e. reporting and analysis) needs.  So, what, or who, is keeping us from this perfect world?

 

Who’s The Villain?

(I’m sure that Dataspace employees and alumni know where I’m heading here)  Who’s letting us down?  Who’s making us spend all that extra money and do all that extra work just so we can actually use the data we capture?

Hardware vendors… J’accuse!

Hardware vendors?  Why?  Because they haven’t figured out how to master the laws of physics to give us infinite MIPS (there it is, Dataspace folks) – infinite computing power.

Think about it; if we had infinite computing power we’d put all of our data into a single, enormous integrated, normalized database.  That database would support both our operational and informational needs.  It would be complex but it could be made to look simple by layering views on top of it.  It would keep all the history we could ever want because, well, why not?  Best of all, response time to any query, no matter how complex, would be instantaneous.  Why?  Because we’d have infinite computing power.

So, in the end, data warehousing is really just a way to make up for the fact that hardware (and maybe communication) vendors, with as many PhDs as they have, just haven’t done that one little thing we need them to do – create a computer with infinite MIPS.  (C’mon guys, get your act together!)

 

Is Data Warehousing the Only Solution?

Given the fact that hardware providers are smart yet, clearly, clueless, we’ve come up with a ‘dirty’ solution to help us get at our data – we build a data warehouse.  We, in essence, do a lot of pre-processing on data because we don’t have the horsepower to do it when queries are issued.  Preprocessing like integrating, aggregating, and putting into user-friendly formats.

But, is this the only way to do the job?  Perhaps, given our lack of infinite MIPS, it is.  Still, the idea of a single, enterprise-wide database is enticing.  And, actually, there is a partial solution that, while not eliminating the need for informational data stores (i.e. data warehouses and data marts), minimizes the effort required to build them.  That partial solution is integrating operational systems or, in its more common form, master data management.

Integrating data before, or as, you build, a data warehouse has a number of advantages:

• It makes building the warehouse easier and cheaper.
• It ensures that, operationally, the whole organization is seeing the same picture (unlike one client who called us after different data definitions led to a multi-million dollar ordering mistake).
• It creates a logical view of the single database concept, bringing you closer to that true picture of one, integrated database underlying your entire company.
• It opens you up to reporting out of a new generation of BI tools, ones that integrate data but don’t require traditional data warehouses yet don’t stress your operational systems each time a query is run.  (more on this in a later posting)

 

Where Does This Leave Us?

So, data warehouses and data marts do accomplish a lot and, largely, are still necessary.  But, integrating data between your operational systems will save you headaches, lower your cost of warehousing and, in some cases, maybe even eliminate the need for a data warehouse.

Where to start?  Well, let’s leave that for a later post, too.

Any comments?  I’d love to see them.  Please submit them below.

– Ben

The Most Important 2% of Your Day

When you look at how Business Intelligence tools are marketed, you’d think that the secret to a wildly successful operation is to simply have executives sit at their desks looking at beautifully laid out dashboards, clicking here and there on charts, graphs, and gauges, drilling down, rolling up, and slicing and dicing their data. After all, that’s what the vendors of Business Intelligence systems portray in their marketing communications (and we’re guilty of using eye candy in our own materials, too).

I’m the CEO of a Business Intelligence consultancy. Organizing and presenting data in ways that enable business decisions is all that we’ve done for the 15 years since I founded Dataspace. Before that, I did it at MicroStrategy.  I’ve, even, co-authored three books on the topic.  Of all people, you might expect me to be sitting at my desk, slicing and dicing to my heart’s content. But you know what? I have a business to run. I’ve got to spend my time on attracting new clients, ensuring my team delivers flawlessly, and conduct a variety of back office functions from tracking payables and receivables to minimizing my overhead. And while we have implemented Business Intelligence tools at Dataspace to help me manage my operation, with the data collected, integrated and presented in a manner specific to my needs, I find I actually spend very little time using these systems. And typically for only two purposes: 1) to investigate a particular problem; 2) to check in once a week or so to see whether things are on track. I recently estimated how much time I spend using on these systems, and found I don’t spend more than an hour a week in them.

Do successful managers spend their days clicking around in BI systems?  I don’t think so.  Successful managers spend their time managing: making decisions and interacting with people – customers, employees, partners, suppliers, etc.  Well-designed BI systems quickly give managers a view of what’s going on – of what decisions they need to make and what conversations they need to have.  Well-designed BI systems get the answer across quickly and then get out of the way.

I’m proud that I use my system less than 2% of the time. After all, well-designed BI systems enable use of that 2% to identify the decisions that need to be made, and the conversations that need to be had with the other 98%.

Want to discuss?  Feel free to contact me at btaub@dataspace.com.– Ben

QlikView: Check it Out!

If you check out the message boards and recent Gartner Magic Quadrants you’ll see that QlikView is the next hot thing in business intelligence. Some of our clients are using the tool and they are ecstatic. Applications are created far faster than with traditional BI tools and executive users eat it up. I can’t think of many other BI implementations where executives are eager to get on the computer.

In a stodgy BI space that is plagued by incremental upgrades and poor customer support, QlikView is BI’s battle of Midway – a point of inflection that changes the game. If you haven’t seen the tool, I urge you to check it out at www.qlikview.com. Run the demos, they give a good idea of what it can do.

So, what’s so good about QlikView? Well, once you see the tool in action you realize that it’s not about producing the next generation of pretty green bar reports. It is about giving users easy tools for rapidly slicing through data. The difference between QlikView and traditional BI tools can be summed up as follows: Traditional BI tools are for people who need reports, QlikView is for people who need answers.

In future posts I’ll talk more about what’s so great about the tool, about how it crushes the traditional BI – DW development methodology, why most companies will still need a data warehouse and why, in the end, QlikView is complementary to, not a replacement for, current BI technologies.

Want more info before then? Drop me a line at btaub@dataspace.com.

Medicare Section 111

I’ve recently been working to help two clients comply with new Medicare reporting regulations. The regulation, MMSEA Section 111 – Medicare Secondary Payer Mandatory Reporting, requires anyone who pays for medical costs to report those payments to the federal government. The government will then compare the list of payees to their list of folks who receive Medicare payments. If there is an overlap, the government will look for refunds of the Medicare payments.

While you might think that this regulation only affects health insurers, the truth is that the scope is far larger. Our clients are not health insurers. One of them is a medical malpractice and workers compensation insurer. The other, believe it or not, is an auto manufacturer. The insurer is subject to this regulation because a portion of their settlements frequently covers the medical costs of the claimants. The auto manufacturer’s situation is more interesting. They are subject because, sometimes, when they pay out a product liability settlement, part of the amount paid is intended to cover the claimant’s medical costs.

Once you see how many companies are affected by this legislation you can get a sense of the total cost that its implementation will entail. Millions upon millions of dollars are being spent to make sure that affected companies are not subject to large penalties for noncompliance. In addition, it seems that many subject companies don’t yet realize that they are required to comply. For those who are subject, compliance is mandatory by Q1 of 2010 – so get to work!

How does Section 111 reporting relate to data warehousing? Well, in a couple of significant ways. First, complying with the regulation entails integrating data from a number of claims and payment systems into a single place from which it can be submitted to the government – just like a data warehouse integrates data from multiple sources. Second, if you already have a data warehouse, your compliance tasks may be much easier. One of our clients has a data warehouse in place that contains a lot of their claims data. We can, therefore, source this data from the warehouse and skip many of the integration and access issues we would otherwise encounter.

Are you under the gun for Section 111 compliance? Give me a call – we’ve got some cost effective ways to get you into compliance – quickly.

– Ben
734.761.5962 x503

Business Objects Insights from Dataspace – December 2008

TIP: USING THE USER RESPONSE AND REPLACE FUNCTIONS TO FORMAT YOUR REPORT

When you include prompts in your Web Intelligence report, you are making your report dynamic so that each time it is run, you can retrieve the data you need to see at that time without modifying the query. Did you know that you can use the User Response function to capture the value(s) you select and then include that in a report title so that you can easily know what data the report includes?

View the User Response and Replace Function example >>