Microsoft Based Planning and Budgeting solutions revisited

By Irit Eizips

In January 2009 Microsoft made the announcement that bed farewell to its PerformancePoint Planning initiatives and rolled its Business Intelligence (BI) solution into SharePoint. The common question asked is – Did Microsoft completely abandon the Corporate Performance Management concept?

For those of you who are still unsure what this concept represents and how it differs from Business Intelligence, I offer the following definition: Corporate Performance Management (CPM) is a methodology which helps organizations to forecast, monitor, analyze and react to various business drivers and performance indicators, using improved organizational processes, metrics and software tools. Components of Corporate Performance Management include – scorecards, reports, forecasts, budgets or key performance indicators.

As a CPM consultant currently working for a reputable BI consulting firms, CapstoneBI (oops… “a bit” of a self promotion) , I set out to the Microsoft BI conference in New Orleans (June 2010) to weigh whether one could implement the CPM components successfully using the Microsoft platform.

I specifically looked for Performance Management software systems that leverage the Microsoft platform. Those would typically be sophisticated database applications that are capable of automating and supporting many finance activities – including budgeting, forecasting, business modeling, decision support, strategic planning, and consolidation and reporting – in a single, integrated platform (in this instance, Microsoft’s).

There were three CPM solution booths at the conference, including: deFacto, Prophix and Clarify Systems. Another CPM solution provider I was introduced to by the Microsoft team was Tagetik, the Italian Microsoft partner, who only announced their tool’s integration with Microsoft integration in March 2009. Out of the four, the only Microsoft strategic alliance partner in the CPM space was Clarity Systems (apparently a big deal). To put things in perspective, out of 60 thousand partners, less than twenty are recognized as strategic partners.

All four vendors, use the Microsoft BI stack to fully integrate in providing the capabilities of forecasting and budgeting, consolidation, financial modeling as well as external financial reporting.

On the surface, it appears that Tagetik (the “new kid” on the block”) does a really nice job at integrating with SharePoint, whereas Clarity Systems offers the most comprehensive pre-built templates for the various financial budgeting models. Prophix introduced a nice tool to integrate their solution with SharePoint, whereas deFacto can quickly migrate existing PPS Planning implementations.

The good news is that there are some choices if your company is currently shopping for a budget and planning tool. However, clearly, a tool assessment process would ensure selecting a tool that would meet your business requirements, resource constraints as well as your company’s overall IT strategy. Hey… did I mention that CapstoneBI specializes in helping our clients in that regard?! …

 

Sources: Microsoft BI Conference, New Orleans (6/2010); CFO Research Services (2/2005)

.

ProClarity Resources

Hard to find Training, Whitepapers, Webcasts, & Case Studies

By Dan Meyers

Believe it or not, ProClarity is not going away anytime soon. In fact, I have done quite a few demos lately at some rather large companies. In addition to that, I often get asked by clients about online training material which tells me that there are still new people using the product every day. Many people have problems finding this training material because there is not a whole lot of it available on the web anymore. Below is a link to some good resources on the PerformancePoint website (I guess they consider ProClarity a ‘Previous Version’ of PPS).

http://www.microsoft.com/business/performancepoint/productinfo/proclarity/proclarity-overview2.aspx

Long live ProClarity!

.

T-SQL Fundamentals

Comparing Similar Approaches to Writing Queries

By Dan Meyers

I often get asked by clients about the “best” way to write a query. Whether they should use IN or EXISTS, table variables or temp tables, a LEFT OUTER JOIN or NOT EXISTS, etc… Most often it depends on what your data is like (does it contain NULL values for example), how are your tables indexed, and a number of other things. In order to make the best decision you really need to understand the details and internal workings of the query engine when using the various approaches that are available to you in T-SQL.

Below are some links to some blogs posts that I think do a good job of explaining the details about some of the most common questions I get when working at a client. Most of them are from Gail Shaw and her SQL in the Wild blog. The others are from SQL Server Central.

LEFT OUTER JOIN vs NOT EXISTS
EXISTS vs IN
IN vs INNER JOIN
NOT EXISTS vs NOT IN
Table Variables vs Temp Tables
JOINs - ON clause vs WHERE clause

Overcoming Calendar Limitations in PerformancePoint Planning

Some financial analysts may require to be able to present their budget and forecasts in both fiscal and calendar years. This becomes somewhat of a design challenge when it comes to PPS Planning since the application is set to handle only one Time dimension which is built at the onset of the application’s creation. Moreover, once generated, the time dimension that was created using the “calendar” wizard, is then very hard if not impossible to change.

Nevertheless, if a company does have a valid business reason to plan their budget using the calendar as well as the fiscal year, or important business requirements could not be accommodated using the existing application-generated time dimension, then the PPS Planning developer could consider the design solution suggested below.

In this example, the initial time dimension was built based on a fiscal calendar with July as the first month. However, the business user’s requirements are to create an alternative hierarchy where the months would be arranged in the order suggested below (a calendar year). Using the existing application’s Fiscal Calendar, this is not an easy task to be achieved. Therefore, we need to create a new dimension with the Calendar specification below using SSAS.

Using SSAS the following settings had been applied to create the additional time dimension for the budget application:

The figure below shows the relationship configured between time dimension (created using SSAS) and measure Group Tables in the PPS Planning application database.

URL Encoding in Reporting Services

Handling Special Characters Using HttpUtility.URLEncode()

By Dan Meyers

Reporting Services allows you to create hyperlinks in your reports using the Go To URL action. This is a very useful feature that provides you with the ability to make almost anything on the report a hyperlink. When creating links to other reports, this action type provides many advantages over using the Go To Report action type. For example, you can embed some javascript into the link so that the report opens up in a new window that is a specific size in a specific location on the screen. You cannot do this using the Go To Report action type.

Of course there are disadvantages too. When using the Go To URL action type you have to manually handle any special characters in you URL string. This is something that the Go To Report action type handles for you when it comes to the parameter values passed to the report. In my opinion, the advantages outweigh the disadvantages. So I always use the Go To URL action type and manually handle the special characters using the HttpUtility.URLEncode() function.

Before we can use the the HttpUtility.URLEncode() function we have to first add a reference to System.Web assembly.

Go to Report –> Properties –> References. Click [Add].

Scroll down a select ‘System.Web’ in the list and click [OK].

You should now see that the reference has been added.

Now that we have added the reference to System.Web, we now have to create a function in the report’s Code window that uses the URLEncode() function. Click on ‘Code’ in the left panel and enter the code below.

Public Function URLEncode (ByVal inURL As String) As String
Dim outURL As String
outURL = System.Web.HttpUtility.UrlEncode(inURL).ToString
Return outURL
End Function

We can now use our new report function in an expression anywhere in our report.

As you can see in the screenshot below, the function will convert any special charters and encode the URL. In this example, I am passing in a string for a MDX member for Barnes & Noble. You can see the before and after values below.

A Common Mistake
One common mistake that many make when taking this approach is that they try to use the HttpUtility.URLEncode() function directly from an expression. The expression will basically ignore the reference and not work. You have to use the function in the report’s Code window and then reference the report’s function in the expression for it to work.

Query the Members of an AD Group using a Linked Server

Using LDAP (SQL Dialect) Queries from SQL Server

By Dan Meyers

I was recently asked by a client about the best way integrate information from AD (Active Directory Services) into SQL and more specifically into Reporting Services reports. They wanted to be able to grab the userid of the person running the report and do a lookup in AD to see if the user was the member of a specific AD group or not. Based on whether or not the person running the report belonged to certain groups they wanted the report to behave a different ways. Based on the results on the AD query they wanted the reports to show or hide certain sections of the report and manipulate the data returned too.

The simplest and most effective solution for this problem is to use a linked server to query AD directly. Using the ADSI provider and a linked server in SQL Server we can query AD live and check to see if the report user is a member of a specific group or not. LDAP, the directory protocol defines the language that we need to use to query AD for user information. You can use either SQL Dialect or LDAP Dialect. I will be using SQL Dialect in my sample code. There are some known restrictions:

Known Restrictions:
· The number of records returned by the query is limited by a setting on the AD server. I believe the default value is 1000 records and can be changed by an administrator.
· The linked server cannot return multi-value fields such as the ‘memberOf’ field, but nothing restricts you from using them in the WHERE clause.

Since the client wants to do this at the report level we can use the UserID function that is built into Reporting Services to return the userid of the person running the report. This makes it easy for us to pass the userid down as a parameter to a SQL query.

It should be easy to use these examples to create a set of views, stored procedures, and/or functions that you can use to query AD over the linked server and integrate the data from AD into your reports.

Below is some sample code that gives you examples of how to:
· How to get the container/ADsPath information for specific groups
· Wildcard Searches Based on Names
· Searching for Groups and Users
· Using Multi-Value Fields in the WHERE Clause
· Returning the Members of a Group
· Checking to See If a User is the Member of a Group

--Run this to create the linked server
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

--Run this to get the ADsPath for each of the groups that have a names that starts with "BI_"
--Note the use of the "*" as wildcard characters
SELECT ADsPath
FROM OPENQUERY(ADSI,
'SELECT ADsPath
FROM ''LDAP://DC=<<MyDomainName>>,DC=com''
WHERE objectCategory=''group''
    AND CN=''*BI_*''
ORDER BY CN')

--Run this to get information about the "BI_" groups
--Note the use of the "*" as wildcard characters
SELECT sAMAccountName as Login, CN as Name, GivenName as FirstName,SN as LastName, DisplayName as FullName, UserAccountControl
FROM OPENQUERY( ADSI,
'SELECT sAMAccountname,givenname,sn,displayname,useraccountcontrol,CN
FROM ''LDAP://DC=<<MyDomainName>>,DC=COM''
WHERE objectCategory=''group'' AND CN=''*BI_*''
ORDER BY CN')

--Run this to get the information about the members of a specific group
--The value being used to filter on the memberOf field is the ADsPath value returned by the first query above
SELECT sAMAccountName as Login, CN as Name, GivenName as FirstName,SN as LastName, DisplayName as FullName, UserAccountControl
FROM OPENQUERY( ADSI,
'SELECT sAMAccountname,givenname,sn,displayname,useraccountcontrol,CN
FROM ''LDAP:// DC=<<MyDomainName>>,DC=com''
WHERE objectCategory=''person''
   AND objectClass=''user'' 
   AND memberOf=''CN=<<MyGroupName>>,OU=BI,OU=Administrator
Accounts,OU=MIS,OU=Corporate Users,DC=<<MyDomainName>>,DC=com''
ORDER BY CN')

--Run this to see if a specific user is a member of a specific group
--The value being used to filter on the memberOf field is the ADsPath value returned by the first query above
SELECT CONVERT(BIT,COUNT(*)) AS IsMemberOfGroup
FROM OPENQUERY( ADSI,
'SELECT sAMAccountname
FROM ''LDAP:// DC=<<MyDomainName>>,DC=com''
WHERE objectCategory=''person''
   AND objectClass=''user''
   AND memberOf=''CN=<<MyGroupName>>,OU=BI,OU=Administrator
Accounts,OU=MIS,OU=Corporate Users,DC=<<MyDomainName>>,DC=com''
   AND samaccountname=''<<MyUserName>>''')

.

Building Great Cubes: Tip 1

Less is More
By Peter Sprague


I have seen a lot of SSAS 2005 cubes that look very similar despite being created independently by different customers and partners across the US and Canada. They always look something like this:

- 5 or 6 dimensions with few or no natural hierarchies
- 70 or more (often many more...) attribute hierarchies, including the ever popular attribute [Customer Fax]
- 4 or more user defined time hierarchies
- Time attributes that contain all dates between 1901 and 2060
- 35+ measures
- Multiple measure groups
- Little relation to a specific user problem

Why is this a concern? These cubes makes it more difficult for the business user to navigate the data, and learn the tools. The complexity also makes it more difficult to understand the data that is returned. The biggest concern is that the users may misunderstand the data, and then base decisions on that misunderstanding. One of the reasons for this complexity is that analytic and monitoring tools (as opposed to reporting tools) directly surface the cube metadata as part of the user interface (Excel, PerformancePoint, ProClarity). Subsequently, multiple measure groups can be dangerous. Let me be clear... ALL of the features of SSAS 2005 are useful, they just aren't useful all the time.

These problem cubes rarely help users support a series of decisions. Seldom do they help users analyze their data to get to an actionable step.

Let me introduce Vilfredo Pareto, an Italian economist who in 1906 observed that 20% of the population in Italy owned 80% of the country's wealth. He also noted that this ratio held true for other scientific and economic distributions. A hundred years and hundreds of self-help books later, we have the Pareto Principle: 80% of the value comes from 20% of the resources. This principle holds true for cube design with the additional observation that the extra complexity from the 80% of the resources cost far greater than the 20% of the value that those resources provide. The next time you create a cube, strongly consider what design set will result in 80% functionality and stop there. In my experience, these cubes looks similar to this:

- 6 to 10 dimensions all with 1 strong natural hierarchy
- 6 or 7 exposed attribute hierarchies
- 1 time hierarchy that only contains dates relevant to the period the cube data
- All metadata expressed in business friendly terms
- Strong relation to a business problem

Remember our goal, to provide a cube that helps our users understand the data and supports further business action or decision. Consider this the first rule of cube design, Less is More.


.