Blogging about BI

BI Challengers: Disrupting the Mega Vendor?

2011-07-26T19:24:00.000-07:00

I recently wrote a post on my predictions for BI in 2011. I received lots of feedback about my thoughts with most of it focusing on my prediction that some of the niche BI players will start to disrupt the BI market in 2011. Based on that feedback I thought a follow up post to dig a little deeper on the topic was in order.

Let’s start with a little basic market analysis. Last year, Qlik Technologies (QLIK) went public, and the results were incredible. According to this Motley Fool article, the stock was the #6 IPO of the year and was up 158%. Not a bad first year and even better when you consider the market conditions. Looking at the other public BI vendors in the market, we can see that when measured by P/E ratio Wall Street is expecting aggressive growth in the market. Even at giants like SAP and Oracle the market has factored in considerable growth to the stock price. Looking at the revenue for the contenders, it is clear they are nowhere near the mega vendors in terms of size. Even so, their rapid growth and projected future growth is one indication that we should not ignore these companies.

The chart below shows the P/E (TTM) and Revenue as of 1/17/2011.

Company	P/E	Revenue
Tibco (TIBX)	49	184M
Qlik Technologies (QLIK)	43	50M
Actuate (BIRT)	24	39M
MicroStrategy (MSTR)	23	114M
SAP (SAP)	23	4B
Oracle (ORCL)	23	8.5B
IBM (IBM)	13	24B
Microsoft (MSFT)	12	16B

Unfortunately, companies like Tableau and LogiXML are not public so I was not able to get financial information for these organizations. One interesting data point worth mentioning is that Tableau had 34 job openings posted on their website, which may be an indicator of growth, and on the flip side,LogiXML had only 2. ** Update 1/18/2011: LogiXML reached out to me to clarify the 2 job postings listed on their website indicate the type of roles they are looking for and not the number of resources they expect to hire. They are very excited about their growth and have hired 15 resources in the past 2 months. They are also planning continued hiring in 2011.

From an analyst perspective I looked at the Forrester Wave and Gartner Magic Quadrant for their takes on the BI market and found it to be consistent with my basic stock market analysis. Gartner had Tableau, Tibco and QlikTech in the challenger quadrant, while Forrester had QlikTech and Actuate in the strong performers group. The analysts clearly highlighted these companies as market challengers and expect them to continue to grow.

The mega vendors are selling to the IT enterprise, while companies like QlikTech and Tableau are targeting the business and bypassing IT. Their tools are quicker to stand up, more intuitive and don’t need the configuration, support and hardware that the bigger players require. I see this path of selling BI to the business as a grass roots movement that starts at the departmental level. As the business becomes more comfortable with the tools there will be pressure to scale the installations from departments to divisions and so on, all the while encroaching on the larger BI installations footprint. As organizations try to scale, it will pressure these new players to build software that can handle enterprise data. In the meantime, with multiple departmental BI solutions being installed it will also create more pain as we try to reach the BI utopia of “the single source of the truth”.

In summary, the stock market projects the niche BI players to experience significant growth, and the analysts at Forrester and Garter have them nipping at the heels of the mega vendors. When I pair this information alongside my own experiences as an Information Management consultant I come to the conclusion that we are headed for a year of change in the BI space. Perhaps the whole BI pie is growing larger, leaving space for everyone to grow or maybe we are poised to see a disruption in the BI market as the pure play vendors continue to charge ahead. Either way, it is going to be an exciting year in the Data Management and Business Intelligence space.

IBM Cognos vs. Business Objects

2011-07-19T20:05:00.000-07:00

I’m a IBM Certified Cognos developer who is just learning Business Objects. With many years of Cognos experience and just a few weeks with BO I must be clearly biased towards Cognos and I probably am. So with my bias why even write something comparing the two technologies? Why not? As a consultant I am always learning new technologies and being asked to compare and contrast them. Given the number of different tools offered by both vendors, I think the comparison is best done at the individual tool level. It is also important to note that I have not used all the tools Business Objects has to offer.

Web Intelligence vs. Report Studio/Query Studio

I started my BO tour by looking at Web Intelligence. The first big difference I noticed right off the bat was that Web Intelligence is the equivalent to both Cognos Report Studio and Query Studio. BO has merged the two and not provided a division between a developer based report writing tool and an end user business based reporting tool. At first I thought this was a nice change since there are less tools to provide training on and less to support for an administrator. After spending some time using the tools, my opinion changed. There is no question that Web Intelligence is easier to learn than Report Studio, but that ease of use comes with a penalty. The power provided in Report Studio just cannot be compared with what is provided by Web Intelligence. For example, Report Studio gives you freedom to build and format prompt pages any way you see fit, while Web Intelligence provides very limited prompting functionality. Report Studio also provides more flexibility to create and join queries, adjust cardinality and change the method of aggregation applied. Cognos 8 gives you more control over report formatting, and provides more predefined objects to use on a report. One object that I really missed in BO is the feature to allow drill through from one report to another. While it can be done, it requires some detailed and time consuming changes to be made using the SDK, a pain for something I often need to use. When it comes to charting, the two tools are similar, both provide all the basic chart functions as well as more charting options than you will ever need. Again, I think Web Intelligence has the ease of use nailed, with a simple right click to turn a report to a chart. Cognos Report Studio makes it more difficult to create a chart, with more options needing to be set up front before the chart can be viewed.

What about Query Studio? Can that be compared to Web Intelligence? In my mind, it can and should be compared. When thinking about these two tools from a business user standpoint the they are neck in neck. In my experience ease of use is the most important factor, if you can’t get users to adopt the tool and use the data you are no better off then when you started. Query Studio is a simpler tool, with many less features and object properties to scare off end users but with that simplicity comes less flexibility. I don’t think it is a cut and dry choice on which tool is better. It is truly dependent on the sophistication of the end users involved. If they are more technically oriented and have the need to build fairly complex reports, Web Intelligence makes more sense. If they just need to quickly look at data with little formatting Query Studio gets the nod.

I think that the bottom line is that BO expects you to have a metadata model (Universe/Framework) and underlying database that is well thought out and designed for reporting. If that is the case, you won’t need most of the advanced features that Cognos provides because you won’t be trying to get around a limitation of the database or metadata model. In my experience, databases and models are rarely perfect, so having the flexibility that Cognos provides is worth the extra time it will take to learn the product.

Xcelcius vs. Cognos Go! Dashboard

After looking at the report building tools, I changed my focus over to the dashboarding toolset. There is not much to say here - Xcelcius is the hands down winner. Cognos is just not there yet with the new Go! Dashboard product. Xcelcius provides an Excel based data format that allows you to transform excel data easily into sophisticated dashboards. The Cognos tool is not much more than a way to take existing canned report and chart content and display it to the user in a flash based format. Xcelcius is not just a way to create charts in a flash format, it is a true dashboard development tool. I think future releases of Cognos will eventually catch up to the features offered in Xcelcius but for now, if your client is demanding complex and sharp looking dashboards, go with Xcelcius. The one downside with the Xcelcius tool is that it is not well integrated with InfoView. Getting dashboard content published to the portal and updated is a bit of an undertaking and not as intuitive as I would expect. The data needed to produce the dashboard has to be refreshed to the excel worksheet, and then the dashboard can be built off the excel data. This can be accomplished by scheduling jobs to build the data and create the dashboard but I would like to see it as an integrated feature.

Universe Designer vs. Framework Manager

I have not yet done a deep dive into Universe Designer but I do have a few initial thoughts about how the products compare. At a glance, the tools are strikingly similar in how they work. Both have nice GUI’s that provide a visual representation of the model, and both offer the ability to create formulas, rename objects and organize data elements in a meaningful way. The biggest difference I have seen is that Framework Manager lets you bring in multiple disparate data sources while Designer does not. In the end both tools can handle reporting off of multiple data sources they just have different approaches. Congos handles it completely within Framework Manager while BO allows a report to contain multiple universes. This allows a BO report developer to connect multiple data sources at the report level while a Cognos report developer cannot. BO also offers a more robust and complex solution called the Data Federator tool. This tool allows the ability to create relationships across data sources and then use those relationships in a universe. Beyond the data sources, the two tools handle dimensional modeling differently, but both are effective in their approaches. One feature of Designer that I found to be useful was the context tool. Setting context allows the Universe Designer the ability to force a query to use a specific and consistent join path through the data. Cognos would argue that you should never create a model with more than one path through the data, but the reality is that it happens and it is nice to have a way to address it.

Although I have not spent much time with Universe Designer, I think it is safe to say that it is very comparable to Framework Manager. The biggest issue with building effective models in either tool will almost surely be the skill of the metadata modeler and the quality of the database design.

Summary

Business Objects and Cognos are more similar than different but certainly they both have their own unique spin on BI. Both tools can be successful in your organization if you have a quality reporting data model and spend the time up front planning and preparing for your implementation. Likewise, both tools can end up a failure if the underlying data model is poorly constructed and the implementation is not well planned. Taking time up front to conduct BI assessment and plan your BI strategy will be far more important than the tool you select.

Cognos Framework Manager Best Practices

2011-07-18T05:20:00.000-07:00

Building metadata models using Framework Manager can be a simple or complex one. It all depends on the underlying database structure and how relevant it is to the reporting that is required. Regardless of the data model complexity there are a number of best practices that should be followed when building models using Framework Manager:

Never allow Cognos to create joins automatically when importing data sources. Cognos rarely chooses correctly and this inevitably creates future rework. Creating joins manually gives full control and avoids cardinality and join issues.
Always separate your model into the following three namespaces. Separating into three layers may seem difficult initially, but this organization makes the model easier to maintain. If the underlying data structures change, you will only need to reflect these changes in a single location.
1. Database Layer Namespace – This namespace simply contains all the query subjects brought in from your data source. No joins or renaming of query items occur in this layer. By leaving this layer untouched, future model changes will only need to be changed in this layer. All other layers can remain untouched.
2. Logical Layer Namespace – Relationships between query subjects should be created in this layer. If query subjects need to be merged, the merge should be created here. Any model filters should be applied and query subjects and query items should be translated to business names in this layer. All query subjects in the Logical layer should reference back to the database layer.
3. Presentation Layer Namespace – This layer contains shortcuts to the logical namespace and organizes the data for the business user.
Assign query usage in the database layer. Understanding the query usage property and setting it in this layer will ensure that it is always set correctly throughout the rest of the model. Each query subject that is created from the base layer will inherit these properties.
Never expose query subjects to the user that could result in cross product query. As an additional precaution, use the governor setting to deny cross product queries.
Use the governor settings to limit the query execution time. This will prevent users from developing run-away queries that can impact database performance.
Maintain a history of your models using a source control system. Framework Manager has direct connections to Visual SourceSafe and CVS- be sure to take advantage of this feature. CVS is an open source versioning system so cost should not be an excuse!.
When modeling your data, avoid creating situations where multiple join paths or loop joins can occur. To prevent these situations, try aliasing the query subject multiple times to force a single path through the data.
If possible, try to model in a star/snowflake schema. In a perfect world, this is done for you in the data model and the underlying ETL processes, but in reality we rarely get this lucky. It is possible to create a virtual star schema by merging query subjects to de-normalize data and creating new query subjects that contain only measures. The drawback of this method is that there can be significant performance impacts. Understanding the queries that are being created against the data is critical to making a good design decision here.
Set security at the highest level allowable, starting with the package, then the object and if needed the data. Applying security at a high level and then drilling down to more detailed security levels will save maintenance and troubleshooting time. Keep security as simple as possible while still meeting the security requirements and you will thank yourself at the end of the day.
Use data level security settings in Framework Manager only when absolutely necessary. Data security is a powerful feature but maintenance and troubleshooting with row level security become complex quickly. If you must use it, make sure to have it well documented for future developers and administrators. If you do choose to use data level security, be aware that it supercedes all other security settings in Cognos Connection.
Check cardinality for accuracy and test cardinality to make sure it is behaving as expected. Avoid many to many relationships in your model. If you cannot avoid them, organize data so that the user is less likely to create a report with a many to many relationship.
Name query subjects and query items using business terms. As simple as this seems, many organizations leave the original table and column names, creating confusion for the end user.
Only publish data that is in the presentation layer, and only expose query subjects that are useful for the end user.
Document the model. This is another simple task that is often overlooked,or more likely skipped to save time. Even if the model developer will be maintaining the model, six months from deployment the developer will not remember all the details of the model. Proper documentation expedites making future changes and fixing bugs Document each layer, the joins associated, and any calculations or merges that were done.

By following these best practices, you will build a model with documentation that allows for better maintenance and support. You will spend more time upfront building the model, but when changes inevitably occur, it will be well worth the effort. If you have other best practices you would like to share, please send them to me at bharden@captechventures.com and I will publish them here in a follow up post.

Making the Right Choice – Agile vs. Waterfall

2011-07-11T19:16:00.000-07:00

Trying to decide whether to stay with Waterfall or make the move to Agile on your next project? There are some big differences between the two methodologies that you need to be aware prior to making the switch. In this post I’ll highlight the differences and call out key areas to consider prior to moving to Agile.

There are four key themes that separate the two methodologies. When considering a change, it is important that you and your organization are ready to move from the “old” waterfall thinking on the left in the chart below to the “new” Agile thinking on the right.

Waterfall	Agile
Waterfall is release driven, with a defined critical path and sequence for delivery	Agile is based on short iterative delivery cycles usually 2-4 weeks in duration
Estimates are based on the work required to meet the requirements	Estimates are done based on the amount of work the team can accomplish in a set period of time
Requires clearly defined requirements upfront	Requirements are expected to evolve and change is embraced
Success is measured by the IT organization	Success is measured by business value delivered

Should you stay with Waterfall?

Agile is not right for every project team and is absolutely not a silver bullet that will solve your organizations delivery problems. In fact, if you are already struggling, trying to change to a new methodology might make things worse. Prior to making the transition to Agile there are two key areas to consider – resources and organization.

Resources

A large number of resources will not be able to handle working in an Agile environment. Prior to selecting project team members it is important to know if the resources you have in mind are ready for life on an Agile team. There are three areas to think about before selecting team members:

Co-location – Tight collaboration is a key tenant of Agile but it is not for everyone. The collocation and continual back and forth between team members can cause tension and create stressful team dynamics.
Discreet Work – Many team members like to finish their work and move on rather than stay with the same team of people for an extended time.
Performance – Agile is quick to single out individual performance issues. Not everyone is ready for this type of scrutiny.

Organization

Having committed resources is important, but having buy in from your organization is critical. If the leadership team is not ready to support the Agile methodology, you can almost guarantee that Agile will not be successful. When assessing your organizations readiness, consider the following:

Is the leadership team ready to support a shift in thinking from the big bang approach to smaller iterative delivery cycles?
Can the organization accommodate the frequent delivery of increments?
Is there a commitment to push Agile to other parts of the organization such as release management and production support?
Has the business bought in to Agile? They will be required members of the project team and must actively participate to make Agile work. In many cases they are not willing to give up their “day job” to support the project team.

Getting Started

If you have the right project team selected and commitment from business and IT leadership to support the Agile philosophy you are off to a great start, but it is only the beginning. As you start your first project, consider the following:

Make sure requirements are prioritized by the business up front
Start small and prove success, don’t try to build enterprise system 2.0 out of the gate
Plan for resistance – even though you have leadership buy in, not everyone will be supportive
Keep Iterations/Sprints short in duration (2-6 weeks)
Deliver business value early and often. Naysayer’s quiet down quickly when the team is delivering working software
If at all possible have the team co-located. While a distributed team can work, there is a tremendous amount of intangible value you get from co-location
Do not start the project unless you have business representation. Without the business, Agile will not work.

With these tips in mind, you should have a smooth entry into the world of Agile software development.

Planning for a Large Project Implementation

2011-07-05T04:37:00.000-07:00

After months or even years of hard work on a project, it is often easy to forget the final push into a production environment. After all the investment of time and effort that goes in to an IT project, having the project considered a failure due to a poor or failed implementation is not acceptable. The good news is that this risk can be mitigated by creating an hour by hour or even minute by minute implementation plan.

Starting to build an implementation plan early on in the development process is critical to ensure you have a comprehensive list of tasks and owners for the rollout. If possible I recommend starting the planning process prior to deploying to test, so that you can use the test deployment as a test of the implementation plan. I have also seen projects conduct a “mock go live” or “dress rehearsal” migration event prior to go live to test the implementation plan and identify gaps in the plan. In large project rollouts there may even be a dedicated team focused only on planning and executing the go live event.

So what goes in to planning a project implementation? In this article I will cover the major components to include when planning a project implementation and provide some key questions that should be addressed in each area. Depending on the size and complexity of your project, your rollout plan may or may not need to include this much detail.

Pre Implementation Plan

Prior to starting the implementation there may be a number of tasks leading up to the actual implementation that need to be tracked and have their results reported. Questions to consider when building a pre implementation plan include:

Are key deliverable dates from each work stream documented? (Requirements, Design, Test etc)
Are key business milestones included from the business community? (Training, process development etc)
Are there other key projects/business activities overlapping with the go live? (High volume processing days, end of month reporting, other system implementations etc)
Have all key stakeholders reviewed and agreed with the key dates?
Are communication activities included in the plan?

Hour by Hour Plan

As you develop the list of activities that need to occur during the rollout it is important to dive down to the lowest level of detail possible, down to the minute if you can. Multiple reviews of the plan with the task owners prior to the go live is invaluable as each review will uncover additional items that were not previously documented. Additionally, any opportunity to do a test run of the hour by hour plan should be leveraged.

Below are a list of questions that should be considered when building your plan:

Are the tasks of a short duration (less then 1 hr) with measurable outcomes?
Are dependencies identified for each task?
Does each task have an owner?
Does each task have a backup?
Does each task have an estimated duration?
Are there large gaps of time with no activities, if so is it logical?
Does the plan include checkpoint calls with executive stakeholders?
Does the plan include a go/no go decision point?
Can multiple activities be done in parallel?

Rollback/Contingency Plan

No one wants to think about what happens if things go wrong. In the event that they do and you are without a rollback plan, you will have a long road ahead trying to get back to where you started. The most important part of the rollback plan is to know what the latest point is in the implementation plan to which you can rollback. You must identify the point of no return and establish a checkpoint call with key decision makers to agree to rollback or proceed.

Below is a list of questions that should be answered as a part of the rollback plan:

Are resources to support the plan identified?
Is there contact information for identified resources who will execute on the plan during the implementation period?
Are the steps outlined clear and executable?
Is there evidence that the plan can be executed in expected timeframe?
Has all required documentation for the Contingency Plan been created and made accessible for resources in #2? (ex. meeting agenda, email message, scripts, reports, etc)
If there are physical or facility requirements to execute the plan, are they available? (phone/fax lines, etc)
If the plan steps are executed, will it effectively address the risk event impact?
Has the plan been communicated to the affected unit and supporting resources?

Command Center Logistics

As silly as it may sound, having a central location to execute, manage and monitor the rollout is incredibly important. Co-location will make the release smoother and help to get issues resolved faster. When planning for a command center it is important to consider the following items.

Are meeting rooms scheduled?
Has the phone system been configured appropriately?
Is the contact list complete?
If you are working off hours do you have HVAC requested?
Are food and drink coordinated?
Are copies of contact list available for use during the weekend?
Is the master hour by hour plan printed and ready for distribution?
Is the coverage schedule available and published?
Is there a process to capture escalate and resolve issues?
Will all resources be on site?
Do task owners know to contact command center before and after each task?

Communications

Leading up to the implementation, during the event itself and immediately following it communication activities are critical. Planning out the communication strategy ahead of time is crucial to success.

Are the key stakeholders identified?
Are standard status update times established and communicated to stakeholders?
Is the escalation protocol identified and communicated?
Do you have a status report distribution list?
How will you communicate?
1. Print - newsletters, posters, flyers, posted mail
2. Internet, e-mail (possibly with link to information on a website), video
3. In-person
4. Conference calls

Status Reporting

During the chaos of an implementation it is important to understand how status will be communicated. Having a status template ready ahead of time with all the key metrics identified will help keep the team organized during the rollout event. It will also help drive what data you need to capture during the implementation. Things to consider for implementation status reporting include:

What are the top volume issues?
What are the highest severity issues?
What is the overall defect ticket count/trend?
Do you have the ability to report on open tickets?
How many calls has the command center/service desk received?
How many defect tickets are open/resolved/closed?
What percent complete are you with the implementation?

Summary

It may seem like overkill to produce the above artifacts but, if you have ever been in the heat of the moment without these details you know how important they are. With resources working long shifts and often over night, tensions can run high and fatigue can set in. Having a well planned and communicated implementation strategy can alleviate some of this stress and help make the implementation a success.

Location Based BI

2011-06-13T04:35:00.000-07:00

Business Intelligence and GIS software have both been around for many years. More recently they have become closely integrated, with most BI offerings providing a connection to mapping layers such as MapInfo or Google Maps. The merger of these two tools provides a powerful visualization for data, connecting massive amounts of data with a visual that most everyone is already familiar with. For example, a map of New York City overlaid with census data quickly shows us where to find Gotham’s ethnic neighborhoods. Another interesting example using Google maps shows us where harassment is occurring in Cairo (and on a side note, Google Chromedoes an excellent job translating this site from Arabic to English). Finally, an interesting take on over 300 million tweets showing when Americans are most happy. Just think how difficult it would be to make sense of that volume of data without a map.

As more and more companies see the value of looking at their data in the context of geography this kind of mapping integration will only become easier and more common. The enterprise is embracing this mash-up between maps and business intelligence but what about incorporating the report consumer’s location?

Today, just about every Smartphone on the market has a built in GPS that has the capability to capture data on our location. This new dimension of real time location data combined with GIS information, a BI tool and an enterprise data warehouse could be incredibly powerful. Imagine an airplane mechanic being able to see the maintenance history of the aircraft that is closest to him. Wouldn’t it be nice to have a real time map showing the location of your pizza delivery driver, with an estimated delivery time based on the driver’s location? What about a salesman who is stuck at the airport? What if he could have a report that integrates the Twitter, FourSquare and Facebook location data of his clients within a mile of his location at the airport? Learning that a key client is also stuck at the same airport could allow him to “bump” into them to catch up or even have an impromptu meeting.

These things may sound far fetched, but the reality is that we have the tools in place to make these ideas happen today. In fact, Smartphone apps are already doing this with location based social media. For example, UrbanSpoon tells us the location of restaurants that are near us and lets us read comments on the quality of the food. If UrbanSpoon can do it, then why can’t a fortune 500 company? To extend these ideas to the enterprise it will require us to link our location with real time operational and analytical data. With in memory analytics becoming more commonplace and near real-time warehouse technology available the only limitation is our imagination.

Delivering Data Warehousing and BI Projects using Agile

2011-06-06T04:34:00.000-07:00

There is a lot of buzz lately around Agile BI and Agile data warehousing. Typical questions asked include - Does it produce better results? Is it faster? How much does it cost? And of course, most importantly, is the Agile methodology a fit for Data Warehousing and Business Intelligence projects?

What is Agile?

Companies are marketing their BI and DW products as agile solutions, meaning that they are easy to work with, adaptable to business changes and quick to stand up (think BI data discovery tools and data warehouse appliances). While the topic of an enterprise agile solution is worthy of a discussion there is another type of agile. In project management circles, when the word Agile comes up in conversation, it is always in the context of the Agile software development methodology, which also happens to be the context for this article. I won’t spend time discussing the Agile Manifesto or guiding principles here, instead I’ll try to talk about how Agile applies to BI/DW projects. If Agile techniques are new to you there are plenty of resources available on the web to learn more, withWikipedia being as good a starting point as any.

Why Choose Agile?

Does Agile produce better results than other methodologies when building a DW/BI application? The only real answer is that it depends. Building a BI application, a data warehouse or some other type of software is a complex process regardless of the methodology you choose. It is a commonly quoted statistic that over 50% of projects fail and some studies have even measured failure rates as high as 80%. So does using Agile increase the success percentage? The short answer is probably not.

So if it does not improve project success rates then why bother? If we are all familiar with traditional waterfall delivery methods, why not stick with what we know? There are two great answers to these question and which, in my opinion, confirm why we should all be delivering projects using Agile techniques. First , Agile delivers business value to our customers quickly, which is critical as time to market is a competitive advantage. For example, give the business access to customer data from the first source while you work on loading the second. The business can start analyzing the available data immediately and as sources are added their results become richer. Secondly, delivering functionality incrementally puts in a budgetary check and balance system. If the project is going to be a failure you have an opportunity to make an assessment after each incremental delivery. In a waterfall project there is a good chance you won’t know (or won’t admit) the project has failed until you are delivering it. So while the total project cost may be similar at the end, there is a huge savings if you can recognize a project failure early.

Will it Work for Warehousing?

Agile may deliver value to customers faster, and may be more cost effective, but can it work for Business Intelligence and Data Warehouse projects? Absolutely! It is interesting that given the benefits it has not yet gained mainstream acceptance in the warehousing and reporting project space. Agile has been slow to catch on because project teams are resistant to change, as more projects are successfully delivered I believe we will see Agile become the delivery methodology of choice. Let’s start with why it makes sense in data warehousing. A warehouse project plan can have iterations built in fairly easily if framed correctly. Splitting delivery by subject areas and/or sources of data is a natural way to segment the schedule and provides an easy way to deliver value early and often. The quicker we can get data loaded and into production, the faster data consumers can use data for decision making. One potential downside to a subject area or source base approach is trying to implement without strong architectural and modeling guidelines in place. Knowing how keys will be structured, how data quality issues will be handled and what the overall warehouse roadmap looks like is critical to an Agile delivery of a warehouse solution. Upfront planning around modeling and architecture will smoothen subject or source based iterations. As with all projects, communication is critical, and with an Agile delivery it is important to communicate the inevitability of rework as the warehouse evolves. Setting these expectations ahead of time will pay dividends when the first major change is required. Even with potential rework, the benefits gained by putting data in the hands of the business earlier more than pays for cost of future rework.

Will it Work for Business Intelligence?

Once a subject area or source data load is ready, the next step is to provide data to consumers using a Business Intelligence tool. Once again, the Agile techniques are ideal for building and deploying a business intelligence application. Building the semantic layer can be done in a similar fashion to developing the warehouse itself. As long as a clear architecture roadmap has been set, the semantic layer can be a fast follower to the subject area or source file ETL load, perhaps even rolling out with the subject area itself. Individual reports, dashboards and cubes can be managed and prioritized via a backlog and delivered as quickly as they are finished, adding incremental value on a regular basis.

Conclusions

Delivering a warehouse project using Agile will feel uncomfortable at first. It probably won’t work perfectly and you may have to adapt the processes to work with your organizational polices and procedures. Stick with it, listen to your team and don’t be afraid to make adjustments as you go (it is agile after all). If you go in with an open mind and a clear roadmap Agile will deliver business value faster than you ever imagined possible.

Mobile BI - An App or Just Another Report

2011-06-01T06:31:00.000-07:00

It seems like everyone wants to deploy a mobile BI solution these days. The idea of location awareness and the freedom to access data from anywhere at anytime has generated a lot of excitement. From a software perspective BI vendors are fueling the mobile BI market with “out of the box” solutions for most of the major mobile platforms (iOS, Android, Blackberry). At the same time we have had an almost overnight adoption of tablets that make mobile BI more usable thanks to the larger screen size.

Just because mobile BI sounds like a good idea doesn’t always mean it is. The tools available from the big vendors can display existing reports on mobile devices but that doesn’t mean the reports are appropriate for mobile consumption. Pushing out an existing reporting suite to mobile consumers will make for frustrated end users and low satisfaction rates. Just imagine pulling out your phone to view a report and waiting for a couple of minutes to download the data. Once you download the data, you have to point, drag and scroll to get to the page of the report you are interested in. Then to locate the right data on the page you have to pinch and zoom to actually read the content. That sure doesn’t sound like the experience we want our BI consumers to have. What is really needed is a mobile BI application that is built to meet the needs of a mobile reporting consumer. Now imagine pulling out your mobile device, loading a report with a single tap and immediately having access to the data you wanted with no additional gestures required. That is the difference between a report on a mobile device and a mobile BI application.

Mobile reporting is a band-aid solution to get existing reports out to mobile devices quickly, with little or no consideration for the device they are being consumed on. A mobile BI application is a holistic mobile analytics solution designed with a specific need in mind. In the rush to deploy mobile BI many organizations end up with mobile reports and not a mobile application.

The chart below is how I distinguish between a mobile BI application and mobile BI reporting. If your organization has delivered the solution on the right, you probably have unsatisfied users, low adoption rates and needs that are not being met. If you have built the solution on the left, your users are likely getting tremendous business value from the BI application.

Mobile BI Application vs. Mobile BI Reporting
Mobile BI Application	Mobile BI Reporting
BI designed to meet a mobile reporting need	Existing BI ported to a mobile device
Reports formatted for the mobile device	Little or no consideration given to the mobile user experience
Aggregated data	Detail level data
Charts and Dashboards	Tabular view
Small data sets	Large data sets
Reports designed for a specific enterprise standard device	Mobile device not considered (tablet vs. phone)
Reporting solution designed for enterprise standard OS	End user Mobile OS not considered
Real or near real time information	Daily, Weekly, Monthly reports

So what are the best ways to avoid the mobile BI reporting trap and build a Mobile BI application? Define a mobile BI strategy for your organization and establish a need. If you don’t have a strategy laid out and a need defined, you will likely end up with a set of reports that can be viewed on a mobile device but will not have a BI Application.

Estimating Extract, Transform, and Load (ETL) Projects

2011-05-28T16:41:00.000-07:00

This is a repost of the attached article first published in the PMI Virtual Library.

In the consulting world, project estimation is a critical component required for the delivery of a successful project. If you estimate correctly, you will deliver a project on time and within budget; get it wrong and you could end up over budget, with an unhappy client and a burned out team. Project estimation for business intelligence and data integration projects is especially difficult, given the number of stakeholders involved across the organization as well as the unknowns of data complexity and quality. Add to this mix a firm fixed price RFP (request for proposal) response for a client your organization has not done work for and you have the perfect climate for a poor estimate. In this article, I share my thoughts about the best way to approach a project estimate for an extract, transform load (ETL) project.For those of you not familiar with ETL, it is a common technique used in data warehousing to move data from one database (the source) to another (the target). In order to accomplish this data movement, the data first must be extracted out of the source system—the “E.” Once the data extract is complete, data transformation may need to occur. For example, it may be necessary to transform a state name to a two-digit state code (Virginia to VA)—the “T.” After the data have been extracted from the source and transformed to meet the target system requirements, they can then be loaded into the target database—the “L.”

Before starting your ETL estimation, you need to understand what type of estimate you are trying to produce. How precise does the estimate need to be? Will you be estimating effort, schedule, or both? Will you build your estimate top down or bottom up? Is the result being used for inclusion in an RFP response or will it be used in an unofficial capacity? By answering these questions, you can assess risk and produce an estimate that best mitigates that risk.

In many cases, the information you have to base your estimate on is high level with only a few key data points do go on, and you do not have either the time or ability to ask for more details. In these situations, the response I hear most often is that an estimate cannot be produced. I disagree! As long as the precision of the estimate produced is understood by the customer, there is value in the estimate and it should be done. The alternative to a high-level estimate is none at all, and as someone who has to deliver on the estimate, I would rather have a bad estimate with clear assumptions than no baseline at all. The key is being clear about how the estimate should be used and what the limitations are. I have found that one of the best ways to frame the accuracy of the estimate with the customer and project team is through the use of assumptions. Every estimate is built with many assumptions in mind and having them clearly laid out almost always generates good discussion and eventually a more refined and accurate estimate.

A common question that comes up during the estimation process is effort versus schedule; in other words, how many hours will the work take versus the duration it will take to complete the effort. To simplify the estimating process, I start with a model that delivers the effort and completely ignore the schedule. Once the effort has been refined, it can be taken to the delivery team for a secondary discussion on overlaying the estimated effort across time.

Once you know what type of estimate you are trying to deliver and who your audience is, you can begin the process of effectively estimating the work. All too often, this up-front thinking is ignored and the resulting estimate does not meet expectations.

I’ve reviewed a number of the different ETL estimating techniques available and have found some to be extremely complex and others more straightforward. Then there are the theory of estimating and the tried and true models of Wide Band Delphi and COCOMO. All of these theories are interesting and have value but they don’t easily produce the data to support the questions I am always asked in the consulting world: How much will this project cost? How many people will you need to deliver it? What does the delivery schedule look like? I have discovered that most models focus on one part of the effort (generally development) but neglect to include requirements, design, testing, data stewardship, production deployment, warranty support, and so forth. When estimating a project in the consulting world, we care about the total cost, not just how long it will take to develop the ETL code.

Estimating an ETL Project

In the ETL space I use two models (top down and bottom up) for my estimation, if I have been provided enough data to support both; this helps better ground the estimate and confirms that there are no major gaps in the model.

Estimating an ETL Project Using a Top Down Technique

To start a top down estimate I break down the project by phase and then add in key oversight roles that don’t pertain specifically to any single phase (i.e., project manager, technical lead, subject matter expert, operations, etc.). Once I have the phases that relate to the project I am estimating for, I estimate each phase vertically as a percentage of the development effort, as shown in the chart below. Everyone has a different idea about what percentage to use in the estimate and there is no one right answer. I start with the numbers below and tweak them accordingly, based on the project environment and resource experience.

Phase	Percentage of Development
Requirements	50% of Development
Design	25% of Development
Development
System Test	25% of Development
Integration Test	25% of Development

Once I have my verticals established, I break my estimate horizontally into low, medium, and high, using the percentages below:

Complexity	Percent of Medium
Low	50% of Medium
Medium	N/A
High	150% of Medium

Generally, when doing a high-level ETL estimate, I know the number of sources I am dealing with and, if I’m lucky, I also have some broad stroke level of complexity information. Once I have my model built out, as described above, I work with my development team to understand the effort involved for a single source. I then take the numbers of sources and plug them into my model, as shown below (Figure 1, in yellow). If I don’t have complexity information, I simply record the same numbers of sources in the low, medium, and high columns to give me an estimate range of +/−50%

I now have a framework I can share with my team to shape my estimate. After my initial cut, I meet with key team members to review the estimate, and I inevitably end up with a revised estimate and, more importantly, a comprehensive set of assumptions. There is no substitute for socializing your estimate with your team or with a group of subject matter experts; they are closest to the work and have input and ideas that help refine the estimate into something that is accurate and defendable when cost or hours are challenged by the client.

Estimating an ETL Project Using a Bottom Up Estimate

When enough data are available to construct a bottom up estimate, this estimate can provide a powerful model that is highly defendable. To start a bottom up ETL, estimate a minimum of two key data elements are required: the number of data attributes required and the number of target structures that exist. Understanding the target data structure is a critical input to ETL estimation, because data modeling is a time-consuming and specialized skill that can have a significant impact on the cost and schedule.

When starting a bottom up ETL estimate, it is important to break up the attributes into logical blocks of information. If a data warehouse is the target, subject areas work best as starting points for segmenting the estimation. A subject area is a logical grouping of data within the warehouse and is a great way to break down the project into smaller chunks that align with how you will deliver the work. Once you have a logical grouping of how the data will be stored, break down the number of attributes into the various groups, noting the percentages of attributes that do not have a target data structure.

Once you have defined the target data subject areas, attributes, and percentages of data modeled, the time spent per task, per attribute can be estimated. It is important to define all tasks that will be completed during the life cycle of the project. Clearly defining the assumptions around each task is also critical, because consumers of the model will interpret the tasks differently.

In the example shown, there is a calculation that adjusts the modeling hours based on the percentage of attributes that are not modeled, giving more modeling time as the percentage increases. This technique can be used for any task that has a large variance in effort based on an external factor.

To complete the effort, estimate the hours per task that can be multiplied by the total number of attributes to get effort by task. In addition, the tasks can be broken out across the expected project resource role, providing a jump start on how the effort should be scheduled. As with any estimate, I always add a contingency factor at the bottom to account for unforeseen risk.

Comparing a top down estimate with a bottom up estimate will provide two good data sets that can drive discussion about the quality of the estimate as well us uncover additional assumptions.

Scheduling the Work

Once the effort estimate is complete (regardless of the type), I can start thinking about how much time and how many resources are needed to complete the project. Generally, the requestor of the estimate has an expected delivery date in mind and I know the earliest time we can start the work. With those two data points, I can calculate the number of business days I have to deliver the project and get a rough order of magnitude estimate of the resources required.

The first thing I do is map the phases established in the effort estimate to the various project team roles (BSA, developer, tester, etc.). Once I break down the effort into roles, I can then divide the effort by the number of days available in the project to get the expected number of resources required. In the example below (Figure 2), I shorten the time that the BSA, developer, and tester will work, taking into account that each life cycle phase does not run for the duration of the project. At this stage, I also take into consideration the cost of each resource and add in a contingency factor. This method allows for the ability to adjust the duration of the project without impacting the level of effort needed to complete the work.

Using the techniques described above provides you with the flexibility to easily answer the “what if” questions that always come up when estimating work. By keeping the effort and the schedule separate, you have total control over the model.

Delivering on the Estimate

Once the effort and duration of the project are stabilized, a project planning tool (e.g., Microsoft Project) can be used to dive into the details of the work breakdown structure and further map out the details of the project.

It is important to continue to validate your estimate throughout the project. As you finish each project phase, revisiting the estimate to evaluate assumptions and estimating factors will help make future estimates better, which is especially important if you expect to do additional projects in the same department.

Conclusion

In my experience, bottom up estimates produce the most accurate results, but often the information required to produce such an estimate is not available. The bottom up technique allows the work to be broken down to a very detailed level. To effectively estimate bottom up ETL projects, the granularity needed is typically the number of reports, data elements, data sources, or the metrics required for the project.

When a low level of detail is not available, using a top down technique is the best option. Top down estimates are derived using a qualitative model and are more likely to be skewed based on the experience factor of the person doing the estimate. I find that these estimates are also much more difficult to defend because of their qualitative nature. When doing a top down estimate for a proposal, I like to include additional money in thebudget for contingency to cover the unknowns that certainly lie in the unknown details.

There is an argument that a bottom up estimate is no more precise than a top down estimate. The thinking here is that with a lower level of detail, you make smaller estimating errors more often, netting the same result as the large errors made in a top down approach. Although this is a compelling argument (and why I do both estimates when I can), the more granular the estimate you have, the quicker you can identify flaws and make corrections. With a top down estimate, errors take longer to be revealed and are harder to correct.

An estimate is only as good as the data used to start the estimate and the assumptions captured. Providing clear and consistent estimates helps build credibility with business customers and clients and provides a concrete defensible position on how you plan to deliver against scopeand it also provides a constant reminder of the impact of additional scope. No matter how easy or small a project appears to be, always start with an estimate and be prepared for that estimate to need fine tuning as new information becomes available.

Glossary of Terms

ETL – Extract, Transform, and Load. A technique used to move data from one database (the source) to another database (the target)

Business Intelligence – A technique used to analyze data to support better business decision making

Data Integration – The process of combining data from multiple sources to provide end users with a unified view of the data

Data Steward – The person responsible for maintaining the metadata repository that describes the data within the data warehouse.

Data Warehouse – A repository of data designed to facilitate reporting and business intelligence analysis

RFP – A request for proposal (RFP) is an early stage in the procurement process, issuing an invitation for suppliers, often through a bidding process, to submit a proposal on a specific commodity or service.

Source – An ETL term used to describe the source system that provides data to the ETL process

Subject Area – A term used in data warehousing that describes a set of data with a common theme or set of related measurements (e.g., customer, account, or claim)

Target – An ETL term used to describe the database that receives the transformed data

About the Author

Ben Harden, PMP, is a Director in the Data Management and Business Intelligence practice of the Richmond, Virginia based consulting firm CapTech . He specializes in the project management and delivery of data integration and business intelligence projects for Fortune 500 organizations. Mr. Harden has successfully managed data-related projects in the health care, financial services, telecommunications, and governmental sectors and can be reached via e-mail atbharden@captechconsulting.com.

Mobile Business Intelligence – Android, iOS, WP7 or BlackBerry

2011-05-25T17:37:00.000-07:00

Gartner predicts that 30% of all BI will be consumed on mobile devices by 2013. With the amount of interest in mobile BI and those kinds of bold predictions you would think it would be easy to understand the options available. It is not. There are a lot of things to consider when deciding what mobile BI tool and operating system fits for your organization:

What BI tool are you currently using? The mobile solution you choose may be dictated by the BI solution the enterprise already have in place. Even worse, it might not be compatible with an already implemented mobile standard.
What mobile OS do you want to support? There are varying degrees of compatibility across the mobile offerings, from full native support to none at all.
Does your BI tool deliver content via the web or natively? Native support provides an integrated solution designed from the ground up for a specific OS. Web based solutions work across more devices but may sacrifice usability.
Will you deliver BI on phones or tablets or both? Not all solutions support all mobile operating systems on both phones and tablets. For example, SurfBI only supports the iPad and not iPhone.
If it is a web delivery solution does the charting engine use Flash or HTML5? For example, Business Objects claims support for iOS, but Xcelcius is Flash based and therefore not compatible with iOS.

There is a lot of gray area with the browser based solutions since most mobile browsers can view web content in a non mobile format. The below chart is representative of what each vendor claims and is based on mobile formatted delivery.

BI Tool	Mobile Operating Systems
BI Tool	Android	iOS	WP7	BlackBerry
IBM Cognos 10	No	Yes - Browser	No	Yes - Native
SAP Business Objects 4	Yes - Browser	Partial (no Xcelsius)	Yes - Browser	Yes - Native
MicroStrategy	No	Yes – Native	No	No
Microsoft SSRS	No	No	No	No
Oracle BI 11g	No	Yes - Native	No	No
LogiXML	Yes - Browser	Yes - Browser	Yes - Browser	Yes - Browser
Qlikview	Yes - Browser	Yes - Native	Yes - Browser	Yes - Native
Tableau	Yes - Browser	Yes – Native in Beta	Yes - Browser	Yes - Browser
SAS	No	Yes – Native	No	No
Note: Data as of May 2011

If the combination of tool and mobile OS you are looking for is not available yet, there are other mobile presentation layer options that can help. These presentation layer tools simply connect to a data source and present the data to mobile devices in a native format. In addition to connecting directly to a data source for presentation, these tools also provide native connectors to some of the BI tools, allowing you to reuse existing BI metadata and report content.

Mobile Presentation Layer Tools (May 2011)
Tool	BI Connectors	Android	iOS	WP7	BlackBerry
RoamBI	SSRS, Cognos, BOBJ, Oracle Hyperion, SAS	No	Yes – Native	No	No
SurfBI	OBIEE	Yes – Native	Yes – Native	No	No

As you can see, this is not a one size fits all market. The iPad and iPhone support most BI products, but there are limitations if it the content is delivered via the web using Flash objects. Microsoft SSRS does not have native mobile support yet, but I expect that to change quickly given the focus on Windows Phone 7. In the meantime, RoamBI has a SSRS connector that can deliver native content to iOS devices. Oracle also has limited support, but SurfBI has filled that void with direct Android and iOS connectivity to OBIEE. The BI vendors are adding native support quickly so I expect rapid change as the market matures and enterprise tablet leaders emerge.

In summary, the mobile BI market is rapidly emerging and there is not an obvious market leader. The right solution for your organization will depend on what tools you already have in place and your long term target mobile architecture.

Blogging from the Gartner BI Summit: Day 3

2011-05-22T19:15:00.000-07:00

It was a great 3 days at the Gartner BI Summit and I thoroughly enjoyed attending a conference dedicated solely to Business Intelligence. It was great to experience the general BI sessions while also getting to sit in on some of the healthcare vertical sessions and hear about the specific BI challenges that the healthcare industry is dealing with. If you were not able to attend, below is my interpretation of the major trends that were discussed:

Not surprisingly, mobile BI was a huge topic, and Andy Bitterer's mobility session was packed. When he asked the audience if they thought mobile was "cool" every hand went up, but when he asked how many people were actually implementing mobile BI only a few raised their hands. It was obvious that we are still in the "hype" stage of mobile with no real substance yet, but it is coming fast and we may very well be at the peak of the hype cycle. Let's hope so, because Gartner predicts that by 2013 over one third of BI will be delivered via a mobile device.
Data visualization is increasingly important in the BI market. There have been major improvements in visualization capabilities and people want to use these tools to interact with their data. Viz helps people to ask the question they didn't know they had and thus changes how they interact with data.
In memory analytics is here with all the major vendors supporting in memory capabilities. It is important to note that although memory is getting cheaper, the lower cost will not be able to make up for the amount of data being generated. As a result, we will need to find a balance between the use of disk and solid state. Look for the next generation of databases to manage this balance for us.
In 5 years unstructured “big” data will be more important than the structured content we typically deal with today. The BI industry will undergo tremendous changes to allow us to mine this new type of data.
Agile BI development methods are not just a passing fad. They are being used extensively in the industry and allowing organizations to get to market faster, embracing the idea of changing requirements
Data discovery tools are changing the way business users consume data. They are allowing the business to quickly access large data sets and look at data in ways they never have before. Even though these tools are growing quickly in most cases they don’t replace the large mega vendor offerings they just compliment them.

In addition to the trends above I heard a bunch of interesting statistics during the week that I thought were worth repeating:

Business satisfaction scores for BI applications is low – just 4.5 out of 10
The average number of BI platforms supported per customer is 4.1
By 2013 one third of BI will be delivered by a mobile device
50% of BI requirements will change in the first year
eBay deals with 50TB of data a day
Mega vendor market share increased from 57% in 2009 to 59% in 2010
Yahoo has worlds largest SSAS cube at 16TB
UPS saved 3 million gallons of fuel thanks to their award winning BI solution
Only 10% of data is actionable
by 2014, 40% of business analytics spending will go to software integrators, not software vendors
Between 75% and 85% of data is unstructured
Worldwide volume of data is growing at 59% per year

All in all, the summit was well worth the trip and I am looking forward to attending again next year.

Click here for my blog on day one of the summit.

Click here for my blog on day two of the summit.

The Importance of Report Rationalization

2011-05-17T18:57:00.001-07:00

Many things need to happen to have a successful migration from a legacy application to a new system. There are difficult data mapping decisions that need to be made and data quality concerns to be addressed. There are the typical challenges of change management and end user training. In large efforts, entire teams may be dedicated to profiling and remediation of legacy data. In the midst of all this migration activity sits a critical and often underestimated function – reporting. What happens to all the analytical, transactional and operational reports the legacy system provided and where do users go for that data in the new world?

The first step to ensuring that existing and future reporting needs are met is to conduct a report rationalization effort. A reporting rationalization is a holistic review of all the existing reports, metrics and dimensions used in the current system with a mapping to how this data will be delivered in the new system. Often during a reporting rationalization, many reports and metrics are found to be either duplicated in multiple places or unused. By spending time up front to understand the legacy reporting and how it translates to the new reporting needs you can almost always reduce the number of reports that need to be replicated. Building a smaller number of highly utilized reports reduces upfront development costs and also keeps maintenance costs lower moving forward.

How does one complete a reporting rationalization? Depending on the availability of documentation it can be a daunting task or an easy one. Understanding what data you need prior to starting the rationalization and developing a strategy to collect it will keep you focused and prevent analysis paralysis. At a minimum, the following data must be captured to successfully use the output of the rationalization to drive target state reporting requirements:

Legacy report name and description
Report owner and/or metric owner
Name and definition of each metric as defined on the legacy report
Name and definition of each dimension as defined on the legacy report
Data types and length of each metric/dimension on the legacy report
Source database, table and column name for each metric and dimension
Calculation for each metric. Often times business logic is embedded into a report, so it is critical to capture this logic so that informed consolidation discussions can occur
Business area – What part of the organization is the report intended for?
Report frequency and latency – Is the report run on demand, produced daily, weekly or monthly? Is the data real-time or batch?
Report type (Operational, Transactional, Analytical) – Often times the legacy system does not distinguish between report types. As organizations move to target architecture, there may be a need to separate reports by future data store
Suggested business intelligence component to use in the target state architecture. How will the data be consumed in the target state? Is it analytical data that needs to be sliced and diced in a cube, is it a canned report or does it require ad-hoc access?

Once this data is collected for each of the legacy reports, a consolidation recommendation can be made. There will likely be opportunities to eliminate reports or combine reports to meet multiple users’ needs. The rationalization also gives the warehouse team a reference point to ensure that the requirements they are delivering on will meet the final reporting needs. Completing a rationalization effort upfront will pay dividends throughout the project and increase the likelihood of having a satisfied business customer at go live.