From Data to Insight: Addressing the Last-Mile Problem

April 23, 2013 by Leave a comment

Prakash Aditham

Ask any amateur runner or triathlete and they’ll tell you that the last mile (or last several) of any endurance event is the most challenging and difficult one to cover. In the networking world, the last-mile problem refers to the speed bottleneck which limits the bandwidth of data that can be delivered to the customer. Within the information delivery network, as data travels from the source system to the data warehouse and approaches the last-mile transformation to actionable intelligence, we notice problems as well. Analysts struggle with data issues and decision-makers continue to grumble about having to make decisions with insufficient information.

As a business leader or decision-maker trying to increase the number of actionable insights, it’s beneficial to do some root-cause analysis and focus on the three major causal categories affecting the last mile: people, platform (equivalent of equipment and materials), and process. Read more of this post

Filed under Business Intelligence, Uncategorized Tagged with , , , ,

Telling Even Better Stories with Microsoft BI and GeoFlow

April 11, 2013 by 2 Comments

Marek Koenig specializes in Business Intelligence, SharePoint and Custom Development.

Marek Koenig

Last December I wrote about Microsoft’s add-in to Excel called GeoFlow, an application that allows users to analyze geographic and temporal data on a globe. Using 3-D data visualizations, GeoFlow enables data discovery that may not be possible in standard 2-D charts and tables. The first post I wrote about GeoFlow covered a pre-released version, which had some basic features and functionality. Now Microsoft is proud to introduce a public preview of GeoFlow, which comes with a host of improved features and functionality. In my first post, I used GeoFlow to build a narrative around ticket sales’ data at the Seattle Center. We’ll continue with that same data set here. Read more of this post

Filed under Business Intelligence, Microsoft Tagged with , , , ,

Innovating Today’s Insurance Industry

March 1, 2013 by Leave a comment

Slalom Consultant Denis Farmer

Denis Farmer

With the continued growth of motorcycle insurance for property and casualty (P&C) insurance companies, it’s more critical than ever for carriers to develop sophisticated products that ensure long-term profitability and market competitiveness. To meet this need, Slalom Consulting has developed a flexible Motorcycle Symbol File, specifically designed to help improve products, pricing, underwriting, and the loss experience for insurance carriers that offer insurance for motorcycles, snowmobiles, and ATVs. Read more of this post

Filed under Business Intelligence, Financial Services Tagged with , , , ,

Creating a Successful Data Governance Program

February 18, 2013 by Leave a comment

Eniko Tucker is an Information Management Consultant in Slalom Consulting’s Chicago office, focusing on Data Governance and Master Data Management. With over 11 years of work experience in internal auditing and data management, Eniko is helping to grow the firm’s Data Governance and MDM practice and support local companies’ enterprise information needs.

Eniko Tucker

Imagine a beauty pageant where data governance showed up amongst supply chain optimization, offshoring, platform growth, and other major contenders. Would the audience treat it like an alien creature, or welcome it with open arms and award it the crown?

Even though data governance has been around since at least the 1970s, the vast majority of today’s companies have not invested in data governance program implementations. In our estimation, only a small percentage of companies have considered a strategic initiative around data governance, and an even smaller percentage of these companies actually have a thriving data governance practice in place. Read more of this post

Filed under Business Intelligence Tagged with , , , ,

Developing a Successful Analytics Strategy

February 8, 2013 by Leave a comment

Carrie is the Practice Area Lead for Information Management at Slalom Consulting. With nearly 20 years of experience delivering business intelligence solutions, Carrie now specializes in Advanced Analytics. Utilizing quantitative approaches to uncover actionable insights based on consumer behaviors, attitudes, perceptions and attribute, Carrie has serve as a trusted advisor to help clients assess their current analytic and data infrastructure challenges and then implement the architecture, processes, and organizational requirements needed to address those business challenges.

Carrie is the Practice Area Lead for Information Management at Slalom Consulting, specializing in Advanced Analytics. With nearly 20 years of experience delivering BI solutions, Carrie helps clients leverage quantitative and qualitative results to meaningfully drive their businesses.

As a major part of our work within the Advanced Analytics Practice at Slalom Consulting, we guide our clients toward the right balance of activities, information, and insights that allows the experience and intuition of the decision-makers to be guided by appropriate information and quantitative analysis. This balance sits at the intersection of finance, operations, sales, and marketing, and encompasses the broad field of advanced analytics, which is currently getting an immense amount of attention in the business press. With this new awareness comes much hype and confusion about how to effectively execute an analytics strategy. From our work with hundreds of clients across many industries, we have found six essential considerations for developing a successful analytics strategy.

Establish Common Definitions to Deliver Consistent Information

While the finance team specializes in categorizing and defining information consistently, marketing (and to a lesser extent, operations), often uses less robust definitions. Metrics such as “awareness” for advertising or “footfall” for store operations are not necessarily defined or captured in the same way in each instance. Bringing together a business’s core groups to adopt a common set of metrics, agree upon how to govern the collection, and determine how it is used will help ensure smoother cross-team communication.

Business activities around promotion, marketing, competitive impact, and operational events are not necessarily captured in the same fashion as general ledgers or sales transactions. However, to have a consistent view of internal and external information, these data points must be captured with a degree of care similar to that used with key enterprise assets.

Apply Business Knowledge to the Process

Insights are most valuable when they leverage the experience of the team along with a broader set of trusted internal and external findings. The most successful teams will include representation from finance, operations, sales, and marketing, all communicating in a common language and with shared understanding. Knowing how data is collected and having specific business rules in place for analyzing that data is critical to understanding statistical insights.

Advanced analytics are only useful for making solid business decisions when all three legs of the stool are in place: data (internal and external), business and domain knowledge, and statistical models.

Select and Capture the Appropriate Metrics

In today’s global enterprise, there is no shortage of financial, operational, sales, and marketing metrics. However, we see wide variation in the discipline and use of this metrics. A study by Currim and Mintz at the University of California Irvine shows that an appropriate use of combined financial, operational, and marketing metrics can be a significant predictor of business performance.

The study indicates that higher-performing companies use a two-to-one balance of financial to marketing metrics, whereas lower-performing companies overweigh their financial metrics. Interestingly, there is a wide variance in the use of metrics by type of marketing activities. Most firms, regardless of their performance quartile, measure pricing or promotion performance. However, companies in the lower ranges of performance typically don’t measure social media or public relations, while top-quartile performers measured at least four key metrics in those areas

Keep It Simple and Experiment Often

When trying to be more analytical, companies often fall into the trap of adding unnecessary complexity. This is often driven by the natural risk avoidance inherent in large organizations, coupled with a reliance on quantitative analysts tasked with finding one “right” answer. As with most things in life, business decisions can seldom be delivered by one complex model that returns the perfect response. The most successful decision-making models often operate with the smallest number of factors possible to arrive at a reasonable answer.

Promote a Culture of Sharing and Storytelling

Insights are not very useful unless shared with the entire decision-making team. It’s natural to sometimes hold on to knowledge that may give one a perceived internal advantage, but this short-term thinking does not allow an organization to grow analytically. Encourage sharing within your organization by using stories. Create a story on the front end to generate hypotheses, and on the back end to help craft the final message to your audience. In the middle, use research methods, data, and statistics to quantify, evaluate, and modify the thinking.

Rewarding team members who not only create insights by leveraging new data sources or models, but also then sharing those insights with others in the organization will help foster teamwork and morale. Team thinking is essential to not only capitalize on the collective wisdom of the group, but also to allow your best analysts to move on to new areas of exploration.

Take a Measured Approach

The evolution of the decision-making process must be undertaken at an appropriate pace. Trying to displace too many decisions with quantitative or scientific models too quickly will invariably backfire. Organizations must have the time to allow analysts and managers to learn from the newly available information and to supplement their current processes before undertaking new ones.

In working with our clients, we help them keep these considerations in mind as they craft their analytics strategy. Striking the right balance here results in a strategy that maximizes the organization’s return on their analytics investment.

We would like to hear your thoughts regarding this approach to building an analytics strategy.  What do you think is the most important consideration?

Carrie Steyer is a member of Slalom’s Information Management Thought Leadership Committee. For more information, email the team at NationalIMThoughtLeadershipCommittee@slalom.com.

Filed under Business Intelligence Tagged with ,

Eleven Game-Changing Advances in Microsoft BI

January 21, 2013 by 2 Comments

OceansElevenOneMore

Ten oughta do it, don’t you think? You think we need one more? You think we need one more don’t you? Ok, we’ll get one more.

I came home from the Microsoft SharePoint Conference 2012 fired up about all of the changes in the way Microsoft approaches BI, which to me are the most sweeping changes since the release of SQL 2005.

Like SQL 2005, I think that these up and coming technologies will change the way Microsoft delivers BI, in ways which may not be obvious yet but will emerge over the next three to five years. For example, when I recommended in 2007 that my company use Excel to deliver ad hoc reporting instead of a standard BI ad hoc solution like WebI, my CEO thought I was nuts. Even I didn’t understand at that time how integral Office would be to BI delivery, so much so that I’m questioning the future of SSRS (more on that in a minute).

I thought this would be a great time to identify emerging trends along with some bold (and not so bold) predictions about how these latest advancements will change Microsoft BI delivery. I asked my colleague Patrick Brady to join me in writing a list of some of these predictions, since he had recently been to PASS 2012 as a returning participant and a speaker. We compared notes to create a top-ten list of game-changing features and possible upcoming trends that we could write about. After paring down the list, we found we couldn’t do less than eleven without feeling like we were leaving something important out.

So with that, here are eleven game-changing advancements in Microsoft BI and some thoughts on what the future may hold.

Big Data

Big Data is a Big Mystery to a lot of CIOs, other than those in industries like e-commerce and social networking who have been at the forefront of understanding and advancing these concepts. However, the use of Big Data applications and related opportunities will expand and grow further in 2013 as more organizations finally begin to understand the scope of this new technology and realize the value of being able to capture and analyze large volumes of ever-changing unstructured and semi-structured data. As part of a strategic partnership announced in late 2011 with HortonWorks, Microsoft recognized early on the emerging market opportunities surrounding Big Data and have been busy creating HDInsight Server and Windows Azure HDInsight Service.

HDInsight is certified by Microsoft to run Hadoop on Windows Server and will suit organizations that want a dedicated on-premises big data implementation. Azure HDInsight Service provides Big Data as a Service (BDaaS) in the cloud to those organizations with occasional to frequent needs. Both products promise a simpler Big Data entry point for those organizations that have been sitting on the sidelines up to this point.

Social Analytics

Social Analytics can be defined as the process of analyzing customer sentiments through the mining of data available from social networks such as Facebook, Twitter, Tumblr, LinkedIn, Google+ or private social networks such as Yammer. Its value can be demonstrated by showing how to use social analytical techniques to support marketing activities, assist with customer support and identify opportunities for future product development.

With Microsoft’s acquisition of Yammer, coupled with the SQL Azure Lab named “Social Analytics,” the question must be asked: what is Microsoft planning in this space? This question further begs an answer when consideration is taken into account of Yammer’s recent partnership announcement with Kanjoya, a vendor specializing in sentiment analysis. All signs point to upcoming announcements in 2013 from Microsoft regarding specific product offerings related in Social Analytics.

PowerPivot Gallery

PowerPivot is not a new technology, as it was first introduced as part of SQL 2008 R2 (see below for more on in-memory analytics). Even PowerPivot Gallery is not new as it was introduced as part of SharePoint 2010. However, the PowerPivot Gallery benefits from multiple enhancements in SharePoint 2013 that greatly improve the ease and usability of the PowerPivot Gallery.

PowerPivot is now a built-in functionality of Excel Services. You no longer need to install separate instances of PowerPivot for SharePoint. Designating an SSAS tabular instance in the data model settings of Analysis Services, you can enable your PowerPivot Gallery. You now also have the ability to drag and drop PowerPivot files into the gallery, much like you can now use drag and drop for other documents in SharePoint. Also, using a Business Intelligence Semantic Model (BISM) is no longer the only possible source for a Power View report. You can build a Power View report off of existing PowerPivot documents in the gallery.

By properly training the information workers in your organization, the PowerPivot Gallery will enable your end users to create powerful in-memory visualizations without requiring report building from IT.

GeoFlow in Excel

Microsoft has mostly focused on utilities that support geospatial reporting, such as the company’s geography/geometry data types and the extensibility of Bing Maps. Other companies such as IDV Solutions—the creators of Visual Fusion­—have utilized Microsoft technologies to make geospatial reporting possible. But at the SharePoint Conference 2013, Microsoft proudly introduced GeoFlow, an Excel add-in that takes advantage of Bing Maps and the same xVelocity technology as PowerPivot to produce 3D, interactive, data-driven maps within Excel.

While GeoFlow is a bit limited at this point, you can’t beat the price (included free with Excel 2013 or higher) or its ease of use. It doesn’t have all of the power that Visual Fusion has with its XML scripts and Silverlight SDK capabilities, but the developers who introduced the project are very enthusiastic and we expect the capabilities will expand rapidly.

Our colleague Marek Koenig wrote a great descriptive piece on GeoFlow, which you can access here.

Excel Services Improvements

Excel Services has been around ever since Microsoft Office SharePoint Services 2007 (MOSS), but has been an underutilized part of the Office/BI revolution. The selling point sounds great: users can create their own reports and share them online, and you don’t need to have Excel installed. That was usually met with a look that said “who doesn’t have Excel?” And the amount of interaction you gave up usually made Outlook the sharing technology of choice for connected Excel documents.

Excel 2013 introduces Excel Interactive View and gives the user the ability to view multiple worksheets, interact with data, and build charts and graphs in an HTML client. Once the information workers understand these major increases in features and flexibility, you can anticipate Excel Services to take off the way many thought it would in 2007.

As interactive reporting features continue to be added to an already very easy-to-use platform, I could see SSRS being featured less prominently in Microsoft’s BI stack in the future.

Visio Services

Most people think of Visio as just a great tool to draw flow diagrams and org charts. And when Visio Services was created, most people thought of Visio Services as just a way to render flow diagrams and org charts online. Prior to 2013, you could only use data linking to connect to data graphics in Visio and Visio Services. However, in Visio 2013 and Visio Services 2013, you can now connect data to shape properties such as size and position, visibility, color, and geometry. You can even create or import custom shapes with custom properties that can also be connected to data.

For example, during Chris Hopkins’ presentation at SharePoint Conference 2012, he showed a retail example using a floor plan of a store, and circular racks which would be more or less fully based on inventory data residing in a database. To a retail buyer or merchandising coordinator, nothing spurs action like the image of an empty rack. You can also use hyperlinks in your shapes that allow you to drill to reports or other dashboards to get more detailed looks, now that you have the user’s attention.

You can also perform high-level customizations using Vwa Namespace in the JavaScript object model, or use the Visio Services class libraries to make custom data connections. You can find a listing of new Visio Services features for 2013 by clicking here.

Parallel Data Warehousing

In 2010 Microsoft shipped SQL Server 2008 R2 Parallel Data Warehouse (PDW), its first enterprise-class parallel data warehouse appliance. After a number of updates to the product, Microsoft will release its next generation PDW appliance in the first half of 2013. SQL Server 2012 Parallel Data Warehouse is widely expected to include a redesigned architecture with significant improvements, including greater performance and a reduced hardware footprint at a lower cost. Also, as a result of the growing need to integrate relational database data with big data sources such as Hadoop, this latest version of PDW will include one significant enhancement: PolyBase. PolyBase will enable analysts with the ability to query data from both relational databases and Hadoop using a single unified query statement. It promises to reduce much of the complexity associated with accessing Hadoop data and its integration with traditional relational-based data during analysis.

So with the continued need for faster analytics of ever-growing volumes of data and the rapidly growing emergence of Big Data, along with a lower-entry cost point than offered by its competitors, Microsoft’s SQL Server 2012 Parallel Data Warehouse is expected to become a more common cornerstone of BI department solution offerings across corporate enterprises during 2013, and beyond.

In-Memory Analytics

Continuing on the success of its development of in-memory data analytics such as that used in xVelocity, Hekaton (the Greek word for 100 times), is Microsoft’s new in-memory technology for online transaction processing (OLTP) databases. Similar in implementation to xVelocity technology, Hekaton employs compression techniques that promise to greatly increase the speed of data processing in transactional application databases. Note that Hekaton is a project code name and is expected to be released in the next version of SQL Server. Few details are available, but one notable item that we do know is that developers will have the option to select either tables or entire databases to host in-memory. We should be hearing more about Hekaton from Microsoft as the year progresses.

Mobile BI

With the emergence of smart phones and the advent of tablet computing, it only seems natural that business users should be able to access BI analytics while on the go using their mobile devices. To date, there have been some notable players who have proved successful in the Mobile BI market (RoamBI comes to mind), but it seems that up until recently, Microsoft has mostly ignored this important aspect of business intelligence. To a certain extent this changed with the release of SQL Server 2012 Service Pack 1 in November 2012. The service pack included a new feature enabling Reporting Services reports to work better interactively on iOS enabled devices. In addition, Microsoft recently provided a demonstration of its new and yet to be released Mobile BI solution named “Project Helix” at SharePoint Conference 2012.

Unfortunately there is currently very little information available on “Project Helix” other than what has been reported from tweets and blog posts resulting from the demonstration shown during SharePoint Conference 2012. Regardless, it would appear that Microsoft has turned a corner and definitely has plans for mobile BI. More will be revealed as 2013 progresses.

Cloud BI

Windows Azure SQL Reporting was made available during a spring 2012 preview, with its pricing model going into effect August 1, 2012. Still, talk of Azure Reporting was quiet both at both the SharePoint and PASS conferences compared to other technologies. Many people who have used Azure Reporting have found it difficult to set up and somewhat limited in its offering; for example, it does not provide a semantic layer comparable to Analysis Services. Some have also felt like the per-hour pricing model wasn’t for them.

For many companies, having a BI server on-premises will make sense for them, as native connectivity to SQL Azure has increased with the release of SQL Server 2012. While this may seem to defeat the purpose of hosting on the cloud, smaller organizations that mainly use their BI internally will find that the hardware costs of hosting Analysis Services and Reporting Services are not prohibitive if the larger OLTP and data warehouse layers are hosted in SQL Azure.

And since this is a post about predictions, we predict there is more to come in Microsoft cloud BI in the next few years, especially with Microsoft’s strategy of releasing many new features to Azure first.

SharePoint/Office Apps and the Apps Store

If you open Excel 2013, you will notice that “Blank workbook” is just one of the first options that hit your screen. You also have the option to open a host of other templates, such as “My financial portfolio” or my personal favorite, “Weight-loss tracker.”

Don’t adjust your set; these are actually Excel 2013 apps. Apps are new in the world of Windows 8 and Office 2013, and more or less replace the idea of add-ins. Architecturally, they involve HTML5, CSS3, and JavaScript and use OAUTH, REST, and other web protocols to connect to the apps which are hosted either on premise or in the cloud. These services can also call up additional hosted or third-party data (including Microsoft-provided data sets) and integrate it into the apps delivery. These apps are made available on the Windows App Store (sound familiar?) with a model that allows the developer to take in a substantial share of whatever revenue they generate.

So what does this mean for BI? The idea of writing code to create “reporting tools” might have seemed as foolish before as using code to write a new spreadsheet application. However, this simple architectural model combined with the reporting improvements in Excel and the rest of Office will make it easier to write simpler, targeted, easily customizable reporting apps delivered in Office and SharePoint. Combining that with the distribution ease of the Windows App Store, we believe SharePoint and Office apps will play prominently in BI delivery in the coming years.

Slalom Consulting Solution Architect Patrick Brady was a co-contributor to this post. These authors are members of Slalom’s Information Management Thought Leadership Committee. For more information, email the team at NationalIMThoughtLeadershipCommittee@slalom.com.

Filed under Business Intelligence, Cloud, Microsoft, Mobile Tagged with

A BI Recipe for Success with Surface RT

January 14, 2013 by Leave a comment

SurfaceThe Surface with Windows RT devices have been roaming the streets for several weeks now. More than a few companies are trying to get ahead of how this type of device is going to be incorporated into their IT landscape, and Business Intelligence departments are often at the front of the line. We have put together a little recipe to enable interesting and useful Business Intelligence delivered via the Surface RT.

Mobile devices are primarily consumption devices. Windows 8 changes the equation, but it does not change the fact that when people are on the go—without large monitors, traditional keyboards, and mice—they are going to skew more toward consumption than creation.

Clearly mobile devices are becoming more and more prevalent. There is a fantastic tie-in here to business intelligence. The information provided through BI does not add any value if it is not consumed. Gartner predicts by 2013 a third of all BI functionality will be consumed on mobile devices. I am not sure what point in 2013 they expect to pass that threshold, but happy new year!

I am excited for the reinforcements in mobility that Windows 8 naturally brings to the Microsoft BI stack. However, as a fan of PowerPivot I was a tad sobered to see this blog post by Analysis Services guru Kasper de Jonge. PowerPivot is not available on Windows RT. And for that matter neither is Silverlight and, therefore, Power View. Kasper does remind us that Excel on Windows RT will still create pivot tables and can connect to a Business Intelligence Sematic Model (BISM) service. That is useful for some lightweight analysis within the Excel App with an online data source.

However, if you are connected, which you would need to be anyway to take advantage of a BISM multidimensional cube or tabular model data source, why not take it a step further and let the cloud do as much work for your consumers as possible? That is what we have done with the following recipe.

A few simple, out-of-box ingredients:

  • One fresh Surface with Windows RT (for the consumer)

The Surface offers fantastic new hardware. Discussion and reviews are bountiful, so we will not go into too much detail here.

  • A handful of SharePoint Online 2013 Enterprise with Excel Services (for the provider)

This is the secret ingredient. Using SharePoint Online makes this a fully fledged cloud-based solution which plays nicely with those mobile devices, and their users, that refuse to sit still. Users  even have the option to expose a pivot table field list and adjust the report on the fly.

Excel Services in SharePoint 2013 include some exciting new features, like Quick Explore, which empowers drill-down and enables real ad hoc data interrogation for power and regular users. To keep the data up to date, we’ve seen success leveraging a script to refresh workbooks hosted with SharePoint Online.

  • A dash of Excel 2013, or in a pinch Excel 2010 with PowerPivot (for the report author)

Authoring reports in Excel, with its strength in data source compatibility, allows all of the data modeling and authoring to be done in the world’s most popular analysis tool. Report authors need not be developers and the cost of creating new reports drops to new lows. Through the use of finger-friendly slicers, charts, and pivot tables you can easily provide users with an experience that feels nothing like looking at a spreadsheet.

Surface BI SharePoint Online Demo Small

This is a Surface RT screenshot, straight from Internet Explorer. From portal to report: long press for the pivot table field list and quick explore drill-down right in the graph or table.

We have found that mixing these ingredients creates a dish that delivers some hearty BI morsels.

To be fair, a browser-based delivery mechanism is not unique to the Surface RT. In fact, quite the opposite is true. Providing users with the scrumptious data and visuals they crave through the browser is about as cross platform and cross form-factor as you can get. This is—mostly—a good thing. The only reason to qualify here is that you’ll need to consider the variety of mobile and desktop devices (as well as their screen sizes and aspect ratios) that your users will leverage to access reports. If you are able to target a specific device, or use creativity to steer users to a specific version of a report based on a specific device, then make your reports shine for that device. However, we recommend balancing your time between crafting perfectly placed pixels and creating a generic report that offers rich insights on a variety of devices.

What makes the Surface RT useful as a BI delivery tool, particularly in this recipe, is really Excel Services on SharePoint 2013. The half-second press and hold gives you touch access to Quick Explore drill-downs along with the power to edit the pivot via the “Show Field List” option. With SharePoint Online supporting Excel Services, you can easily create a 100% cloud-based BI solution. Like all recipes there are modifications which you can make to better suit your needs. For example, if you need to support larger data sets (more than a few million rows) or are looking to leverage some of the more advanced features in SharePoint 2013, then running the on-premises version of SharePoint server will allow you to scale that processing capability and feature set as needed. The screenshots below are based on a data connection to Salesforce CRM (but could be Microsoft Dynamics or almost anything).

Bon appétit!

Slalom Intelligence

Side-by-side comparison of the Surface RT and iPad.

Slalom Consulting PowerPivot Architect Barbara Raney was a co-contributor to this post. These authors are members of Slalom’s Information Management Thought Leadership Committee. For more information, email the team at NationalIMThoughtLeadershipCommittee@slalom.com.

Filed under Business Intelligence, Microsoft Tagged with , , , ,

Thinking About Data

December 19, 2012 by 1 Comment

“Though the mills of God grind slowly, yet they grind exceeding small…” Henry Wadsworth Longfellow translated this famous line from a German poem written by Friedrich von Logau in 1654.  Imagine if Longfellow worked as a data architect in today’s Information Technology industry.  Perhaps he would have written this now famous line as follows: “Though the databases of God grind slowly, yet they grind exceedingly small.”

This is often how I feel when I begin investigating a database to diagnose performance problems or to start documenting the schema and constructing ETL process to populate a reporting database or data warehouse.  As data modelers, data architects and database developers- all of whom I will collectively refer to as database people for the remainder of this article-we are taught to think about data relationally and dimensionally. Relationally we normalize data for production OLTP databases, organizing it in such a way as to minimize redundancy and dependency.

Dimensionally we design data warehouses to have facts and dimensions that are easily understandable and provide valuable decision support to various business entities.  High quality, reliable data that is easy to query and consume is the goal of these design patterns.  During my twenty year career, however, I have discovered that well-designed database schemas and data models are the rare exception, not the rule.  And there are a couple of common themes underpinning the bad designs I encounter.

Poorly designed databases are often the result of designers who instead of thinking about how data will flow through their databases and more importantly, which people and internal business entities will want to consume that data, simply view databases as a convenient resting place for data.  This erroneous view frequently stems from a lack of formal training in database normalization, relational algebra, dimensional modeling and data object modeling; skills that I believe are essential for anyone serious about enterprise level database design.  The database schema is foundational to almost every business system, so it is imperative to involve skilled database people early in the design process.  Failure to do this may result in flawed database schemas which will suffer from one or more of the follow issues:

  • Lack of extensibility
  • Difficult to maintain
  • Lack of scalability
  • Difficult to query
  • Contain a high degree of data anomalies which render the data unreliable
  • Performance problems

Having worked with a lot of weird and strange database designs-I could probably write an entire book on the subject- I want to briefly mention some of the more commonly encountered database design errors.  And I am going to classify these errors into two general groups: Design errors in production OLTP databases and design errors in databases intended for reporting.

Production OLTP databases

  1. A completely flattened, un-normalized schema. Databases designed this way will have all sorts of issues when placed under production load; performance and scalability for example. I often hear this line from developers, “Well it worked fine in QA with a small amount of data, but as soon as we deployed to production and threw serious traffic at it, all sorts of performance problems emerged.”  Flattened schemas like this frequently lack declarative referential integrity which leads to data anomalies. Getting reliable reporting from this environment is difficult at best.
  2. A highly normalized schema, possibly over-normalized for the context, but lacking in sufficient/useful payload.  Every part of the data model has been taken to its furthest Normal Form and data integrity is well enforced through Primary Key/Foreign Key relationships. But getting useful data requires both excessive table joins and deriving or calculating needed values at query time. For example, I worked on the invoicing portion of an application-the invoicing code was written in PERL-in which customer product usage was calculated during the invoicing process.  The final usage totals were printed on the paper invoices, but not stored anywhere in the database.  Finance needed this information, both current and twelve months historical and to get it, I had to recreate large parts of the invoicing process in SQL; a herculean task.  When I inquired of the developers as to why customer usage invoicing totals were not stored in the database they responded as follows, “I guess we never though anyone would want that data.”

Reporting databases intended to serve internal business entities

  1. Attempts to build internal reporting functions directly against production OLTP databases.  A discussion of OLTP optimization techniques vs. OLAP optimization techniques is beyond the scope of this article. But suffice it to say that attempts to run I/O intensive reporting against a production OLTP system which is not optimized for reporting will cause tremendous performance problems.
  2. Building out a secondary physical database server and placing an exact copy of a production OLTP database on it as a “reporting instance” database.  Doing this will certainly remove any chance of overwhelming the production server.  But it will not provide a database optimized for reporting.
  3. Adding a few somewhat haphazard aggregation tables to the “reporting instance” database mentioned above.  This may temporarily reduce query times for reports relying on aggregated data, but it is not a long-term substitute for a properly designed dimensional reporting model.

Data models are often given short shrift because the original developers, being inexperienced with relational and dimensional databases, do not think correctly about data. This error in data thought frequently results in poor database designs which may perform poorly and contain unreliable data that is difficult to query.  I want to leave you with a specific example of this point by briefly relating a somewhat recent client experience of mine.

My client at the time had recently purchased a start-up company whose product was a complex ad serving application/engine.  The SQL Server databases foundational to the application were suffering severe performance problems which rippled through the entire system and resulted in a less-than-stellar customer experience.

At my client’s behest I executed a major system review and quickly ascertained two primary issues; a data architecture that limited scalability and incorrect table indexing which was a direct result of the architecture. I kept their developers and Program Manager involved with my solution development process and after a successful deployment, which solved their performance issues, the program manager made a key statement to me. She said, “Wow, I never understood the value and importance that a person with strong data architecture and DBA skills could bring to project like this.  You can be certain that on all future projects of this magnitude I will insist on, and budget for a person with your skillset to be involved at the outset to ensure we avoid these types of database issues.”

Every IT, Program and Project Manager would do well to heed her advice.  Consider spending some time with your recruiting department to find an experienced data architect with a successful track record at the enterprise level.  It will be time and money well-spent.

Filed under Advanced Infrastructure Services, Business Intelligence, Custom Development, Portals & Collaboration Tagged with , ,

Tell Better Stories with Microsoft BI and GeoFlow

December 17, 2012 by 8 Comments

Marek Koenig specializes in Business Intelligence, SharePoint and Custom Development.

Marek Koenig

Storytelling seems to be a lost art form; in this fast-paced world people are more interested in getting as many bits of information as they can. But without a story, something to pull all the data together, the target audience will probably gloss over your message. Throwing a few numbers and charts at the reader has become pretty acceptable nowadays. But to make something truly memorable, and hopefully shared with someone else, you need some kind of story. You need something to tie it all together, to give it some meaning. That’s where Microsoft steps in with its latest addition to the Business Intelligence Analytics space with an add-in to Excel called GeoFlow.

GeoFlow allows a power user to take an existing data set and visualize it on a 3D globe. The visualizations can vary from simple points to heat maps and bars that extrude from a map. Using the data, you can build a tour of the insights that were found and replay them. Similar to PowerView, you can embed the captured insights into various communications and share them with your peers. Read more of this post

Filed under Business Intelligence, Microsoft

Tweet Your Business Requirements

November 5, 2012 by 1 Comment

Introduction

My Twitter feed would explode if, as a business analyst (BA), I was required to tweet all of my project requirements. For this and many other reasons, tweeting requirements is unlikely to become a best practice any time soon. First, there is the very public nature of Twitter, and second, the brevity that Twitter requires at 140 characters or less per tweet.

Everyone knows that business analysts write extensive (read: lengthy) Word documents and Excel spreadsheets full of requirement statements to demonstrate a thorough understanding of the subject matter at hand. In some organizations, the bigger the document, the more highly regarded the business analyst—even though few (if any) of their stakeholders actually read the material they’ve written. To be clear, I consider stakeholders to be both the business and technical project partners who consume requirement and rule statements for validation or production. Feel free to tweet me back @justincullifer if you disagree with my claim that stakeholders are not reading the material.

Considerations

A colleague of mine shared that, in a former position, he managed a team of a dozen or so individuals in a product management organization. Business analysts were a part of this team, responsible for eliciting and capturing requirements for technology projects. His BAs produced the customary requirements documents from which developers coded and testers tested. He shared that one of his brightest BAs used to include, in every requirements document over the course of several years, a statement that said, “If you read this, call me at [phone number], and I will give you $20.” Her phone never rang. While a comical anecdote, the more serious implication is what many of us have known for a very long time: lengthy requirements documents are nearly impossible to consume and retain.

Attention span varies from person to person. Some of us have the ability to work heads-down, fully immersed in the subject matter at hand. Others work in patterns of on-and-off focus time intermixed with breaks for conversation, reading news, or catching up on email. Still others find it difficult to focus for any significant length of time and therefore devote little time to any one item. We can take a lesson from network news media and social media, who accommodate those with the shortest attention span by delivering sound bites, headlines, tweets, and wall posts. Perhaps BAs should consider this approach, as BAs must deliver informative statements in a timely manner.

This returns us back to the hypothetical use of Twitter for the delivery and consumption of requirements and rule statements. If BAs are limited to 140 characters, they are going to try really hard to make the very best of those 140 characters. Here are some ideas about potential content:

  • They might mention the user role to which the requirement or business rule pertains by using an @ mention. For example, @Librarian must always obtain a @Customer driver’s license number prior to issuing a library card.
  • They could specify locations by leveraging the cross-hair “add your location” feature.
  • Attaching a static wire-frame using the camera’s “add an image” feature may prove particularly useful when tracing requirements to visualize user interface pages.
  • A link to artifacts, videos, or models using a bit.ly link would surely maximize the 140 available characters. Now, now—no cheating! Linking to lengthy Word documents or Excel spreadsheets would break our imaginary set of rules!

In this fictitious world of tweeting requirements, the onus would be on the BAs to make the most of their brief statements. To simply tweet hundreds or thousands of tweets negates the remarkable nature of this concept: concise and targeted requirements. Concise and informative headlines, tweets, and wall posts are exactly what media outlets have found that people are accustomed to and respond to in today’s connected world. Is it possible that leveraging Twitter might be the next leap in the evolution of BAs focused on delivering requirements that have been fully vetted for clarity and accuracy?

Consumption of high-quality requirements is critical to the success of every project. Imagine that your project stakeholders (the ones validating the accuracy of the requirements) followed their BAs’ Twitter handles and kept close tabs on newly tweeted requirements as they came across their respective Twitter timelines. The stakeholders could elect to reply to each tweet with corrective feedback, or even re-tweet requirements to other stakeholders for validation. Since tweets appear at the speed of light, the stakeholders would be forced to frequently monitor their timelines, so as not to miss any critical requirements or rule statements and, subsequently, their opportunity to provide feedback or approval. Tools like Tweetdeck may enhance this process, but each would require the stakeholders’ attention. A by-product of this fictitious world is the fact that the BA would have significant influence over their stakeholders’ schedules, which is only a dream of BAs in most organizations. The same concept would apply to consumers farther along in the project’s lifecycle, such as developers, testers, and trainers, who would also need to monitor their timelines for the same material.

In spite of the perceived benefits of tweeting requirements, I realize that organizational barriers will continue to require the production of Word documents and Excel spreadsheets most of the time. Industry trends are favoring the adoption of robust requirements management systems like Jama Contour and IBM RequisitePro. These systems have lower costs of entry and flexibility than ever before, allowing project teams to capture, manage, and consume requirements effectively. Arguably, the downside of such systems (and Word and Excel) is that these systems still allow BAs to freely enter extensive amounts of text that someone, eventually, is going to have to consume. To the points made earlier, BAs must understand that their stakeholders consume information in sound bites and must clearly articulate requirements to accommodate all levels of interest and attention.

Conclusion

Given the concurrent trend of businesses seeking to expedite speed-to-market and respond to customer feedback quickly, I believe that BAs will need to find creative ways to follow more pragmatic approaches to their elicitation, documentation, and delivery behaviors.

Leveraging advanced requirements methodologies, such as Requirements Visualization, enable business analysts to work collaboratively with user experience designers to elicit, capture, and deliver the right requirements at the right time. In turn, stakeholders can maximize their time and gain greater visibility into every stage of their projects. Interactive models traced to thoughtfully produced requirement and rule statements ensure that stakeholders are engaged and interested.

Until your organization adopts a contemporary requirements methodology, consider my tweeted requirements proposition: keep requirements succinct, accurate, relevant, and as easy to consume as possible.

Filed under Business Intelligence, Project Management, User Experience

← Older posts

Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: