BI Syndicate

[bisyndicate-blog]

What Came First? The Data Mart or the Data Warehouse?

So you decided that you can’t avoid Business Intelligence (BI) any longer. Starting from scratch may seem like a daunting task, but it is actually a very fortunate position. Starting from a blank slate can be much easier than trying to put past BI problems onto the right track. Very often people ask, “Do I build a Data Mart first or the Data Warehouse?” The answer is “Yes.” When you are just starting out, the most important consideration is keeping your eye on the big picture. Nobody can deliver the entire Enterprise Data Warehouse in one big project. If you are trying this approach, you are probably taking on a project that is too large and will likely suffer from continuously changing business requirements. Instead, it is important to try to deliver many, smaller projects that can demonstrate that they are delivering business value in a short period. These successes can ensure continued funding for future phases.

When mapping out your first BI project, step back and think big about what the end result should look like years from now. What major subject areas (or data marts) would you like to include in your Business Intelligence solution. Potential data marts could include:

- Finance

- Healthy & Safety or Sustainability

- Operations

- Marketing

- Human Resources

After considering the fully realized solution, ask yourself what data these subject areas (or data marts) have in common. At a minimum, they will share a common calendar. They could also share other common data and hierarchies such as:

- Geography (Locations, Offices, Sites, Regions)

- Organization (Business Units, Departments, Lines of Business)

- Account (Categories, Ledgers)

- Company (Subsidiary, Legal Entities)

- Customer (Client, Member)

- Projects (Programs, Initiatives, Campaign)

- Employee (Manager, Responsibility)

In addition to these dimensions that the subject areas might have in common, there may be a number of dimensions that don’t contain business data, but are used to describe the data (meta data). An example of this is a metrics-based data mart used to support a performance management function:

- Metric – A list of metrics or KPIs that the business calculates and monitors

- Scenario – The different types of metric data (Actual, Budget, Target, Forecast)

- Time Data View – The different ways that the data can be pre-aggregated (Quarter-to-date, Year-to-date, Previous Year, Last period)

Having decided on the data that could be shared across each data mart or subject area, you are now aware of the data marts will overlap. Understanding this, you will need to keep the data sources or future data marts in mind when you design and build these dimensions.

“When mapping out your first BI project, step back and think big about what the end result should look like years from now.”

The first data mart will need to lay the ground work for the common dimensions that will be used by the first project as well as the other components that will assist in scheduling processes and data validation. The back-end tables that manage your data warehouse might include:

- Refresh History – a table that contains 1 record for each refresh of the data. This will have an ID, the refresh date range (start and end dates), the status of the refresh (in progress, failed, completed) and any other attributes that you’d like to track for each refresh

- Refresh Parameters – a table that contains only one record. This record is a copy of the most recent record from the Refresh History table. A one-record table can be joined to any SQL statement as a Cartesian product (i.e. CROSS JOIN). This can add the start and end dates to any query to restrict the result-set to just the new and changed records

- Refresh Audit – This table will contain the audit or data validation results for each refresh. Row counts and data quality checks can be run on the data to validate that the refresh not only completed, but the result is correct and the data can be reported with confidence. This table should be linked to the refresh record in the Refresh History table that describes which refresh this audit relates.

  - Greg Kirkham

[bisyndicate-blog]

Good Characteristics for your First BI Project

So now that we’ve talked you out of the six figure Strategy document, how do you pick the first project to rebuild trust? When we are working with customers at this stage we are typically looking for some project characteristics to help determine where best to start. The kinds of things we look for in a first project are:

Keep it Simple – There are a few things that will drive up costs and complexity that should probably be avoided in your first kick at the can. When possible steer clear of projects that have known data quality issues. Also try to avoid introducing new business logic that might add complexity and require vetting. Ideally you want to work with data that is already well understood, readily available, complete and relatively clean. Finance and Human Resources data are usually good places to start but operational data like POS data for a retail company or demographic data for a direct marketing organization are all good options too.

High Impact - Do your analysts get constant requests from the Executive for a particular slice of data in constantly changing views? Is there an area of the business that is a growing concern or a neglected opportunity? Is there an appetite to drive behaviour through achievement of business objectives? Are there reports that take you weeks to cobble together, resulting in data past its useful shelf life? These are the kinds of projects that you should focus on first. You want to reduce pain or increase delight. Pet projects are to be avoided at all costs unless they are from the CEO.

Audience - There is no one perfect audience but you want to go for either large numbers of users or a smaller audience who are very influential. Either will do. This may sound like pandering but if you are truly creating value then these two audiences can help you continue on that path.

“So now that we’ve talked you out of the six figure Strategy document, how do you pick the first project to rebuild trust?”

Sex appeal - I know data doesn’t sound sexy but I think we once made a CFO purr when he received his new Executive Dashboard. Gone are the days of the Report Repository, with lists of endless reports that may or may not be in use by someone. Fellow data geeks will talk about Guided Analysis and Key Performance Indicators but ultimately presentation and usability are more important than ever in Business Intelligence. If you are emailing reports as pdf files… well, it’s time for a makeover.

End to End - With all Business Intelligence there is the presentation layer and the back-end, with potentially a few layers in between. Things like Data Marts and ETL (Extract Transform & Load) packages are never seen but they are where the “magic” happens. We typically call this the “heavy lifting”. On the front end you have Reports, Analytic tools, Dashboards, Scorecards, Charts and Maps. All of these objects are equally important as they represent the tangible results of work done on the back-end. In early projects, you ideally want to tackle BOTH. If you are picking an early project that just addresses issues on the back-end, you are doing yourself a disservice because the perception will be that you are spending money with no visible results. Likewise delivering something flashy on the front end that is still a strain on your IT folks to cobble together and/or support is equally unwise. Try to find something where there is both flash and substance. It will take your IT department a step further in the direction they need to go but will also delight the business users as well.

  - Deanna Tonn

[bisyndicate-blog]

Do We Really Need a BI Strategy?

I have to admit.  Strategy is a great gig.  Big consulting dollars, access to everyone in the company and all that we have to deliver is a document that looks suspiciously like the Strategy document we did for the last company.  It's also why we rarely do them.

Business Intelligence has a long history of cautionary tales.  The most common one is about the company that has struggled to deliver a comprehensive BI infrastructure for years.  Has tried every vendors bullet-proof, can't-fail software, followed every known methodology and hired every Top Gun consultant they could find and still came up short of delivering what the business needed.  The result of which is a chasm that grows with each passing failure between IT and the business.

Trust is a necessity in all good working relationships.  This is especially true of the relationship between business and IT and Business Intelligence has a very insidious way of eroding that trust quickly.  When I hear that a company is looking for a BI Strategy, I look at their track record and start taking the mental inventory...

Have they had large dollar BI failures in the past? 

Have they used three or more BI platforms from various vendors? 

Are they having a hard time attracting or retaining good smart people with BI skills? 

Have they invested in BI Strategy projects in the past and still find themselves at square one? 

If this sounds like your company or one you know, your problem isn't a lack of Strategy...at least not yet. 

Your real problem is trust.  And the only way you can rebuild trust is to demonstrate ability.  No document is going to help you with that.  So if you're going to invest, make sure it's in something tangible, something the business needs and something you can use as a tool to educate and market to the business on what BI is and what it can do for them. 

  - Deanna Tonn

[bisyndicate-blog]

Why Do BI Projects Fail?

I was recently speaking with a colleague of mine on the current state of Business Intelligence, our chosen but sometimes befuddling profession, and he made what I consider the perfect analogy. He compared the current state of BI to teenage sex....everyone was doing it and talking about it but it all amounted to a lot of fumbling in the dark and a general sense of dissatisfaction with the outcome.

While the analogy gave me a chuckle, the unfortunate reality of the statement gave me pause to wonder what it was that continued to hold us back. I say us because regardless of the talent of our skilled consultants and the numerous customer wins under our belts, we all...software vendors, consultants, analysts and the customers we sell to...all have to take some responsibility for the fact that generally speaking, successful Business Intelligence projects in both public and private sectors, organizations large and small, continue to be elusive. Software vendors, consultants and the analysts have all been waxing poetic on Business Intelligence for many years now and not much about the message has changed and I can honestly say that the technology is getting better. So why are we still not hitting it out of the park?

"He compared the current state of BI to teenage sex....everyone was doing it and talking about it but it all amounted to a lot of fumbling in the dark..."

Well, that's what I'm hoping we can discover together in this blog. I know there are a plethora of blogs on the subject of BI out there today but they tend towards the academic or the technical. While I have great respect for both of these voices, it is the layman, the guy who stares down the beast every day who has some interesting insight rarely shared on how we begin to accelerate past...well...the past.

So with that I beg your patience and ask you to stay tuned. In these pages we hope to share with you the cautionary tales, the lessons learned and the hard fought wins of our own collective experience in the field. Along for the ride are some of the best and brightest I hope to ever work with as well as a few guests to keep us honest. We encourage your feedback and hope you stay with us for the ride.

  - Deanna Tonn