OK, folks, BI & DBCF fans, one last 2008 post ... Here are 5 things that I intend to focus on in 2009 as part of my new year's resolution around database, BI & migration projects. Happy New Year & may 2009 be a better one!
[ in no particular order ]
1. MDM (Master Data Management) I have at least 3 initiatives queued up for 2009 that will entail various tools and data sets that require MDM hubs and efforts: product data, customer data and project data. I promise to blog about my findings. This one is going to be very interesting.
2. More pervasive BI. This is a very important aspect to BI as a valuable mechanism added natively to applications and something that is key to MEC which I speak of in this blog. I fully believe in BI as a key integral part of business applications and solutions and am very focused on this in 2009.
3. Continue to put forth the business value and viability of pre-built data models, analytics and scorecards around particular common business problems in vertical industries including retail, manufacturing and healthcare. Particularly in harsh economic times, businesses can not afford to experiment and try to figure these complex solutions out without a baseline set of existing solution parameters specific to their business models.
4. ETL that is auto-healing, auto-detecting, metadata based and easily modified. Much of this will be custom built and I will try to get some examples in here in 2009.
5. More interactive exciting data visualizations to make BI and dashboards pop with interesting ways to view and interact with business data.
It has been a year of change for my role in product management, Microsoft Enterprise Cube and, well, pretty much the entire world, I suppose. For my traditional end-of-year post this year, I will keep things text and reflective. So there may not be much in the way of tips, tricks or insights this month. And, yes, I did miss 2 posts in November. But things slowed down incredibily due to the economic conditions, elections, etc. so that I was not posting for speaking engagements, user groups, etc.
2009 promises to be even more challenging for those responsible for technology initiatives and implementing BI & DW solutions. Let's face it, 2009 will likely see continued economic recession slumping and business challenges that will lead to changes in the way we do IT. And the way that IT embraces and implements business intelligence will need to show value, ROI, reduced costs and focus on your business users. These are all factors that I speak about at public sessions and here in my MSDN blog.
But do not lose sight of the business units that are using your BI solutions. This has always been a problem in the past when I meet with customers or peers in that IT groups will sometimes get trapped into "playing" with the new PPS tools or the new SQL Server 2008 features without a clear roadmap on how that creates added business value, reduces costs or solves business problems. In 2009, if you are not addressing those issues with scorecards, dashboards, analytic reports or predictive analytics, then you are putting yourself in a precarious position.
Ok, so let's look on the bright side for a moment and touch on the areas that I will focus on for Microsoft BI blogs in 2009:
1. PPS: Continuation of business domain solutions to provide specific business performance and optimization solutions
2. SQL Server 2008: Continued adoption of SQL Server 2008 will lead to more things that I can report here in practice with SQL Server 2008 BI solutions
3. Heterogeneous solutions: No longer focused only on the SQL Server solution area, we can chat here about integration and cooperation between Microsoft dashboards and other tools
Happy holidays and I'll speak with you on the other side of 09!
1. Can we use Essbase with SQL Server BI?
Yes. Here is a good whitepaper on Essbase with SSRS: http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/UsingSSRSandESSbase.doc
Essbase does have an XML/A adapter. XML/A or XML for Analysis, is the XML specification that is used by both Essbase and Microsoft.
Both OLAP databases use MDX as the query language. But you cannot use PPS with Essbase.
2. Are there trial versions available for PPS?
I believe that this is good for 180 days: http://www.microsoft.com/business/performancepoint/howtobuy/trial-software.aspx
3. How is PPS priced? http://www.microsoft.com/business/performancepoint/howtobuy/default.aspx
Here is a link to the final Microsoft BI slide presentation that I used at the NJ October SQL Server user group meeting:
http://cid-e780c16f79d3848d.skydrive.live.com/self.aspx/Public/njsqlug%7C_001.pptx
Thank you to all who made it out for this Saturday morning event. It was great to meet with everyone and share our experiences with Microsoft BI. PA/NY/NJ user groups for SQL and .NET have a great following and a tremendous sense of community that is great to see outside of Redmond.
I am going to enter a new blog entry this week for the Q&A which I took and promised responses on. If anyone wishes to reach-out to me personally, please email me @ kromerm@gmail.com.
Lastly, a special message of gratitude to Melissa, Peter and Don, who made the day a success with their terrific organization efforts and AV assistance!
Best, Mark
Perhaps more than ever, in these days of tightened budgets and questionable ROI, squeezing every dollar possible out of your IT projects is essential. Business intelligence provides you with some unique helpful techniques to measure your project success and earned value as well as using those metrics to improve your project performance. In this entry, I will talk a little bit about earned value, predictions and overall project portfolio performance. The actual tracking systems that you are using to track schedules, resources, portfolios or budgets do not matter for this chat. They can be Excel, MS-Project or any other project management tool. The idea will be to extract those into a data warehouse or directly into a cube in SSAS so that you can perform mining, create scorecards and predictive models.
Starting with earned value, you will want to extract actuals & plans and let SSAS create forecasts for you. The key metrics here will be cost variance (earned value - actual cost), CPI or cost performance index (EV/AC) and SPI or schedule performance index. Viewing the s-curves in your Excel spreadsheet or SSRS report when monitoring your project performance against these KPIs is crucial. History shows that once your project goes over budget, it is nearly impossible to make that up later. Earned value measures are an ideal way to monitor your project scope and cost performance.
When measuring your project schedule performance, you will first need to set your baseline and continuously measure your performance against that baseline. This is achieved by exporting actual schedules and budgets from your GANTT in Microsoft Project and your costs either from Project or ERP such as SAP, Dynamics, etc. Having a weekly snapshot of this data should be sufficient for most IT projects.
By storing your project data such as schedule variance, cost variance and resource allocation, you begin moving from a reactionary-based project model to a more proactive approach. In PMI terms, project managers that put tools like SSAS, Excel and SSRS as a key part of your project monitoring activities, you will have more opportunities to quickly recognize deficiencies and avoid falling behind to that point of no return. Additionally, using your data mining tools from SSAS to perform resource leveling and constant continuous improvement by always ensure that you capture your project activities in a retrospective step of your project closure, you will quickly become the true organizational project manager hero!
(Below are some sample KPI reports to help you during project monitoring to move to a more reactive project PERFORMANCE management styled project manager)
Just a brief blog this time around, folks. As I have been settling into a new job, I have run across an often-repeated common set of practices in implemented a business intelligence solution for your business. Many, many times I have encountered an IT shop implementing a BI tool that has promised to uncover hidden treasures in vast amounts of mined gold and to improve sales, satisfaction and product marketing numbers immediately.
Ok, in the Microsoft world as well as any other BI software toolset, these are promises that can be fulfilled. The operative word being "can". The tools are enablers, simplifiers and just that: tools. To successfully make strategic advantages for your business users, you must implement your BI infrastructure in a systematic process that includes fundamental data modelling and planning initiatives.
I am going to make it a goal of mine to outline those throughout the next coming months in this blog. What I will do today is to start with the beginning: requirements gathering.
I am going to make an assumption that you have already chosen Microsoft as your BI vendor and are going to begin with a database in SQL Server 2005 and utilize PerformancePoint Server 2007 to implement charts, reports and scorecards. This is good. Now go and interview the business users. Ask questions such as:
1. How do you measure your business today (KPIs - product performance, sales targets, budgets, etc.)
2. Ask about common business entities - products, people (organizational mapping), sales figures, budgets. Begin the process of looking for "dimensions".
3. Ask questions about what are the difficulties in finding out sales performance, goals, customer segmentation, etc. Look for where you will begin to map "facts".
4. Look for the relationships: How do sales relate to quotas and salespeople. What do the reports look like today.
5. Ask about time, frequency and geography. This will help to map out slicers such as "by region", "by quarter", etc. as well as to determine the update frequency you will need for ETL and cube refresh. Remember that SQL Server Analysis Services is an OLAP engine and we will likely use to MOLAP to refresh the cube on a schedule.
Use this as part of the analysis to formulate a set of requirements that will generate your specifications. We'll walk through this process in this blog coming up soon and I will show you how to trace all the way from analysis to actual reports and scorecards implemented in these tools.
In the meantime, search out my previous MSDN blogs and you will see some examples of leveraging a BI COE and data modelling techniques that I explain in my blog that can be of help to you as you map out your BI deployment strategy. We'll work on this together!!
C'Ya - Mark
While we typically focus on vertical business industry BI solutions here, let's talk today about product information and some examples of how to build and maintain an effective product hierarchy in your Microsoft BI solution. As I wanted to keep this blog posting title short, I should note that I am not going to go into the details of a full PIM system solution. Instead, I am going to speak of modeling product data in your BI hierarchies for reporting purposes.
Let's start with one of the best sources for best practices in data warehouses and ETL for SQL Server: Adventure Works. The datawarehouse in the Adventure Works sample database has dimensions for products, product subcategory and product subcategory (pictured below). I also highly recommend downloading and looking through Project Real at http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx. While Adventure Works (AW) has good product information in it, Project Real has good real-world customer data.
When you use such a star schema architecture with SSAS 2005, it is as simple as using the cube wizard to derive the proper dimensions and facts with natural hierarchies. As a matter of fact, the wizard will decipher and compile these for you, allowing the developer to override the default settings if needed.
In the case of Adventure Works, what you end up with is a hierarchy of:
PRODUCT
\/
PRODUCT CATEGORY
\/
PRODUCT SUBCATEGORY
Now you can use this for drill down reports, scorecards and other purposes, including a PIM solution where you can build a central location of your company's product information for tracking, cataloging and monitoring.
Another tool that you can use to build your product catalog is PerformancePoint Server. The above example is developed using Business Intelligence Development Studio in SQL Server. This is an example of a hierarchy is PPS using the member set capability in the Business Modeler:
This blog space is dedicated to Microsoft BI enabling business to optimize their business through business performance management. In the Microsoft product portfolio, this is accomplished with SQL Server, MOSS & PerformancePoint Server (PPS). Readers of this blog are also familiar with Microsoft Enterprise Cube (MEC), whereby Microsoft Services makes available to customers and partners, a series of pre-built analytics and data models specific your business industry.
Today, let's talk a little bit about the Microsoft solution to driving your business toward a strategic advantage in your market space. That is, not focusing on the bottom-line of lowering costs and operational efficiencies. While those are very important goals and are very much achievable with PPS & MEC, using these tools and techniques of BPM (business performance management) are really quite vital to continue to grow your business. Simply put, most commercial businesses will not continue to be successful, no matter what the industry, without growing revenues, opportunities and expansion.
Some of the uniqueness of Microsoft BI tools to accomplish this include the full-cycle ability of an integrated set of tools with the Microsoft BI suite. Most of this functionality is available right in PPS.The ability to plan, forecast, measure and adjust your business plans all in a single tool is unique in this space. Additionally, most businesses plan and budget from Excel today. A core belief in Microsoft is to allow you to continue to operate directly from the most ubiquitous BI client tool today: Excel. Take a look at this very good data sheet from PPS regarding planning, budgeting and forecasting.
Once you have completed your budget cycle (which differ from industry to industry) and entered the plan into PPS, you can now monitor and measure your business units against each stated goal. This is the monitoring capability along with drill-down analytics. This knowledge creates power in your organization and Microsoft (especially true with our MEC customers) customers are able to leverage their Sharepoint (MOSS) portals to put the power of making better decisions with better information directly in the hands of everyone in your organization. Collaborating, sharing and maintaining visibility into your key business metrics can drive your top-line and your next planning cycle. Some interesting top-line metrics to use for this purpose include customer net adds, sales opportunities, project inventory turns and customer satisfaction.
So, what does this all look like when it comes together? Here is a sample budget from PPS, integrated with Excel:
And here is what a dashboard would look like after you have entered your business strategic goals and objectives:
A lot of discussion has been kicking-up lately about BI and SOA as combined forces in the IT enterprise. Much of the discussion has been around similar, or dissimilar skill sets and strategies of these architectures. Not to mention, is BI or is SOA a solution, architecture or strategy? To be honest, unless there is a driving and obvious business need to utilize SOA mechanisms in a BI solution, we cannot put forth a compelling argument to do so. Without that business case, we are just implementing architectures for technology's sake, not for the business' sake. And in the end, it will be the business that drives the decision.
So let's take a look at how data integration for your BI system can benefit from web services. Keeping in mind that this particular blog space is focused on industry specific vertical solutions in business intelligence, I will pick a particular industry solution example. Let's choose, for the sake of this article, a manufacturer of consumer goods meant for mass consumption, who is looking to implement customer segmentation so as to improve their product marketing and streamline their go-to-market process.
In this case, data sources may be from such varied heterogeneous sources as transactional databases, Excel files, mainframes or LOB systems (ERP, CRM). If you were to perform straight ETL by pulling defined fields based on database data types, you will fight the common ETL problems of tight coupling, high sensitivity to source and target changes, deep understanding of source schemas, etc. But a promise of SOA is to provide "contract" based integration and programming where you should be able to access line of business and legacy systems from a "semantic integration" perspective. In the case of a customer segmentation solution for manufacturing, you would, for example, integrate customer data from CRM to survey results from an external database along with financial figures for the business from Excel, for example.
Now, reaching a "nirvana" level of integration is a lofty goal, one that will greatly benefit those of us who hold responsibilities with business users to provide meaningful, actionable, aggregated knowledge. With an understanding that this should be a goal of the enterprise will help us to continue to work toward adding web services layers to legacy data access and a unified approach to SOA & BI in corporate IT.
Here are a few of my personal favorite links to continue reading about this topic:
Microsoft BizTalk Adapter Pack is a set of adapters that we use with our customers for BI data integration through SOA
For a good look at ETL and SOA in the world of BI try this article by Arnon Rotem-Gal-Oz
There is a basic first-step process that must occur in early phases of your business intelligence solution build-out. Make sure that you have business & system analysts interview, document and review with your key business stakeholders and business users, the documented process and entities to be captured to solve your business needs.
To rephrase that in a simpler, more succinct way: Model your business first with a conceptual model. This is very important as well as very difficult to achieve to a level of success in which you have accurately captured and modeled your business processes in a way that can result in a software solution. Very skilled analysts with the ability to speak in terms that clear in regards to gathering requirements and business processes, many of which the stakeholders may not have fully realized in their day-in and day-out work day.
The way that we do these through Microsoft Services and Microsoft partners for Microsoft Enteprise Cube (MEC) solutions, is to utilize our program for BI delivery models which describes best practices and techniques for understanding the customer's business models and business needs. Since MEC is most concerned about providing business value through BI solutions based on industry pain points, we devise conceptual models in Visio that are high level using ORM or UML. The first picture below is an ORM example from Visio followed by an example of UML. I have found that ORM is very much sufficient and appropriate for data modeling while UML has much broader application for capturing use cases:
What we have found with the Microsoft Enterprise Cube, BI SDM and the Microsoft Solution Framework, is that without a focus on gathering, documenting and validating models of business processes and business entities, your BI project runs a high risk of not solving the real business needs or business problems.
I am going to continue this discussion as a series throughout the summer. Next we'll dive into putting this conceptual model into a logical model and user E-R diagrams as we start to build-out the physical data models that will serve as the basis for both the relational and multi-dimensional databases within a complete business intelligence solution project.
- Mark (makrom@microsoft.com)
Regularly, in this blog space, you read often about "vertical" business intelligence solutions, or "industry" BI solutions. Usually, I will frame this around our Microsoft Services and partner offering called Microsoft Enterprise Cube or MEC. That is probably appropriate terminology in the Microsoft space of platform software technology. In other words, at Microsoft, in the product groups, we make flexible tools and platforms for developing solutions such as business solutions based on business intelligence. For Microsoft, that means SQL Server, PerformancePoint Server, Excel and Sharepoint for the most part. And the development environment for the glue is .NET all residing on the Windows Server OS.
But in solutions group such as within Microsoft Services, we help our customers implement these products as high-impact business solutions that will solve immediate problems and provide ROI in the shortest payback period we can produce. In the world of business intelligence, there is really is no need to distinguish between an industry specific vertical BI solution or any other type of BI solution. In order produce a business solution, you must use industry knowledge, data and content based on the vertical area you are addressing. There is just no other way to do it. The distinction then is really boiled down to product vs. offering. So, PPS, SQL & MOSS are our products. Packaged solutions such as MEC are our offerings to solve your business problems.
Back back to the area of indsutry domain. If you look at a holistic approach to solving a business problem with BI, you need to start at the source. Or the data sources, to be more precise. If you are a healthcare provider or a manufacturer or a telecom company, your data sources will have some similarities in CRM, inventory, billing, etc. But there will be many differences in business processes, data models and operational data that will mean that scorecards with KPIs in one industry do not directly translate into another industry's business domain.
Once you work your way up the stack from data sources, you will again find similarities in areas such as collaboration and user access. But in manufacturing, for example, there may additional needs to remote access from portable devices to see what-if scenario output, forecasts and dashboards whereas an insurance company may require 90% PC access to their BI portals.
These differences should be discovered and recorded through requirements analysis using techniques such as E-R, ORM and UML diagrams that depict the processes, entities and relationships that will form the basis of our business intelligence solution. Performing these up-front tasks as essential to build the domain model that will produce high-impact business value with your Microsoft BI tools.
Clearly, there are a lot of reasons why business intelligence makes sense in the context of Customer Relationship Management (CRM). After all, both of these applications typically provide deep insights into customers. BI can give you the analysis and behavior patters or answers to the questions "why" and "how", while your CRM will tell you "who" and "what" among many other things.
For example, your CRM will tell you who your customers and what they have bought. There are a number of complex CRM systems that provide bundle software services to allow you to add in to that mix planning, scorecards, what-if scenarios and other BI domain functions. Things are much more complicated then that, but this is a blog and I don't want to bore you to death! So let's stick with simplicities here and focus on my little topic of the day, which is to look at the BI part of CRM and how these are independent.
At this point, once you are looking at a complex and independent BI infrastructure to provide, let's say, a BI portal (we'll assume Sharepoint) for a metals manufacturer. We'll want to have all points of the supply chain benefiting from both of these so that the financial planners, operations managers, sales team, marketers and strategic decision makers are looking at the same data points and making streamlined excellent decisions.
Great, now we have to worry about single source of truth and a single view of the customer. Is the BI infrastructure using the same customer database as the CRM system? Are your suppliers and B2B customers linked into your CRM? If not, we've immediate run into problems that can be resolved with CDI (customer data integration) and MDM (master data management).
MDM can provide the central hub to supply that single view of customer, product, employee, etc. These entities are common MDM modelling entities and is definitely a direction to begin thinking about with your company data. Any BI system that is supplying knowledge for decisions must use a unified, up-to-date, accurate vision of the business and the underlying process. Otherwise, you are doing more harm than good.
For a good dive into the Microsoft vision of MDM, I recommend starting here:
http://www.microsoft.com/sharepoint/mdm/default.mspx
Roger Wolter has some good writings on there and the CDI Institute is sort of rebranding as the MDM institute:
http://www.the-cdi-institute.com/
So thanks for hanging in there with me this posting! It is vitally important to get your data marts, data hubs, customer views, etc. correct before we can have fun with analysis and data mining. Which is what I will focus on next time. I am going to give you some samples of real-life data mining applications that created real business value for our MEC customers.
Best, Mark
Welcome back, lovers of business intelligence and solutions for business optimization!!
Today, I thought I would touch on the topic of driving business performance and optimizing processes and keeping your employees focused on your key business metrics.
In the world of BI, there are many terms that relate to measuring your business performance: KPIs, metrics, measures and so on. What I want to do here is to use our Microsoft Services business solution framework MEC to show a very interesting way that I have seen customers being successful at this.
The first thing you need to do is to decide which top 5 organization, strategic metrics do you need to have your employees have visibility to at all times. That can be tricky because you may have a large bucket of metrics and KPIs but they need to be categorized into strategic, operations and tactical. With a Microsoft BI projet such as MEC, we can provide you with a rich set of KPIs for your business that meet those categories and are appropriate for different parts of your organization.
So now you can decide which 5 need to be visible to groups such as marketing, sales, HR, operations, etc. At Microsoft, and in MEC in particular, we use Sharepoint as a centralized BI portal such as in this dashboard example:
Below those Virtual Earth and Silverlight controls live Microsoft PerformancePoint analyst dashboards and all of this is surfaced via MDX out of a SQL Server Analysis Services cube.
But if you take a look at the top part of the portal, you will see a scrolling Silverlight control that mimics a stock ticker which I have highlighted here in a red outlined circle:
What you achieve with this approach is that every time your employees log into their portals every day, they see the top business metrics that they need to be aware of to drive those business activites to their optimal level. You can see the current measure as well as what the trend looks like.
The objectives we are putting forth here is to give visibilty to all of your employees to coallesce around driving the overall business strategy through business performance management.
You hear me talk alot about business intelligence as a solution not a tool here in this blog. And in fact I am more than just an advocate of BI and demonstrating the value of BI to business, but I live and breathe it every day! To me, it seems that a successful business intelligence solution, implementation project or pilot, must, must, must utilize best practices, standards, common practices and leverage key learnings more so than most other IT ventures. Notice that I did not mention BI products or tools here! I am speaking of implementing your BI for return on your BI investment, to embed business intelligence into the fabric and rythm of your business so that your entire organization makes better decisions.
Ok, enough of that. I am biased, no doubt. But my past history in IT and software companies was not always BI. So I feel I am on pretty solid ground when I say that the sort of business intelligence projects that I am speaking of here will need a center of excellence (COE). And I am referring to my old concept of end-to-end BI (please read through my prior postings for more on that) which would include data integration, data quality, data management, reporting, KPIs, workflow and data visualization. That is a TON of data movement, data presentation and data governance all coming together in the end to empower your business to make excellent decisions.
A center of excellence (COE) is there to provide the guidance, standards, best practice and learnings to use while implementing these projects. In our group out of Microsoft Services where we work on vertical BI solutions for industries (which we call Microsoft Enterprise Cube or MEC). We have internal COE groups for BI and BPM and there are also good articles out there on MSDN, DM Review and BI Review which I recommend you search for BI COE. I am an absolute advocate of our internal groups at Microsoft, our partners and our customers. One of my favorite activites, in fact, is spending time to review, discuss and debate approaches to business intelligence implementations, scalability and lessons learned. An interesting approach that we are taking is to try and bake that knowledge base into MEC so that our customers can see the fruits of the labor somewhat tacitly in the form of very well designed and developed reports, KPIs, data models and ETL as well as explicitly in white papers, guide books and training.
I won't have the time here today to get into the different approaches to setting up an internal COE. Certainly those references I listed above are good places to begin your search. You'll have to remind me to speak about this more here or if you run into me at one of our events. As always, I would greatly enjoy speaking about the experiences or ideas around business intelligence center of excellence. Or you can reach out to me via email (makrom@microsoft.com) or speak with your Microsoft local rep about Microsoft BI best practice consulting and MEC. Being able to build up a go-to group with this sort of knowledge will save you enormously on these projects. In fact, measure your COE operational cost savings on your BI projects by reducing your internal project overhead or buffers by leveraging the COE.
I wanted to take just a few minutes out this month to post up some of my favorite, more interesting data visualization examples using Microsoft products (SQL, PPS, SSRS, Proclarity, MOSS & Silverlight) as a way to demonstrate making data analysis, scorecards and dashboards exciting and interesting. I will probably not have much of a chance to blog here for a couple of weeks while I am heads-down on getting our BI solution framework for industries launched and rolled out to our field and partners. Enjoy!!
Here is an example of a bubble chart surfacing data through SSRS ( thanks to Rob Wilson for this chart )
Here is an example of a PPS dashboard using Visio for a strategy map, a very nice way to utilize Visio to surface BI:
Here is an example of Sharepoint BI dashboard with Silverlight, Virtual Earth and Sharepoint subsites surfacing data from MDX queries against a SQL Server 2005 OLAP cube:
Perhaps not as cool or snappy is a favorite of mine. Excel is a very, very good reporting tool for data analytics and very popular for reporting. Excel now has support for Edward Tufte's Sparklines. Essentially, you are able to view a chart within a cell, which can help get across an important statistic in a small amount of space, which is a goal of Sparklines:
Finally, here is an example of allowing your users to see their scorecards remotely from their Windows Mobile device:
