For those of you who haven't heard, Microsoft recently announced a "Strategy" change in their approach to Business Intelligence. In a nut shell, PerformancePoint will no longer exist as a stand-alone product. My understanding is that the Monitoring and Analytics portion will be rolled into the next MOSS Enterprise version (Microsoft Office SharePoint Server) as "PerformancePoint services", ala InfoPath services, or Excel Services. No further investment will be made in PerformancePoint Planning.
So how does this affect your own BI strategy. First, let me get a little bit philosophical...
If your Business Intelligence strategy is dependent on the strategy of a supplier (albeit a gigantic one), you might want to rethink your strategy.
The Bad News: For those of you who have heavily invested time and money into PerformancePoint Planning, and considered the excel-based forms, workflow, security, and relational and OLAP database created by PerformancePoint business models the key technology supporting your BI activities….you may have to rethink this approach…yes I know that Microsoft says they will continue to support Planning, but if I were CIO that would do little to ease my pain.
The Good News: From a Monitoring and Analytics standpoint, if you have properly thought through your approach to Business Intelligence and Data Warehousing, it really shouldn't affect your overall approach....here is why:
1) The key to good dashboards and scorecards is the underlying data.
A scalable and flexible approach to BI is dependent primarily on how you've modeled your data…for example:
- Have you identified your key sources of business data?
- Did you properly define your key business entities (customers, suppliers, employees, etc…)?
- Did you properly define the key measures (sales, costs of sales, etc…)?
- Are your ETL (Extract, Transform, and Load) processes to retrieve data from its source maintainable and scalable?
The answers to those questions are vendor independent. While each vendor has a way of storing the entities and measures, and different ETL products work in different ways, the bottom line is that the hard work comes in understanding your data and your business in such a way that you can use those tools.
Also, unless you were storing your business entities and measures in a PerformancePoint planning database, and designed your ETL process around the peculiarities of the PerformancePoint Planning database, this announcement doesn't affect the answers to any of these questions.
2) Flexible, scalable BI is system of related but distinct components, not a single product.
While I think that SQL Server, Analysis Services, and Integration Services (all Microsoft products) offer a great price for performance, it might help you sleep at night knowing that if you wanted to switch ETL vendors you don't have to re-do the entire BI system. Similarly, the relational data warehouse structure will look the same whether you build it in SQL Server, or Oracle and a migration shouldn't involve rethinking the underlying logic. And whatever OLAP database you are using should support MDX queries (although I admit, there will be some rework there) and be able to build cubes off of a standard star-schema architecture. I realize I am oversimplifying things a bit...the rework would not be trivial. But my point is that a well designed BI architecture contains these independent components...
In this sense, PerformancePoint Monitoring and Analytics is simply your presentation layer. In the next version of MOSS, you will simply use whatever new "PerformancePoint" services functionality is available, or some other third party analytic tool. For those that can wait for the next version of MOSS, or have an existing MOSS license with Software Assurance, this is actually a pretty good deal.
As this was just announced last week, I'll probably babble about this some more later...
The Problem
The other day I was perusing through a textbook, Valuation: Managing and Measuring the Value of Companies, (http://www.mckinsey.com/ideas/books/valuation/index.asp) and I thought to myself, "given a hierarchical chart of accounts designed for reporting and internal performance measurement, how could I design a system which would provide a reliable and maintainable source of capital-structure-independent profitability measures, WITHOUT the use of brittle excel formulas and external link dependencies…" OK, so it didn’t go exactly like that, but it did get me thinking on how I would handle this with SQL Server Analysis Services. This is really more of a proposed idea then a recommended solution. I haven't done any performance testing on this approach, and I must admit, I'm not an expert on the financial performance measures I refer to…I did my best to create them using the accounts available in the AdventureWorksDW Account table, but the McKinsey guys (and gals), might ding me on some of my formulas…
Some Background: SSAS and the Chart Of Accounts
A key concept in financial accounting is the Chart of Accounts. The Chart of Accounts effectively identifies key financial information necessary to make financial decisions and fulfill reporting requirements. These accounts are related to each other in a hierarchical fashion…for example, the general ledger may contain an account called Trade Sales. Trade sales rolls up into an account called Gross Sales, which rolls up into Net Sales, which in turn rolls up into Gross Margin, and so on.
This parent child relationship can be seen in the beloved Microsoft demo database AdventureWorksDW, in the DimAccount table.
Include all the accounts together, and you get an account hierarchy that looks something like this.
While I won't get into Account Intelligence here, suffice to say that this intelligence includes the logic about how an account is aggregated into its parent ( Trade Sales is added to Net Sales, whereas Total Cost of Sales is subtracted when rolled up to Gross Margin).
Note, however, that this parent-child relationship necessitates that an account have one and only one parent. This is fine for financial reporting and for basic analysis, but there are many financial measures that won't follow the pre-defined chart of accounts. For example, Net Operating Profit Less Adjusted Taxes (NOPLAT), a common performance measure for valuation, is equal to Operating Income less adjusted taxes. While there is an account for Net Operating Profit, naturally there is not an account for "Tax Adjustment". We must create an on-the-fly account to calculate this.
The Custom Member field
There are a few ways to handle this issue, but I chose to use the "CustomRollupColumn" field available for parent-child dimensions in SSAS. This allows a member (in this case my "Tax Adjustment") to obtain it's value from a custom formula (MDX…resistance is useless…) instead of from the fact table (like "Trade Sales" in Adventure Works) or from it's children (like "Gross Sales" in Adventure Works).
To see how AdventureWorks handles this, open the AdventureWorks cube in SSAS, go to the Accounts hierarchy in the Account Dimension, right click on Properties and look at the CustomerRollupColumn property..
This is pointing to a column in the DimAccount table…Here is what I put in for my "Tax Adjustment" member:
While forgiving me for hard coding the estimated marginal tax rate, note that this CustomMember formula will be computed based on the values of other members (in this case Other Income and Expense and Interest Expense).
If we browse the cube, you can see the Tax Adjustment now appears as a calculation
(34165-12000)*.39=8644.35
I can now use that same process to calculate NOPLAT(Operating Profit + Tax Adjustment),a derived member called "Invested Capital"(Assets - (Total Liabilities - Long Term Debt - Short Term Debt), and a ratio of the two called Return on Invested Capital (ROIC). Note that I also filled in the parent account numbers, so that there was a logical drilldown into the components of ROIC (Invested Capital and NOPLAT). Finally, I created a parent account called "Performance Measures" to logically separate these measures from balance sheet and net income data.
Then end result is the ability to drill down into performance measures (just ROIC in this example), and then drill down into the components (NOPLAT and Invested Capital).
Alternative Approaches
I could have also used calculated members or even PerformancePoint to create these performance measures. I'll probably revisit this issue at a more practical level of detail later...
What is Pervasive BI
In Business Intelligence circles, "Pervasive BI" has gotten a lot of airplay lately. The image of everyone in an organization embracing the new analytic tools is quite appealing to C-level managers, and vendors froth at the mouth when they think of line-level employees logging into their portal every morning to look at the most recent production reports and analytics. While this is appropriate for part of the organization, in practice, many employees simply will never take the time out of their day to go look at such a portal unless perhaps it also contains videos of buffaloes attacking crocodiles, exploding Pepsi bottles, or Paris Hilton campaign videos.
Pervasive BI must address the reality of employee processes
For Business Intelligence to be truly "Pervasive", it needs to be part of the daily employee processes...to some extent it needs to be brought to THEM via the applications they use everyday, whether that be a CRM or ERP system, or some other LOB system. Of course, this could pose a development and maintenance nightmare, as each system would have to be customized to provide such capabilities, and many would argue, not incorrectly, that this violates the "single version of the truth" notion so important to many BI professionals.
So let me try to coin a term…How about "Re-usable Business Intelligence"? Just like developers strive to re-use code and business logic through classes and web services, the logic behind Business Intelligence should be reusable. Along these lines, I looked a little bit into PerformancePoint Monitoring and Analytics web services, to see what kind of potential there is for re-using the logic.
Can PerformancePoint Web Services help?
For those of you not familiar with how PerformancePoint works, the basic idea is this:
- A Business Intelligence Developer designs the data warehouse and analytic models.
- A Business User uses a program called "Dashboard Designer", which looks and smells a lot like an Office 2007 application, to connect to previously defined models, create reports (via a drag and drop interface), create scorecards, Key Performance Indicators, etc..
- The Business User "publishes" them to the PerformancePoint Web Service
- SharePoint web parts then "consume" the PerformancePoint Web Service and display pretty dashboards and scorecards to business users.
The fact that the business user "published" their work to the PerformancePoint Web Service means that that logic is now available to whatever applications need to use it. While I normally break out in hives if I write any code, I wanted to write a little ASP.NET app to see what kind of things I could do with this Web Service. This Mickey Mouse application simply allows a user to select from a list of PerformancePoint analytic reports and view the MDX, which is the query language for multi-dimensional databases. This could be useful, as the developers in the organization could reuse this exact MDX in their applications.
Implementation Sample
For those following along at home, here are the basic steps I took:
1. Create a new ASP.NET Web Application
2. Add a new Web Reference
3. Type in the url to the web service (which by default is on port 40000) and click Go
(My performancepoint service was at http://MyPerfPointServer:40000/WebService/PMService.asmx
4. Type in the Web Reference Name (I named it PPSM), and click Add Reference
The application has two controls, a drop down list called ddlPPSReports, and a label control called lblMDX. As I am not a developer by trade, and this is the first line of code I've written in two and half years, please forgive any stylistic errors...
public partial class SimplePerformancePointApp : System.Web.UI.Page
{
//Instantiate PerformancePoint Webservice
PPSM.PmService mon = new PPSM.PmService();
protected void Page_Load(object sender, EventArgs e)
{
//Set Default Credentials
mon.Credentials = System.Net.CredentialCache.DefaultCredentials;
//Get Analytic Reports
if (!Page.IsPostBack)
{
//Get Analytic Reports
//The web method GetReportViews gets all reports created in Dashboard Designer.
PPSM.ReportView[] PPSReports = mon.GetReportViews();
PPSM.BpmPropertyText ReportName;
//Iterate through each of the reports
foreach (PPSM.ReportView PPSReport in PPSReports)
{
ReportName = (PPSM.BpmPropertyText)PPSReport.Properties[0];
Response.Write(ReportName.Text + "<br>");
//Store ReportName in the combo list item text property,
//and the GUID in the value property…
//many of the PerformancePoint Web Service methods require a GUID
ListItem liReportListItem = new ListItem();
liReportListItem.Text = ReportName.Text;
liReportListItem.Value = PPSReport.Guid.ToString();
ddlPPSReports.Items.Add(liReportListItem);
}
}
}
protected void ddlPPSReports_SelectedIndexChanged(object sender, EventArgs e)
{
//Use the GetMDX method…Note the second parameter is the report version…
//I arbitrarily just picked version 1… lblMDX.Text = mon.GetMdx((new Guid(ddlPPSReports.SelectedValue)),0);
}
}
Developers could also use this service to get the connection strings for OLAP data...
PPSM.DataSource MyDataSource = new PPSM.DataSource();
MyDataSource = mon.GetDataSource(new Guid("<GUID String for PPS Data Source>"));string strConn = "Provider=MSOLAP.2;Data Source=" +
MyDataSource.ServerName + ";Initial Catalog="
+ MyDataSource.DatabaseName;
If you had already retrieved MDX from a report, you could then fill a data set with something like this...
AdomdConnection conn = new AdomdConnection(strConn);
DataSet ds = new DataSet();
ds.EnforceConstraints = false;
ds.Tables.Add("Results");
AdomdCommand cmd = new AdomdCommand(strMDX, conn);
AdomdDataAdapter myDataAdapter = new AdomdDataAdapter(cmd);
myDataAdapter.Fill(ds.Tables["Results"]);
return ds;
So as you can see, theoretically, there is some opportunity to re-use the logic created by the business user via the Dashboard designer, in this case the data sources, and the mdx.
Next Steps: Key Performance Indicators?
To tell you the truth, I was actually searching for something a bit higher level than what I've demonstrated here...I was hoping the business user could define KPIs and their thresholds, and another application could simply retrieve that status of that indicator...for example, the business user would define 20% profit margin as good, 10% profit margin as ok, and 3% profit margin as bad. Applications could then just query that KPI for the status. I'm sure this is possible, but at this point it is not immediately apparent. I'll keep you posted.
I often find myself trying to explain Business Intelligence to non-technical people. When faced with the question "What is Business Intelligence?", I normally reply…with a straight face…"Business intelligence is a set of methodologies, processes, architectures, and technologies that transform raw data into meaningful and useful information used to enable more effective strategic, tactical, and operational insights and decision-making….duh." (Sometime I leave out the "duh" part). Definition courtesy of Forrester.
After a brief uncomfortable silence, many times the answer is…"oh yeah, we do that. We get weekly reports put on our desk each week….I never look at them."...Or..."we have a balanced scorecard that is supposed to help us with that…although I'm not really sure where to find it."...Or, the absolute worst…
"We have a dashboard that shows some data, but I'm not sure the data is accurate or up to date". In short, they've got the technology lying around somewhere…it's just not fully utilized.
My point is that business intelligence is not a technology. Nor is it a "project" with a start date, end date, and budget. Business Intelligence is more about the culture of an organization. Does the organization believe that the complex and often expensive processes of collecting, analyzing, and displaying data will yield some sort of advantage in the market place? Equally important, is that attitude pervasive across the entire organization?
Bottom Line: You can implement all the right technologies and follow all the correct "best practices" and still have a failed BI implementation. Technology might enable the aggregation and organization of data, but it is the culture of an organization which will allow it to make use of that information and differentiate itself in the market place.
There is no silver bullet here…there is no 5 step program to better B.I., no new development methodology that will increase BI success rates, and just because you implement your Business Intelligence initiative with a "top down" approach, or get "buy in" from your line-level employees, it will NOT assure success. Really, it's a matter of going into such an initiative with a whole lot of vision of how things could be, and a little bit of cynicism on how things could turn out. Recognize that processes might have to change, that disengaged workers may no longer feel at home, and that there will often be delays as these details are worked out.
The good news is that for many organizations the benefits amount to something far better than simply getting quicker and easier access to information. It results in a better understanding of customers, competitors, supply chain, operational processes, etc.. Furthermore, it allows the formulation of evidence-based strategies and tactics…but that is the subject for another blog. Check out "Competing On Analytics" by Thomas Davenport and Jeanne Harris if you want to know more about companies that have done just that.
This is a repost of an entry i did a few months ago...
Recently a client of mine had a need to display some financial ratios in a PerformancePoint dashboard. These were relatively standard ratios, yet the source data from which these ratios were calculated (a SQL Server Analysis Cube) did not pre-calculate these ratios for us. While it was decided ultimately these calculations should be done at the cube level, in the interest of time, we realized that the PerformancePoint KPIs would have to do the calculations for us, at least in the short term.
Unfortunately there isn't a very business-user focused method of doing calculations within a PerformancePoint KPI. You can't simply select your Current Assets measure and divide by your Current Liabilities measure. Instead you must use an MDX tuple formula. I really hope that eventually some sort of expression builder is available for these situations, but in the mean time, here are the steps to create a ratio based on balance sheet data in the Adventure Works DW database.
Create an Adventure Works data source, and point it to the Finance cube in the Adventure Works DW Analysis Services database. (It's actually just a Finance perspective but that's another blog)
Create a new KPI, and edit the data mappings for the Actual "metric" by clicking on the link
Click "Change Source" and select the Adventure Works source you just created
Click on the "Use MDX tuple formula" and type in the appropriate MDX formula . (you DO know MDX, right?)
Note: For demonstration purposes the above MDX statement reference members by name, not key. In a real implementation it would be wise to reference them by ID. By the way, for those that care, this is my favorite MDX resource: http://www.databasejournal.com/features/mssql/article.php/1495511.
Add this and any other ratios to a scorecard, add the scorecard to a dashboard, and deploy the dashboard.
I'm beginning to suspect that the use of PerformancePoint, at least in it's early releases, will require at least functional knowledge of MDX and PerformancePoint Expression Language (PEL) for PerformancePoint Planning, neither of which are exactly business-user friendly. That being said, many of these types of calculations will be similar, so it's not unrealistic for a very data-savvy business user to be able to handle some of these expressions without TOO much I.T. assistance.