BIpartisan

New Path, Same Focus

2009-12-05T03:43:08Z

I’ve worked with Mariner for almost 12 years. It’s been a very good journey, with many great experiences. I’ve worked with a lot of great people, and delivered some really interesting BI solutions to clients in a number of industries. One aspect of my job that I always particularly enjoyed was helping developers be more productive when creating BI solutions, and reducing the repetitive (read: “boring”) aspects of developing solutions on the Microsoft stack.

Recently, a new opportunity to focus more heavily on that came along. As a result, after a long and enjoyable career with Mariner doing business intelligence consulting, I am taking a new position with Varigence, a company that is producing tools that will make implementing BI solutions faster and easier, as well as introduce new capabilities and better integration into the Microsoft BI stack.

I’m really looking forward to the new role and the new experiences it will offer. I will continue to be heavily involved in Microsoft BI, so I plan to maintain this blog and continue speaking and writing on it as often as often as possible.

Scale Up or Scale Out for SQL Server Data Warehouses

2009-03-01T06:53:58Z

Historically, scale up has been the model for Microsoft data warehouses. Running a large, multi-terabyte data warehouse meant buying a lot of hardware for a single server, and hoping that it would be enough, once the warehouse was fully loaded and under use. If the hardware wasn’t sized properly, you could be looking at big costs for purchasing a new server, with more capacity for memory, disk, and CPUs.

Over the past several months, though, there have been a number of announcements in the SQL Server space that change that. We now have the option of scaling our warehouses up or out. Project “Madison”, which is the integration of the massively parallel processing (MPP) technologies from the DATAllegro acquisition, promises to allow SQL Server 2008 to scale out to 100s of terabytes in the warehouse, by distributing processing among multiple commodity servers. Even though it’s not been officially released yet, I’ve seen several demos of the functionality, and it looks promising. The advantage of this approach is that as you need additional capacity, you simply add additional servers.

On the scale up front, last week Microsoft announced “SQL Server Fast Track Data Warehouse”, which is a set of reference architectures for symmetrical multi processing (SMP) data warehousing. These are single server configurations that are optimized for data warehousing workloads, and have been tested and validated. These take much of the guesswork out of sizing your data warehouse server. However, you still have to provide good estimates of query volume and size to use the reference architectures effectively.

So now the question becomes, should you target a scale up or scale out approach for your data warehouse? One of the deciding factors is going to be your data volume. The Fast Track reference architectures are currently targeted towards 4 to 32 terabyte warehouses. Given current hardware restrictions, that’s the practical limit for a single server. However, as the hardware continues to get better, that number is expected to go up. “Madison”, on the other hand, can scale well past 32 terabytes. So if your current data needs are greater than 32 terabytes, I’d be looking closely at “Madison”.

What if your current needs are less than 32 terabytes, but you expect to grow past that point over the next couple of years? Well, fortunately, the Fast Track reference architectures are designed to offer an easy transition to “Madison”, when your needs grow to that point. And if you expect your data volumes to stay below the 32 terabyte mark, then the Fast Track reference architectures certainly offer a greater degree of confidence that you are getting the appropriate configuration for your warehouse.

It’s always nice to have options, and improving the scaling abilities of SQL Server should certainly help Microsoft in the large data warehouse marketplace. However, the roadmap for how this might apply to the Analysis Services component of SQL Server hasn’t really been directly addressed yet. It would seem logical to offer the same sort of solutions in that space. It will be interesting to see which direction Microsoft takes on that.

Developing a Foundation While Doing Iterative Development

2009-02-17T00:06:01Z

In my initial post about doing iterative development for BI/DW projects, I mentioned that one of the challenges was developing a solid foundation while doing iterative development, especially in the first few iterations. If you are started from scratch on a new BI initiative, there is often a lot of work to do in getting environments established, developing processes for moving data and code between environments, and exploring and validating the source data to be used. Unfortunately, most of this work does not result in deliverables that an end user would consider valuable. Since, as part of each iteration, you want to have the possibility of delivering working functionality to the stakeholders, this can present a problem.

Since most end users consider working functionality to be something that they can see in a nice user interface, you need to look at ways to minimize the development time required to present data to the end user. Some of the common time-consuming tasks in the first couple of iterations are:

Establishing the environments
Exploring and validating the source data
Developing ETL processes to move and cleanse the data

There are really no quick workarounds to setting up the environments. In fact, I’ve usually found that taking shortcuts on the environments leads to much bigger problems down the road. However, what can be effective is to minimize the number of of environments that you deal with in each iteration. While theoretically you should be able to deploy to production in the first iteration of a project, it’s rare that this is actually needed. So instead of creating a development, QA, and production environment, consider only establishing the development and QA environments. I do think that having at least two environments is important, so that you can begin validating your deployment procedures.

Exploring and validating the source data is definitely important. In the first couple of iterations, though, it’s often necessary to limit and restrict what you explore. For example, a project I was involved in recently had some very serious issues with data quality. The source database did not enforce referential integrity, so a large percentage of the data in the source was not related to the rest of the data correctly. Rather than derailing the current iteration to completely research and resolve the data quality issues, the project team and the stakeholders made the decision to only import data that was related correctly. This enabled the project team to still present a set of working reports to the stakeholders at the end of the iteration, rather than not being able to demonstrate any working functionality. The subsequent iterations were adjusted to better reflect the level of data quality.

ETL processes can be time-consuming to develop, particularly if the organization does not already have a framework in place for the ETL processes. In the first couple of iterations, an alternative approach is to load data in a more manual fashion, using SQL scripts or direct manipulation to get an initial set of data populated. This has a couple of benefits. One, it allows the time for building a full ETL process to be spread across multiple iterations. Two, it allows the end users to get a look at the data (in a friendly user interface, if you follow the advice above) and validate that it is correct.

A key part of developing the foundation, while still providing value in each iteration, is accepting that you can’t do it all in a single iteration. The foundation development will have to be spread across multiple iterations, and it is acceptable to build some scaffolding code in order to deliver functionality in each iteration. Clearly, you want to minimize that amount of code that can’t be reused. Generally, I’ve found that with the Microsoft toolset for BI, it’s pretty easy to build incrementally, with minimal rework. However, even if your tools don’t support this as well, in my experience the downsides of having some code that gets replaced in a later iteration are far outweighed by the benefits of being able to demonstrate tangible progress to the stakeholders in each iteration of the project.

Getting Business Value from BI Today

2009-02-02T17:10:17Z

Today’s economy and business environment poses problems for many IT departments. Budgets are tight, companies are looking for ways to reduce headcount, and strategic investments are not very popular right now. Instead, companies are focusing on projects that have immediate ROI, primarily in the form of cutting costs. This has particular impact on BI projects, since they often have longer time frames, and in many cases, don’t have clearly defined ROI. The effects of this are becoming evident. A number of consulting firms in the southeast US have cut back on the number of BI consultants they employ, and a number of companies in the region are cutting back on planned BI expenditures.

Since I believe very strongly that BI is a valuable investment, I hate to see this, but it is understandable. If you are beginning a large data warehouse project, that projects 12 months or more before any value is delivered to the users, it’s not a very attractive investment right now. So, given the current environment, what can you do to ensure that your BI projects don’t end up on the chopping block?

Solve Existing Problems

Focus on solving existing problems, rather than trying to anticipate future needs. Don’t get me wrong, sometimes you do need to invest in creating the BI infrastructure to handle future needs. However, in the current environment, business decisions makers are much more interested in hearing about the problem you can solve today, not the problems that they may encounter in a few years (if the business is still around).

Identify ROI

Identify the ROI up front. Too many BI projects get started on the vague promise of “When we have all this data together, we’ll be able to do great things!”. That’s not enough to justify expenditures today. Instead, you need a clear understanding of the problem, the cost of solving it, and the cost of not solving it. In a cost cutting environment, the latter point is often effective in communicating why the project needs to be done.

Incremental Wins

Look for incremental wins. As I’ve commented before, many BI initiatives take on a massive amount of scope, and run for many months before producing anything of value. An incremental approach allows you to demonstrate value to the stakeholders rapidly, and those incremental demonstrations of your ability to solve the problem often result in additional funding coming your way.

Do More With What You Have

Now that PerformancePoint Services is being rolled into SharePoint, there is an opportunity to provide additional BI with minimal or no investment in software, particularly if your company already uses SharePoint. By combining the capabilities of PerformancePoint with the workflow capabilities in SharePoint, you can provide some very interesting solutions in optimizing business processes, and making sure that the users of the process have the appropriate information at their fingertips.

By focusing on quick wins that have immediate ROI, BI teams can still provide a lot of value to the business. Demonstrating this value is the key to keeping your BI initiatives alive in a down economy.

Impact of the PerformancePoint Server Changes

2009-01-27T01:08:00Z

If you follow business intelligence news at all, then you probably saw the news from Microsoft last week that PerformancePoint is becoming a component of SharePoint. However, it won’t be all of PerformancePoint – the Plan portion will see one additional service pack (SP3), then development will cease. The Monitor and Analyze portions of the product will become part of the SharePoint Enterprise license.

Reaction has been mixed. Generally, many people see the advantage in including the Monitor and Analyze functionality in SharePoint, as it will open that functionality to a much broader audience. This lines up nicely with Microsoft’s “BI for the masses” vision that they have working toward for several years. It also lines up with the more recent marketing message, “People-ready BI”. Seeing that SharePoint is becoming the place that many users go to do their work, it makes sense to incorporate their BI tools in the same location. I think that offering PerformancePoint Services (the new name for the Monitor and Analyze functionality under SharePoint) as part of SharePoint will make it easier to include BI functionality in new applications and lower the barrier to adoption of this functionality in organizations of all sizes.

The negative reactions are primarily around two things: discontinuing Plan, and not having a full-client story (besides Excel). I understand the reactions around discontinuing Plan. Version 1 had some rough edges (OK, a lot of rough edges), but Microsoft has a history of quickly releasing subsequent versions with much better functionality, and usually having a very good product by version 3. Breaking this pattern caught a lot of people by surprise. Version 1, while lacking in a few key areas, was definitely usable. Some of Microsoft’s customers are using it in production, and even more partners had made significant investments in it. Fortunately, while Mariner had done some work with it, we had not invested heavily in it. We were more focused on the Monitor and Analyze portions of the product. In part, this was because we recognized that performance management is a specialized discipline, requiring some specific skill sets. Just because you can deliver successful solutions on Microsoft technology doesn’t necessarily mean that you can deliver successful performance management solutions. I think that was a point of confusion for many partners (the “one stop shop” approach is very popular in the partner community), and that lead to Microsoft not having as strong of a partner base to support the product as they had hoped. On the other hand, there were some really strong partners in the performance management space who did some great things with Plan, and I can certainly empathize with those that made big investments and are now disappointed by the change in direction.

Mauro Cardarelli, a SharePoint MVP, had an interesting post on his concerns that making PerformancePoint available as part of SharePoint raises the same concerns. Competent delivery of SharePoint solutions doesn’t necessarily correlate to competent delivery of BI functionality, and successful delivery of BI solutions doesn’t mean that you can deliver good SharePoint solutions. Since this was one of the challenges for Plan, it will be interesting to see how it plays out going forward. In the short term, I’d encourage companies to be sure that their vendors either have both sets of skills (and can demonstrate that they’ve used them in the same project), or look for best-of-breed partners who are willing to work together.

The full-client story is a concern. The current direction seems to be for Excel to become the full client for consuming Analysis Services data, and for SharePoint to become the thin client interface. I’m definitely in favor of SharePoint as the thin-client interface, but using Excel as the full client leaves a pretty big gap in the story. It used to be that you could recommend ProClarity desktop to fill that gap, but since ProClarity is in a support only mode now, that’s not a good option. In time, more of the functionality of ProClarity should surface in Excel and SharePoint, but that’s still some time off. And Excel, while improving as an Analysis Services client, is still not on par with a dedicated desktop client built to expose the full functionality of Analysis Services. Hopefully that will improve over the next couple of releases of Excel, but in the meantime it creates opportunities for third parties to fill the gap.

Overall, I think this move will promote broader adoption on the Monitor and Analyze functionality in Microsoft’s customer base, and will strengthen the value proposition for moving to SharePoint Enterprise licenses. It’s a good thing for Microsoft, and good for customers who have already invested in SharePoint. However, it remains to be seen what impact not having a planning component or a strong full client application in the BI stack will have.

Some other reactions from around the web:

Chris Web (he also has a set of links featuring other reactions)

Nigel Pendse

Cindi Howson

Managing Scope for Iterative Development

2009-01-19T20:23:50Z

As I mentioned in my previous post, defining an appropriate scope is difficult when you are attempting to do iterative development on BI/DW projects. The problems with this are not unique to BI/DW projects, of course. All projects have scope issues. Most of the same scope management techniques that work well on traditional application development projects also work for BI/DW projects. There are two unique aspect of scope for BI/DW projects, though. One is that there is often significant work required “behind the scenes” to deliver even small pieces of visible end user functionality. The other is that there can be significant “hidden” scope in properly cleansing the data and making it useful from a business perspective. This can be challenging because the end users may have a perception that they are not getting very much functionality, particularly in the first several iterations of the project.

What are some of the hidden aspects of BI/DW projects? ETL and data profiling are two of the most common. I consider these hidden because the end users of a BI application rarely are intimately involved in the ETL process development. They may be involved in the data profiling, but often are not involved in the data cleansing that usually accompanies it. These are time-consuming activities that the users only get to appreciate indirectly, so they often don’t put very much value on it.

How can this be addressed? I’ve found that there’s two parts to it. First, you need to do a certain amount of education with the stakeholder on what happens behind the scenes, so that they have a better understanding of the effort that has to be expended. The benefits that they get from this effort need to be explained as well. Telling them that ETL processes are time-consuming to implement isn’t very effective unless you also explain the benefits of well-implemented ETL: cleaner, consistent, conformed data, with appropriate controls to verify that the right data is going into the data warehouse, and the bad data is being excluded.

However, education is not enough by itself. The second part of it is to show them that they can get incremental benefits. Again, as pointed out in the previous article, each iteration should deliver something of value to the users. It’s important to do this from the first iteration on, and to continue to do it consistently. One effective way to determine what would be of value for an iteration is to ask the users to decide on one or two questions that they want to be able to answer. The scope of the iteration becomes delivering the information that allows them to answer those questions. But what if the questions are complex, and you don’t feel that they can be addressed in a single iteration? I’ve generally found that if the questions are that complex, you can break them up into smaller questions and negotiate with the stakeholders to deliver them over multiple iterations.

This does require that the development team on the project has an agile mindset, and is focused on meeting the deliverables for the iteration. It also poses a more significant challenge when a project is in an initial phase, and the infrastructure is still being put in place. I’ll discuss this challenge further in my next post.

In conclusion, scope management is important on all projects, not just BI/DW projects. However, perceptions of scope may be more challenging on BI/DW challenges, because of the hidden nature of some of the activities. It’s important to communicate the value of these activities to the project stakeholders, and to demonstrate that you can consistently produce incremental deliverables, while still carrying out these valuable activities.

As always, comments and feedback are welcome.

Challenges to an Iterative Approach to Business Intelligence

2009-01-09T01:24:04Z

I’m a fan of agile development. Prior to focusing on business intelligence and data warehousing, I architected and developed client server and n-tier applications, and I found that agile development techniques delivered better end results. I believe that, in large part, this came about because of the focus on smaller, functional iterations over a more traditional waterfall approach. However, this approach is still not regularly used on BI projects. Since it can have a big impact on the success of your BI initiatives, I’d like to list some of the challenges that prevent adoption of this approach. I’ll go into more detail on each of these in my next few posts, and also cover some of the benefits you can see if you overcome the challenges.

First, though, let me define what I mean by an iteration¹. An iteration is a short development cycle that takes a specific set of requirements and delivers working, useful functionality. Generally, I think the duration of an iteration should be 2 weeks to a month, but I don’t consider that an absolute rule. More than 2 months for an iteration, though, really impacts the agility of the project, so I prefer to keep them shorter. Delivering working functionality in an iteration doesn’t necessarily mean that it has to go to production. It does mean that it could go to production, if the project stakeholders choose to do that. Delivering useful functionality means that what’s been developed actually meets a stakeholder’s need.

There are a number of challenges that you might encounter in doing iterative development for BI. Fortunately, you can work around these, though it’s not always easy.

Scope
Many BI/DW initiatives are large and involve multiple systems and departments. Even smaller BI projects often have multiple audiences with differing needs and wants. Managing the scope of the effort appropriately can be challenging in that environment. This means that defining and managing scope is critical to successful iterative development.
Foundation development
Particularly when BI projects are getting off the ground, they need a lot of foundational setup – environments, software installations, data profiling, and data cleansing. This poses definite problems for the first couple of iterations, especially when you take into account the guideline of delivering working and useful functionality with each iteration. The foundation needs to be built in conjunction with delivering useful functionality.
Existing infrastructure and architecture
Iterative BI development requires an infrastructure for development that is flexible and adaptive, and it makes it much easier if existing solutions were architected to be easily modified. Sadly, this is not the case in many organizations. Existing process and infrastructure tends to be rigid and not support or encourage rapid development. Existing data warehouses tend to be monolithic applications that are difficult to modify to address changing business needs. And many BI development tools do not provide adequate support for rapidly changing BI applications.
Changing requirements²
Changing requirements is a fact of life in most IT projects, and it’s definitely the case in BI/DW projects. While agile and iterative development can help address changing requirements, it still poses a challenge. As requirements shift, they can have a ripple effect on the BI infrastructure – adding a new field to a report may require changes to the database and ETL processes in addition to the report itself. This can make seemingly inconsequential changes take much longer than expected, and lower the adaptability of the project.
Design ²
Due to the scope and complexity of many BI/DW initiatives, there is a tendency to get bogged down in design. This is particularly true when you consider that the database is a key part of BI/DW projects, and many BI developers feel most comfortable having a complete and stable data model before beginning development (which is perfectly reasonable). However, design by itself does not produce working functionality, so an iteration that involves nothing but design doesn’t really meet my definition of iterative.

After looking at all these challenges, you may feel that it’s pointless to try iterative development on BI projects. However, I’m happy to say that all these items can be addressed (granted, some of them are much more easily fixed than others). If you do make some changes, you can get a number of benefits, including the ability to rapidly accommodate change, deliver working functionality more quickly, and facilitate emergent requirements and design. The end result? Happy project stakeholders who are more satisfied with the results of their projects, and less wear and tear on the BI developers. Everybody wins!

Over the next couple of months, I’ll be posting more information about the challenges, and how you can work around them. Please keep reading, and if you have questions or comments, don’t hesitate to get in touch with me.

Notes
1. If you are familiar with Scrum, you will notice some similarities between what I describe as an iteration and a sprint. Scrum has influenced my thinking on agile methodologies quite a bit, and I’m a big fan of it. However, because I don’t follow the definitions in Scrum rigidly, I’ve found it better to not use the same terminology to avoid confusion. If I do refer to a sprint, though, I will be referring to the Scrum definition (a 30-day increment, resulting in potentially shippable product).
2. Please don’t take the above to mean that I don’t believe in requirements or design – I feel that both are vital to iterative development. However, the approach that many BI practitioners take to requirements and design does not lend itself to iterative development.

Welcome!

2008-11-26T18:44:00Z

Welcome to BIpartisan, a new blog that will focus on Business Intelligence and data warehousing. That's a pretty broad area, though, so this blog will focus in on two particular aspects of business intelligence: bridging the gap between IT and business, and delivering value from BI initiatives more rapidly through the use of agile techniques. Particularly given the current economic situation, these two areas are very important to your continued success in the business intelligence space.