A Balanced Scorecard, Business Intelligence & Six Sigma Blog by Laura Gibbons a.k.a Sassy Data Chic

I find it funny how we 'the collective human being" tends to believe what we are taught without questioning how factual or relevant a given learning is - For example, Pluto is no longer a planet as those born in the 70's and 80's were taught. And for decades prior, people believed our solar system consisted of 7 interplanetary objects called planets orbiting around a circumspect, gravity-free system of preconceived time, dwarf stars and theory, bound no more by the laws of astronomy, than is gravitational force an immutable space. Likewise, the black swan was considered myth until one made its way from Australia once a long-time ago, because the very existence was merely one belief, while baked in 1st -person factual experience. Did anyone go back and say "jeez, I was an id*ot for disbelieving you Joe Australian, seer of the black swan"? I highly doubt it. So, here is an account of legend gone extinct gone awry.

Quote

Monsters of Legend: Cryptozoology


Many animals once thought extinct or legendary have been eventually discovered to be real.

Fact: Only 19 percent of companies say their employees have all the data they need to make better, more informed decisions. ¹

Legacy "executive information systems", and in some cases, failed TQM, CRM and IVR - any business-dubbed acronym assigned to a once disruptive yet sexy piece of technology used to support some business process. And with each time said, the 'apparently innocuous acronym', became what corporate decision support & IT leaders would hate the most; the ever-present reminder that they, too, like you or I, believed the promised triple digit ROIs and NPVs and IRRs and TCOs; and we all know how we feel about the typical IVR experience.

And for all of the marketing hype and business-related jargon, none much materialized in the way of extreme financial rewards. In fact, the pains that were felt were invisible to the average eye. They came in the form of the levels of support (i.e. manpower) that was required to 'support' users and systems; but hey, they don't call it disruptive for nothing.

But I digress…

Let's just say the expectations set were far greater than the actualized returns.

Driven to succeed and with common understanding of importance of sharing information across silos and processes, and free from the burden of role ambiguity, employees can truly uncover ALL of the intersection points affecting a given process, product and plan. And with further analysis, one could establish the root causes and relative impact to the ultimate measure of success for most companies: profitability.

And with boundary-less organizations, come a well-oiled operations machine, free from the clutter and waste that infiltrates overly complex and bureaucratic organizations. The same ones that have meetings to plan meetingsàI know you know the ones …

What comes when one begins to think outside of the proverbial box, and think about things from the customer's vantage point, is a powerful sense of organizational self awareness, ultimately, bubbling up a call to action to your workforce, resulting in focus and drive...but that's where I stop…it is stalled by middle management, filled with less than promising mid-managers who know little about mentoring and growing their staff, far too consumed with feelings of being threatened by xxx up-and-comer staffie…

Using this 'voice of the customer' along a supply-chain is a powerful technique for designing and developing effective process and project metrics. In many ways, effective measurement determines the success of the projects -- A world where BI and project / processes have merged to evolve a new technique called Process/Project Intelligence, where the combined use of project management methodologies (including ISO9001, Agile (Scrum) or Design for Six Sigma) and business intelligence are merged into a true picture of the health of the organization across all verticals and horizontals, where all root causes are explored, eliminated and prioritized. While BI has undoubtedly evolved into a powerful set of technologies suitable for different types of users and information analysis needs over the early 'EIM' days, it is far from the 'single source of truth' that so many vendors spin into their sales pitch because most systems negate to weave the in element of the business managed processes that geminate out of every department within every organization at some level.

Honestly examining one's process by actual current state and stop blending the truth through their rose-colored, kaleidoscopic-view of operations, can one move into the thrilling world offered by those at the top of the Process & Project Intelligence pyramid. A place driven by the truth of data, where unified and systematic approaches to understanding one's business are commonplace rather than exception; where process optimization is key to project prioritization rather than secondary; where development initiatives are driven by the 'voice of the customer', rather than the voice of the developer.

"In God We Trust….All Others, Bring Data," w. Edwards Deming

It’s interesting to think about the world through the eyes of the leader who wakes up one day finding he or she finally “gets it” – They have seen through the fog of confusion when learning data warehousing concepts. Even worse, take a theoretical concept like Business Intelligence or Business Process Management, and try to explain that to them, and you will be given the blankest of stares. Taking this to the next level, how does one explain this to others when they are asked the inevitable, party ice breaker, “ what do you do for a living? ”

Take this image. I wanted to illustrate my point which is, in essence, illustrating the subject of my blog post. This dual meaning approach is the lens through which all life is viewed against the landscape of corporate nuances and entendres.

BUT, I digress….And now we’re back to the example...look at the levels, and two columnar labels, and tell me –> do you see the point(s) of integration?

Well 1st, the descriptions: this flow-down chart represents a logical grouping of customers into segments called ‘Value Tiers’ with a conditional statement limiting the ‘set’ to just the leaf level element = “Gold”.  This flow-down ends with a further subset of customers found within the Gold Tier / Segment called ‘Members’,’Joiners’, and/or ‘Leavers’.

Why would this subset stratification be important…? Once you know who your platinum  and/or gold customers are, do you believe you are ‘golden’, i.e. ‘have arrived’ aka can complete the analysis?

Forgive the pun; the answer is unequivocally NO…By following the tree of logic downstream, the analysis grows exponentially in its ability to uncover information of growing importance and relative value to the business.

This example represents a method known as a ‘customer segmentation’ or ‘customer value scoring’ model – Often, it is no more than applying a dimensional model architecture to the levels in the graphic. Other times, you can use more statistical analysis’ like partial least squares to score your customer base into set buckets as determined by your modeler. By understanding your most valuable customers, and subsequently, the least valuable (aka - most costly customers), you, dear reader, can arm yourself with extremely power intelligence to shape your loyalty program.

Now do you see how the segmentation model is a representation of both business process management driven off of cunning business intelligence? This example typically isn’t what is materialized for most people’s ‘example reference library, the place where we all go to when brainstorming in our minds, the metaphorical well we draw from when a client asks us for a best practice solution based on our vast knowledge of the thought space.

Now, take me for example…a thought leader in both BPM and BI, who has a tendency for breaking off from the pack in terms of thought leadership, in order to drive the industry to the next level. Disruptive at times, and often too conceptual for an organically visual society, I represent an enigmatic persona, compounded by my sheer excitement for all things ‘BI’. For me, it is one thing to think about BI and BPM as independent thoughts; it is an all together another universe of thought leadership to weave them seamlessly together into a marriage of process and data driven decision making, where the proof is in the returned, not perceived benefits.

If you are one of those ROI jockey’s who would rather see a triple digit ROI % than see realistic figures , who is wooed by the most dramatic or compelling ‘stories’, then you will appreciate the rewards that the marriage of BI and BPM will afford to you in not just theory…but also in practice. Segmentation, when stratified along the correct lines, with the correct balance of stick vs. carrot, a company can reap massive rewards in terms of loyalty which drives retention and repeat purchase likelihood which drives the financial bottom line.

yields    =  

 Everything in your Right Hand Tool Bar is Your Friend - Create a Model is where you start!

Dimensions - Day 1

Once you have your model, you have a cube within Analysis Services to analyze. Then you can slice your dimensions to get you the particular leaf level of information you need:

These correspond to your Excel PPS add-in report creation wizard options:

 

Where the magic is for getting those pesky financial ratios calculated correctly (outside of the cube) is in the Business Rules section, found by clicking on a model name and clicking on the 3rd tab (see image below).

PerformancePoint Planning Server (PPS Planning) uses PEL expression syntax as the PPS native query language -- On the up side, PEL can be converted to both Native SQL and Native MDX, which is a nice feature!

3 Types of Rules

1) ASSIGNMENT RULES - can copy and paste debug code that is output by PPS PEL into SQL Server Analysis Services' (SSAS) 2005 Management Studio client tool or SSAS 2000 MDX Sample App client tool for assignment rules ONLY 

Example assignment type business rule - real-world PEL expression:

--comment: all rules are nested within scope (); scope statements that are set explicitly or variably within the embedded 'THIS' statement below.

SCOPE(

[Scenario].[All Members].[Forecast],

[Time].[YQM].[July FY2008]:[Time].[YQM].[December FY2008],

[Account].[BizCorpAccount].[20010], -- external sales

[BusinessProcess].[Standard].[input],

[Entity].[BizCorpEntity].[All].leafmembers,

[TimeDataView].[All Members].[PERIODIC],

--[Currency].[All Members].[USD],

[Currency].[All Members].[Total All Currencies].LeafMembers,

[InterCompany].[All Members].[None],

[Geography].[BizCorpGeo].[ALL].leafmembers,

--[Geography].[BizCorpGeo].[NAM],

[Products].[BizCorpProducts].[All].LeafMembers,

[Version].[All Members].[Current]) ;

THIS = ([Account].[BizCorpAccount].[35300] * [Account].[BizCorpAccount].[35200]);

END SCOPE;

Here is the native MDX code for the above assignment rule to help bridge the gap between PEL and what you might be more used to when writing against multi-dimensional sources (assuming those interested in this section on MDX are OLAP developers and architects):

WITH CELL CALCULATION queryCalc

FOR

'([Measures].[Value],

[Account].[BizCorpAccount].[Level 11].&[5001],

[Scenario].[All Members].[Scenario].&[9],

    {[Time].[YQM].[Month].&[200807] : [Time].[YQM].[Month].&[200812]},

    [BusinessProcess].[Standard].[Level 06].&[8],

    Descendants([Entity].[BizCorpEntity].[(All)].&[0], 1073741823, LEAVES),

    [TimeDataView].[All Members].[TimeDataView].&[1],

    Descendants([Currency].[All Members].[(All)].&[0], 1073741823, LEAVES),

    Descendants([Geography].[BizCorpGeo].[(All)].&[0], 1073741823, LEAVES),

    [InterCompany].[All Members].[InterCompany].&[-1],

    Descendants([Products].[BizCorpProducts].[(All)].&[0], 1073741823, LEAVES),

    [Version].[All Members].[Version].&[1])'

    AS

    (([Account].[BizCorpAccount].[Level 03].&[5090],[Measures].[Value]) * ([Account].[BizCorpAccount].[Level 03].&[5089],[Measures].[Value]))

    SELECT NON EMPTY

    ([Measures].[Value],

    [Account].[BizCorpAccount].[Level 11].&[5001],

    NonEmpty(({[Scenario].[All Members].[Scenario].&[9]},

    {{[Time].[YQM].[Month].&[200807] : [Time].[YQM].[Month].&[200812]}},

    {[BusinessProcess].[Standard].[Level 06].&[8]},

    {Descendants([Entity].[BizCorpEntity].[(All)].&[0], 1073741823, LEAVES)},

    {[TimeDataView].[All Members].[TimeDataView].&[1]},

    {Descendants([Currency].[All Members].[(All)].&[0], 1073741823, LEAVES)},

    {Descendants([Geography].[BizCorpGeo].[(All)].&[0], 1073741823, LEAVES)},

 
  {[InterCompany].[All Members].[InterCompany].&[-1]},

    {Descendants([Products].[BizCorpProducts].[(All)].&[0], 1073741823, LEAVES)},

 
    {[Version].[All Members].[Version].&[1]})))

    properties [Scenario].[All Members].Key ,

    [Time].[YQM].Key ,

    [Account].[BizCorpAccount].Key ,

    [BusinessProcess].[Standard].Key ,

    [Entity].[BizCorpEntity].Key ,

    [TimeDataView].[All Members].Key ,

    [Currency].[All Members].Key ,

    [Geography].[BizCorpGeo].Key ,

 
  [InterCompany].[All Members].Key ,

  
[Products].[BizCorpProducts].Key ,

 [Version].[All Members].Key

  
ON COLUMNS

  
FROM [Forecast]

2) Definition RULES - same as assignment except calculated at run time and is not stored within the cube.

Sample expression for a definition rule for calculating variance to budget:

scope (

[TimeDataView].[All Members].[PERIODIC],

[BusinessProcess].[Standard].[INPUT],

[Account].[BizCorpAccount].[20320].leafmembers,

[Scenario].[All Members].[VarBudget_pct]) ;

This=(([Scenario].[All Members].[Budget]-[Scenario].[All Members].[Actuals])

/

[Scenario].[All Members].[Budget]

);

end scope;

3) INTERCOMPANY RECONCILE RULE - when one department is the receivable and the other is a payable - this rule balances your balance sheets

   

Properties within Reports:

• Under options:
  • Capture changes before workflow action(writes to SQL before workflow action - false by default, and best practice due to amount of data sent).
• Capture design time formulas - allows for writable cells in Excel reports - default is true
• Inherit design time formulas - on the excel side - true by default - can slow things down…Put formula at the All members so that the leafs inherit the formulas
• Options - should use NO spread for most instances

        

Security within PPS - Day 3

Windows AD or LDAP only

If you have write access but NOT read, you have nothing - READ MUST BE IN THERE 

If READ ONLY, at the ALL MEMBERS

IF WRITE, at the leaf level

Use same service account as SSIS  (or other ETL tools) instead of PPS service accounts - ensure your login not associated as an owner;    

Should be the same account as the SSIS package "Data import" service account - it will pass through the credential details of the account; not the same account as the ADMIN account or anything requiring ADMIN rites on SSAS…

   

Cycles 

A cycle is a time period is for 1 scenario (Actual, Budget, Forecast) and 1 time range

    Check Define a recurrence for forecast if it happens every year

   

• Dates start and end are for non recurrence entered next screen - first and last days to input in the forecast before close out
  • For recurring, select App Calendar, the period of recurrence (Month is typical)
  • For each repetition of the forecast, enter the time here for when assignments can be made and forecast can be edited. This is th e 'open' books period.

Summary slide for assigning cycles

        

Remember: Forecast cycles - outside container of time for all activities

Only 1 Cycle is 1 scenario x 1 model x 1 range of time

   

Assigned to user in Excel - submit and submit draft actually hit db; otherwise, local saved only

Once submitted, it is closed!
   

Status of submission:

Pending status first - waiting in queue to be picked up by PPS service

Wait Process - services picked it up Partial / Submitted - Data is in the Fact tables 

Between models, you can associate dimensions - especially important between forecast and corporate models when different time scenarios are used, or different accounts from the general ledger are part of the cube requirements, something harder than usual for an OLAP developer to get right. (the 1st test pass at least)

Please email me for the actual files from the list of options below...lauragibbons@scorecardstreet.com.

   

Remember...

1. 2 keys with your dimensions - 1) to check-in your model before you proceed to any of the offered 'Available Actions' and 2) you deploy and redeploy model after changes are made to the structure where you want to refresh your cube (fact) data;

2. 2 keys with your business rules -- when making changes or adding new assignments &/or associations, PPS Planning requires you 1) refresh and reprocess model data and 2) redploy rules
   

Now that I no longer work for my old boss, who took this post personally, I am adding it back by popular demand.

Workplace Metaphors and more...

(originally posted on blogger http://flowergirldujour.blogspot.com/ in 2006)