BI Blogs

Recently I’ve been reading @guykawaski’s book, Reality Check. One of the chapters is based on the classic post he wrote in 2006 called The Art of Execution. On the last day of the quarter in a very difficult economic climate I thought that the post was worth re-reading and writing about given its’ focus on goal setting, measurement, and of course execution. To paraphrase Guy:

• Goals must be measurable, achievable, relevant, rat-hole resistant, well-communicated, tracked on a weekly basis, and not too touchy feely (because “son, sales fixes everything!”).

Instead of a culture of measurement, Guy advises that you go further and establish a “culture of execution.” This means that you:

• Establish a single point of responsibility, follow through, reward achievers, and heed your “Morpheus” (although these days it’s Simon Cowell who really helps people “see things as they really are” on American Idol - which is about to get started).

All great advice. So be sure to read the full post, get the book, follow @guykawaski on Twitter, and really consider the goals you’re setting heading into the next month and quarter and how you’re tracking these goals over time. Enjoy your April Fools Day (avoiding Confiker I hope).

So you want to be on the latest and greatest software with your Business Intelligence platform…well watch out for this one.  Back in January this year I was in the middle of a major SQL Server 2008 and Windows Server 2008 upgrade when we started to experience some issues with our Reporting Services (SSRS) 2008 testing against Analysis Services (SSAS) 2008 data sources.  What we were doing was building out all of the SQL Server 2008 components with Windows Server 2008 and moving the SQL Server 2005 items to these new servers to perform testing.  All of the SQL Server 2005 components were running on Windows Server 2003.

The error that we were running into when trying to running SSRS 2008 reports against SSAS 2008 was the following:

The connection either timed out or was lost. Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. An existing connection was forcibly closed by the remote host

So after getting this error we made sure that Kerberos was properly configured and then started to dive into a more deeper analysis using WireShark and reviewing network traces.  We also made sure that all of the servers had updated firmware and drivers for the network adapters (http://support.microsoft.com/kb/942861) and we turned off all offloading on the adapters to see if that would resolve the issue including the TCP Chimney (sorry Santa, we closed the Chimney down) and UPD Checksum and any other Offloading that was setup on the network adapters (http://support.microsoft.com/default.aspx/kb/951037) – these properties can be found if you go into Device Manager—>Network adapters—>and check the Advanced tab of the properties of each network adapters.

The funny thing was that if we pointed SSRS 2008 against our SSAS 2005 server (that was running Windows Server 2003) the reports executed just fine.  We also ran all of the reports with issues from SSRS 2005 servers to the SSAS 2008 servers and they worked fine.  So what was going on?  After numerous testing and network traces we finally determined that this was out of our control and needed to contact Microsoft.

After going through more testing, network traces, SSRS log files, etc. and about seven support people I finally got a phone call one night while I was in Cub Foods getting a few groceries at about 8 or 8:30 at night.  The Microsoft engineer was with the SSAS team and he was aware of the issue…this was starting to pop-up with customers and we were about the fourth case of this that he had heard of

After additional testing it turns out there is an issue with AES (advanced encryption standard) aware operating systems (Vista, Windows Server 2008, Windows 7) and with Kerberos.  I was given a very detailed description of different messages that are used with Kerberos.  There is an issue with the decrypt data for security buffer not being the correct size for AES and this causes the error.  This occurs when you are using Kerberos and are trying to get AES aware operating systems to talk to each other.

So our workaround was to keep the front-end servers (SSRS, PerformancePoint, ProClarity, and SharePoint) on Windows Server 2003 and the backend servers for our databases on Windows Server 2008.  This was a very frustrating item to uncover and took us a few weeks of testing along with additional time working with Microsoft on the issue.  The Microsoft case has been moved over to the Windows Server team and they have stated that a fix should hopefully be available in May 2009 (need to perform a few months of testing and going through the red tape for the hotfix).

For more information in regards to this and a similar posting check out this link -  SSAS: Kerberos kills ‘large’ MDX queries on Windows Server 2008.  Sorry I didn’t get this posted sooner to warn more people, but hopefully this will now help out other people experiencing this same issue or planning their infrastructure upgrades

ORANGE PEEL AND CALUMO GROUP FORM STRATEGIC ALLIANCE TO TAKE ENTERPRISE BUSINESS
PERFORMANCE MANAGEMENT TO THE MICROSOFT DYNAMICS COMMUNITY

A Shared Vision for Joint Technology to Drive New Growth Opportunities

SYDNEY & LOS ANGELES – 31 March, 2009 — CALUMO Group and Orange Peel Corporation
have today announced a strategic alliance based on their joint release of Budgeting,
Forecasting and Reporting (BFR) for Microsoft Dynamics for Business Performance Management.
The application will provide an effective and scalable entry point for all Microsoft
Dynamics users to set a roadmap for business intelligence and planning, leveraging
the Microsoft Business Intelligence platform.

“Orange Peel has the deep understanding of BPM, the US market and the Microsoft Dynamics
portfolio required to bring tremendous value to the Microsoft Dynamics community.
BFR is the first fruit of our alliance and we couldn’t be more excited about the value
it can create for Microsoft Dynamics clients. The first steps to enterprise BPM are
always rapidly addressing immediate pain points and showing immediate tangible business
value. Our applications’ emphasis on performance, reliability and ease of use make
that a reality,” said Dominic Parsons, CEO, CALUMO Group.

Orange Peel CEO, Per Solli, further explains, “CALUMO 11 is the perfect platform for
BFR. The Microsoft Dynamics community can now achieve immediate results needed to
service their business and leverage their systems investment while staying firmly
on the Microsoft platform. When businesses are ready and able to invest in their future
to drive deeper deployment and greater value from their BPM, CALUMO provides the perfect
answer for scaling on the Microsoft platform.” By engaging the companies at the technology
and business levels, the alliance will allow both companies to drive new growth opportunities
and improve productivity for its customers.

Orange Peel has elected to only offer BFR through Microsoft Dynamics certified partners.
These partners have the extensive knowledge of their clients and the Microsoft Dynamics
range required to ensure their clients reap immediate benefit. Orange Peel are working
with both end users and partners to help them move through a road map for BPM that
sees them doing better business sooner.

About Orange Peel Corporation

Orange Peel Corporation is the North American distributor for CALUMO. OPC has a solid
background from implementing BPM solutions for both enterprise clients and the mid-market,
based on the Microsoft BI Stack. OPC is recognized for innovative thought-leadership
in the BPM arena and has adapted agile SCRUM methodologies for customers and partners
to use, in order to achieve quick results on their BPM investment. Orange Peel Corporation
head office is situated in Los Angeles, USA. For more information visit www.orangepeelcorp.com.

About CALUMO

The CALUMO Group (www.calumo.com) is a leading
provider of Performance Management solutions for enterprise-wide reporting planning
and Business Intelligence. Since 1998 the group has successfully delivered solutions
to a wide range of enterprises from SME’s to some of the largest listed companies
and government organisations. These solutions provide quantifiable and valuable business
insight, offering a single platform from which to integrate Corporate, Financial and
Operational performance and objectives.

The Group is a Microsoft Gold Certified Partner in Business intelligence (achieving
full competency in both Platform and Performance Management Specialisations), Data
Management Solutions and ISV/Software Solutions.

At last week’s SAS Global Forum, I sat down for a few minutes with Stephan Chase, Vice President of Customer Knowledge at Marriott Hotels. Chase’s team provides the analytics and predictive modeling to the marketing team within the Marriott Rewards business unit. I asked him to elaborate on an earlier comment he had made about the cultural impact of becoming an analytics-driven department within an organization.

You made a comment earlier, which I’ll paraphrase, about how mandating the use of predictive modeling within a department can be a bit unsettling for staff–how so?

When you change the way people are used to working, you can send an unspoken message that there was something wrong with the way they were doing things before. And it’s not so much that the previous approach was wrong–but that it could be better.

Giving yourself over to an analytic approach can be uncomfortable at first, because it can feel like you’re surrendering control to an analytic process. If you lived in the world of qualitative focus groups, and now you’re being asked to put yourself into the quantitatively measured world, it can be very frightening. But once the marketing analysts saw that both they themselves and their marketing counterparts are both incorporating this more measured approach as a team, the anxiety went away.

How did you get started incorporating predictive modeling into the Marriott Rewards team?

Within three months of moving to the Marriott Rewards team, I wrote a white paper on how we should incorporate predictive analytics and hired a consultant to help create a roadmap.

What presented an interesting challenge was that I was accountable for developing this new analytical process and getting people on board, when many of the people whose buy-in I needed didn’t even report to me. But achieving that made it even more valuable–like organic implementation, people are more committed that way.

How do you then go on to build credibility and awareness for an analytical approach, especially within an organization to which this would be new?

You have to live it for awhile. People respond to truth and sincerity but when it becomes political, the demand evaporates. You have to stick to your metrics and this takes a while. You need to mean what you say and admit when something you try didn’t work.

Marriott wasn’t entirely new to predictive modeling–we had been doing it in the pricing area for a while. But I was new to the hospitality industry. People in marketing were curious about predictive modeling and they were impressed by the analytics, but thought it was an obscure process. So we started small, testing our own modeling, and hit it big with a huge success. Showing the incremental success is important because it builds excitement.

People want to do meaningful work, and when you can show analytic results in an objective way, there will be demand for it. Sure, not everything we do succeeds–especially if the process becomes the focus instead of the campaign being the focus. If the campaign is the focus, it objectifies the process and makes it less personal.

I can’t overstate the value of incremental success, similar to what [SAS CMO] Jim Davis said in his Business Analytics approach. He said you can start with small wins in data integration, analytics and reporting, then grow from there.

Are you by nature a very analytical person?

For working in an analytical field, I actually test very high as intuitive in Myers Briggs, I’m very theoretical. I even code that way. Sometimes we’ve proven something with a small group of customers but we need to test ourselves to determine if we’ve found a real trend or just a blip, a distraction. We work very hard to test ourselves. We ask ourselves constantly, Is this as good as we can get to approximate the truth?

The team loves this, which is why I love working for Marriott. It’s a great company. Our IT group has been around for a long time, we know each other well and we respect and understand each other–and I bring them interesting work to do. We’re both highly functioning teams. Now the IT people are evangelizing the value of our marketing analytics tools.

Do you feel like an analytics evangelist?

Absolutely, and predictive modeling has kind of become my thing at Marriott. Not too long ago, I was in a meeting with my boss and the entire Marriott Rewards team. Before I knew it, and without me even saying anything, the marketing team was asking how they could use predictive analytics in some new ways they hadn’t thought of before. It was the most gratifying conversation I’ve had in a long time because I felt like we had really gained some mind share.

I have long liked the Oracle merge statement - so much more elegant (and efficient) than the “try insert and if it fails have a go at update” or “try update and if there is nothing to update add the row” strategies that used to be used. But did you know that you can (from Oracle 10 onwards) use merge to just add new records without updating existing records?

So, why did I want to do this? My customer has a table of hierarchy members, that is a table that looks quite a lot like the EMP table in the SCOTT schema, except that their table is a few million rows long; they also have a smaller table of new or updated members (let’s call it NEW_EMP) that needs to be augmented with the parent records from the existing table (EMP) but only if there is not a replacement record for the parent in NEW_EMP.

We can simply build a merge statement and omit the WHEN MATCHED THEN UPDATE SET… clause and just use the WHEN NOT MATCHED THEN INSERT on its own.

MERGE INTO SCOTT.NEW_EMP NEW_EMP_1 USING
(SELECT DISTINCT EMP.EMPNO EMPNO,
EMP.ENAME ENAME      ,
EMP.JOB JOB          ,
EMP.MGR MGR          ,
EMP.HIREDATE HIREDATE,
EMP.SAL SAL          ,
EMP.COMM COMM        ,
EMP.DEPTNO DEPTNO
FROM SCOTT.EMP EMP,
SCOTT.NEW_EMP NEW_EMP
WHERE ( NEW_EMP.MGR                 = EMP.EMPNO )
) MERGE_SUBQUERY ON ( NEW_EMP_1.EMPNO = MERGE_SUBQUERY.EMPNO )
WHEN NOT MATCHED THEN
INSERT
(
NEW_EMP_1.EMPNO   ,
NEW_EMP_1.ENAME   ,
NEW_EMP_1.JOB     ,
NEW_EMP_1.MGR     ,
NEW_EMP_1.HIREDATE,
NEW_EMP_1.SAL     ,
NEW_EMP_1.COMM    ,
NEW_EMP_1.DEPTNO
)
VALUES
(
MERGE_SUBQUERY.EMPNO   ,
MERGE_SUBQUERY.ENAME   ,
MERGE_SUBQUERY.JOB     ,
MERGE_SUBQUERY.MGR     ,
MERGE_SUBQUERY.HIREDATE,
MERGE_SUBQUERY.SAL     ,
MERGE_SUBQUERY.COMM    ,
MERGE_SUBQUERY.DEPTNO
)
;

This construction is probably easier to understand than the traditional INSERT operation where we would need to self-outer join the NEW_EMP table to find the the candidate rows to insert.

Hmm, you wait for years for commercial tools for monitoring Analysis Services (the only one I’d ever seen before was Companion for Analysis Services from SQLMinds) and then two come out at once. One of these tools I’ll be blogging about towards the end of this week, hopefully – I’ve had a sneak preview and it looks very cool – but today I found out the following from SQL Solutions:

• OLAP Heartbeat: http://www.sqlsolutions.com/products/OLAPHeartbeat/index.html
• OLAP Performance Advisor: http://www.sqlsolutions.com/products/olap-performance-advisor/index.html

both and give them a thorough test as soon as I can.

Here’s an interesting MDX nugget. The other day I was working on something similar to the following query, that finds the count of the number of customers who bought something in the current month as well as the previous month:

WITH
MEMBER MEASURES.TEST AS
COUNT(NONEMPTY(
NONEMPTY(
EXISTING [Customer].[Customer].[Customer].MEMBERS
, [Measures].[Internet Sales Amount])
, ([Measures].[Internet Sales Amount], [Date].[Calendar].CURRENTMEMBER.PREVMEMBER))
)
SELECT MEASURES.TEST
*
[Product].[Subcategory].[Subcategory].MEMBERS ON 0,
[Date].[Calendar Year].[Calendar Year].MEMBERS
*
[Customer].[Country].[Country].MEMBERS ON 1
FROM [Adventure Works]

On my laptop, this runs in almost 16 seconds – not good. What I was doing here was making the assumption that it was a good idea to do the EXISTING first, to filter the set of customers down as much as possible, then do the two NONEMPTYs. In fact, what I found was that by moving the EXISTING to after the NONEMPTYs had been evaluated the query ran much faster:

WITH
MEMBER MEASURES.TEST AS
COUNT(EXISTING 
NONEMPTY(
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, [Measures].[Internet Sales Amount])
, ([Measures].[Internet Sales Amount], [Date].[Calendar].CURRENTMEMBER.PREVMEMBER))
)
SELECT MEASURES.TEST
*
[Product].[Subcategory].[Subcategory].MEMBERS ON 0,
[Date].[Calendar Year].[Calendar Year].MEMBERS
*
[Customer].[Country].[Country].MEMBERS ON 1
FROM [Adventure Works]

On my laptop now, the above query executes in 3 seconds. Why is this? To be honest, I’m not completely sure… looking in Profiler and Perfmon I see differences, but not many; these things are better left to the likes of Mosha who know what’s going on inside in the formula engine. But anyway I thought I’d post this up because it’s a common form of calculation – so if you are using NonEmpty and Existing together, do the NonEmpty first and then the Existing.

Following are the key points that needs to considered when setting up a Data Integration environment.

Data Integration Environment Setup

• Repository setup and folder structures to hold the development objects (code) like transformations/mappings/jobs.
• Coding standards and development process.
• Document templates for low level design specifications and for capturing test case & results.
• Version management process of the objects.
• Backup and restore process of the repository.
• Code migration process to move the object from one environment to the other like from development to the production environment.
• Recommended configuration variables like commit interval, buffer size, log file path etc
• User group and security definition
• Integration of the metadata of the database with the DI metadata and that of the DI metadata with the reporting environment
• Process for Impact Analysis for change request
• Data Security needs for accessing the production data and the process of data sampling for testing
• Roles and Responsibilities of the environment users like Administrator, Designer etc

Data Integration Process Design

• What are the different data sources and how are they to be accessed.
• How the data are provided by the source systems, is it incremental or full feed, how to determine the incremental records.
• What are the different target systems and how would the data be loaded
• Validation and reconciliation process for the incoming source data
• Handling late arriving dimension records
• Handling late arriving fact records
• Having dynamism in the validation and transformation process
• Error handling process definition
• Table structures for holding the error data and the error messages
• Process control or audit information gathering process definition
• Table structures for holding the process control data
• Determining reusable objects and its usage
• Template creation for commonly used logics like error handling, SCD handling etc.
• Data correction and reentry process
• Metadata capture during the development process
• Means of scheduling
• Initial data load plan
• Job failure and restartability methods

Edwin Hubble first proposed his now famous Hubble’s law in 1929 and that is considered the first observational basis for the expanding space paradigm – in simple words, Hubble proved (using Doppler effects and Red shifts) that the universe is expanding at an accelerated pace.
At a more earthy level, it is my contention that the universe of Business Intelligence is also expanding at a fairly rapid rate. If Hubble had been a BI practitioner, he would have probably explained the whole expansion quantitatively but I (and you as a reader) have to be content with some qualitative analysis.

Business Intelligence has come a long-way from the days when Howard Dresner defined it in 1989 as “a set of concepts and methodologies to improve decision making in business through use of facts and fact-based systems”. You can read about the changing face of business intelligence at this link

Looking beyond definitions, practitioners would most certainly feel the expansion of BI boundaries in the last 8-10 years. What was initially a technology centric Data Warehousing domain has expanded to subsume areas like Performance Management, Business process analytics, Data and Text Mining, Packaged Industry-specific analytics to name a few.

With the advent of real-time operational BI, Information Services Bus architecture, Complex Event processing (CEP), Business Activity Monitoring (BAM) etc., I will not be surprised if the term Business Intelligence itself gets subsumed under a much more broader & comprehensive concept. With that, I rest my case regarding the relationship between Business Intelligence and Hubble’s law.

Now here is the real point of this blog post. Though the boundaries of Business Intelligence are definitely expanding, there is still lot of play left well-within the boundaries. Organizations fundamentally require insights into their business processes so as to the optimize them. Enterprises embarking on BI initiatives should have one eye on the expanding boundaries but should be pragmatic enough to keep the other eye on what gets delivered at the end of the day. Incremental Business Intelligence is still the right step forward in the BI journey for many organizations but awareness of new developments would make them ready to take the leap when their business demands it.

Fundamentally, BI as we know it, encompasses a wide spectrum and is constantly growing. Organizations should be able identify the right kind of BI that is applicable to them given their current business context and be nimble enough to make changes to the contours of BI within their business as they move forward.

Thanks for reading. Please do share your thoughts.

My previous post on business process fundamentals concluded with a friendly exhortation to BI practitioners inciting them to view their craft from the point of optimizing business process.

So the next time you are involved in any BI endeavor, please ask this question to yourself and the people involved in the project – “So which business process is this BI project supposed to optimize, why and how?” I define ‘Optimization’ loosely as anything that leads to bottom-line or top-line benefits.

Business processes by its very definition belong to the industry domain. Companies have their own business processes – some of them are standard across firms in that particular domain and many of them are unique to specific companies. Efficiency of business processes is a source of competitive advantage and the fact that ERP vendors like SAP has special configurations for every industry illustrates this point. So by corollary, for BI to be effective in optimizing business processes, it has to be tied to specific industry needs creating what can be called as “Verticalized Business Intelligence”. (V-BI in short)

At Hexaware’s Business Intelligence & Analytics practice (the company and team that I belong to), we have taken the concept of V-BI pretty seriously and have built solutions aimed at industry verticals. You can view our vertical specific BI offering at this link and we definitely welcome your comments on that.

Though Verticalized BI is a powerful idea, companies typically need an “analytics anchor point” to establish a BI infrastructure before embarking on their domain specific BI initiatives. The analytics anchor point, mentioned above, should have the following characteristics:

• All organizations across domains should have the necessity to implement it
• Business process associated with these analytics needs to be fairly standardized and should be handled by experts
• Should involve some of the most critical stakeholders within the organization as the success of this first initiative will lay the foundation for future work

Based on my experience in providing consulting services for organizations in laying down an Enterprise BI roadmap, I feel that “Financial Analytics” has all the right characteristics to become the analytics anchor point for companies. Financial Analytics, the common denominator, typically comprises of:

• General Ledger Analysis – (also known as Financial Statements Analysis)
• Profitability Analysis (Customer / Product Profitability etc.)
• Budgeting, Planning & Forecasting
• Monitoring & Controlling – The Dashboards & Scorecards
• General Ledger Consolidation

The above mentioned areas are also classified as Enterprise Performance Management. The convergence of Performance Management and BI is another interesting topic (recent announcements of Microsoft have made this subject doubly interesting!) and I will write about it in my future posts.

In my humble opinion, the prescription for Enterprise BI is:

• Select one or more areas of Financial Analytics (as mentioned above) as your first target for Enterprise BI.
• During the process of completing step 1, establish the technology and process infrastructure for BI in the organization
• Add your industry specific BI initiatives (Verticalized Business Intelligence) as you move up the curve

I, for one, truly believe in the power of Verticalized BI to develop solutions that provide the best fit between business and technology. That business and IT people can sit across the table and look at each other with mutual respect is another important non-trivial benefit.

Thanks for reading. Do you have any other analytics anchor points for organizations to jumpstart their BI initiatives? Please do share your thoughts.

What is ‘Analytics’ - A business intelligence application with ready to use components for data analysis, we also refer to it as ‘packaged analytics’. ‘Business Analytics’ refers to analytics applications that support analysis of data collected as part of a business process.

In similar lines we can define an analytics application that supports analysis of data collected as part of a ‘computer user’ daily activity as ‘Personal Analytics’.

Business systems evolved from the state of building custom applications to a state of configurable generic Enterprise Resource Planning (ERP) systems. Now we have configurable generic business intelligence applications called ‘Business Analytics’ which have evolved from the state of building custom business intelligence applications.

The ERP systems are designed to collect the business data where as the Business Analytics systems are designed to analyze the collated business data, so one of the key sources for a Business Analytics application is an ERP system. Data analysis is a next logical step after data collection, the ERP vendors like Oracle, SAP, Microsoft got delayed in addressing this specific requirement of data analysis. In the last two years we have seen some finer business intelligence products being acquired by the ERP vendors. Clearly the customers who are on ERP products would get a better platform that can talk to their ERP applications for data analysis.

It’s a reality that not many companies, at least the larger (>USD 500million) companies would not run their entire business in one ERP system. Consolidating all applications to one single ERP platform will not happen immediately, multiple ERP and custom applications would get added if the company grows through acquisitions, hence existence of multiple transaction systems cannot be avoided. The number of customers embracing packaged analytics from the ERP vendors will increase as the flexibility of the business analytics applications from the ERP vendors matures to accept data from other outside applications.

Logical Data Model to Packaged Reports

The business analytics applications grew step by step as following

• 1. Logical data model - as a first step towards the formation of packaged analytics, companies like IBM, Teradata provided industry specific logical data models (LDM) to help customers build their enterprise data warehouse. The LDM was based on the business process and provided the required jumpstart to enable the integration of data from multiple source systems effectively. We also have certain industry endorsed LDMs like Supply-Chain Operations Reference-model (SCOR), Public Petroleum Data Model(PPDM
• 2. Metrics definition - LDMs led to the next step of defining metrics to measure the performance of the business process. The required data for the metrics that were specific to a business process were extracted (virtually/physically) into data marts as analytic data models in a fact-dimension data model
• 3. Semantic Layers - the next step was the creation of semantic layer over the data mart to enable adhoc querying and report generation
• 4. Reports and Dashboards - then we had set of reports and dashboards delivered over the semantic layer

Still the packaged analytics are positioned as a data mart application addressing specific business process like HR or Customer Relationship, unlike ERP systems which addresses complete end to end business process of an organization…there is still more time to go for an Enterprise Analytics Application to be established.

Business Intelligence has a fairly wide scope but at the fundamental level it is all about “Business Processes”. Let me explain a bit here.
BI, without the bells and whistles, is about understanding an organization’s business model, its business processes and ultimately find the reason (analytics) and way to optimize the processes. The actions are carried out based on informed judgments (aided by BI), to make the organization better in whatever endeavor it has set itself to accomplish.

Assuming that BI practitioners are convinced that understanding business process is critical to their work, let me delve a bit into the basics of it.

1) What is a business process? (As a side note, one of the best explanation for business models is given by Joan Magretta in her book ‘What Management Is”)

Business processes are set of activities involved within or outside an organization that work together to produce a business outcome for a customer or to an organization. The fact is that for an organization to function, there are many outcomes that are required to happen on a daily basis.

2) What are BPM Tools?

Business Process Management (BPM) tools are used to create an application that is helpful in designing business process models, process flow models, data flow models, rules and also helpful in simulating, optimizing, monitoring and maintaining various processes that occur within an organization.

3) The Mechanics of Business Modeling

Business Process Modeling is the first step, followed by Process Flow Modeling and Data Flow diagrams. All these 3 diagrams and associated documentation will help in getting the complete picture of an organization’s business processes. Brief explanation of these 3 types are given below:

a) In Business Process Modeling, an organization’s functions are represented by using boxes and arrows. Boxes represent activities and arrows represent information associated with that activity. Input, Output, Control and Mechanism are the 4 types of arrows. A box and arrows combination that describes one activity is called a context diagram and obviously there would be many context diagrams to explain all the activities within the enterprise.

b) Process Flow Modeling is a model that is a collection of several activities of the business. IDEF3 is the process description capture method and this workflow model explains the activity dependencies, timing, branching and merging of process flows, choice, looping and parallelism in much greater detail.

c) Data Flow Diagrams (DFD) are used to capture the flow of data between various business processes. DFD’s describe data sources, destinations, flows, data storage and transformations. DFDs contains five basic constructs namely: activities (processes), data flows, data stores, external references and physical resources.

Just like the data modeler goes thro’ conceptual, logical and physical modeling steps, a business process modeler creates the Business Process Models, Process Flow Models and Data Flow Diagrams to get a feel for the business processes that take place within an enterprise.

Thoughts for BI Practitioners:

1. Consider viewing BI from the point of optimizing business processes
2. Might be worthwhile to learn about Business Process Modeling, Process Flow Modeling and Data Flow Diagrams
3. Understand the working of BPM tools and its usage in the enterprise BI landscape
4. Beware of the acronym BPM. BPM is Business Process Management but can also be peddled as Business Performance Management.
5. My view is that Performance Management is at a higher level, in the sense, that it is a collective (synergistic) view of the performance of individual business processes. A strong performance management framework can help you drill-down to specific business processes that can be optimized to increase performance.

Thanks for reading. Please do share your thoughts.

We observe many BI Project Sponsors clearly asking for an Analytics Package implementation to meet business needs; the benefit is that it saves time. By deciding on an analytics package we can get the application up quickly and comes with all typical benefits of a ‘buy’ solution against a ‘build’ solution.

So what are the key parameters that we need to look for in choosing an Analytics Package. The following would be the points to consider in choosing an Analytics Package, in the order of importance.

1.The effort to arrive at the right data model for a BI system is huge and as well quite tedious, so a comprehensive ‘Data Model & Metrics, Calculations’ from the package is very important.

2.The flexibility and the openness in managing Data Model is also very critical, some of tools to manage the data model elements that can be looked for are

• Ability to browse the data elements and its definitions
• Support for customization of the data model without getting back to the database syntax
• Auto Source System profiling and field mapping from the source systems to the data model
• Enabling validation of data type, data length of the data model against the source system field definitions
• Means to ensure that customization of the data model in terms of field addition doesn’t happen when a similar element exists
• Availability of standard code data as applicable to the functional area
• Supporting country specific needs in terms of data representation

3. ETL process for a BI system is also a major effort. Though the absolute effort of pulling the data and making it available for the package in the required format cannot be avoided, availability of plug-ins that can understand the data structure from typical systems like ERP would save good amount of effort.

4. Availability of ETL process for typical data validation as part of ETL is also a must; integration with any data quality product would be valuable

5. Ability to support audit and compliance requirements for data usage and reporting

6. Integration of the package with industry specific research data from vendors like D&B, IMS etc to enable benchmarking the performance metrics against industry peers/competitors

7. Customizable Security Framework

8. Semantic layer definition with formulas, hierarchies etc

9. Ready to use Score Cards and dashboard layouts

10. Pre built reports and portal

Often all the pre delivered reports under go changes and are almost completely customized when implemented. So availability of a larger list of reports itself doesn’t mean a lot since most of the reports would be minor variations from one other. Certain compliance reports would be useful when it comes along with the package; these would be published industry standard report formats.

Definitely an evaluation phase to test the Analytics products capability on a sample of the data before choosing it is a must, the above ten points would the evaluation criteria during this exercise.

Dear Readers, Greetings!

In the current business scenario, securing the data and restricting the users from what rows and columns of data they can see and what rows and columns of data they cannot see is very important.  We can secure the rows of data by row level security. Some people call this as ‘Fine grained access control’.  We can secure the columns of data by column level security. This is popularly called in Business Objects as ‘Object level security’

ROW LEVEL SECURITY

There are various ways through which the row level security can be implemented in a Business Objects environment.

One way is by securing the datamart. In case of this approach, the datamart is secured - meaning the security policies and rules are written in the datamart. Technically, a security table can be created and maintained having the users / groups with corresponding access rights.  Security policies can have a logic to compare the active logged in user and security table. All the users accessing the datamart are provided access to their data only after executing the security policies. We can also embed the security policies and rules in a view. A good example for row level security is — Non-Managers cannot see the data of   co-workers however managers can see the data of his / her sub-ordinates. In Oracle (for example), we can create a non-manager and manager views with the security rule (<security_table.user> = “USER”). The security views are imported in the Business Objects ( BO) universe and the reports use these security views through the universe. The main ADVANTAGE of securing your datamart is that your security rules can also be used by many other BI tools ( Cognos, Microstrategy )  as the rules are built at the datamart and NOT at the Business Objects)

Second way is by building the security rules at the Business Objects. Here the security rules comparing the logged in user and security data can be written in a virtual table of your Business Objects. These virtual tables are nothing but the universe derived table. BO Reports use the derived table to access the datamart tables. Alternatively, we can also define security filters in a BO universe. The filters are called as condition / filter  objects in the BO universe world. With this approach, you can take the maximum ADVANTAGE of the BO features however the disadvantage is that when you are going to a different BI tool like Cognos you need to rewrite the business security rules in your new tool.

In case of the projects dealing with the migration of Peoplesoft transactional reporting to Business Objects analytical reporting. We can potentially reuse / import some security tables  and security policies from Peoplesoft into our analytical datamart. These reusable components can save time in building the secured datamart and reporting environment.

COLUMN LEVEL SECURITY

Like ‘Row level security’, we can implement the column level security either at the datamart or Business Objects. In the financial industry, the business users do not want their revenue amounts, social security number , tax id number and other sensitive columns to be shown to unauthorized users.  Given this instance, we can mask the sensitive columns by a restricted tag in the place of sensitive columns. Non-sensitive columns like first name , last name , gender , age can be left and shown as it is to the end business user. These logic can be technically implemented in the business objects universe derived table or datamart views using a decode / ‘if then else’ / case statements.

Alternatively , we can use the universe object restriction feature in the BO designer to define restriction on the universe objects. So whenever a business user tries to drag the restricted object from the universe , the restriction rules get invoked , authorization occurs and the object access is given to the end user if he / she is successfully authenticated to access that object.

I’m signing off this BO security blog for now. The contents are based on my knowledge and BO experience in various projects.  Thanks for reading.  Please share your thoughts on this blog. Also, please let me know your project experiences pertaining to row and column level security in Business Objects.

Let me start with the defintion of Predictive Analytics as used in literature - “The nontrivial extraction of implicit, previously unknown and potentially useful information from data”. If that doesn’t sound esoteric enough, you are probably more advanced than what this post gives you credit for!

For a BI practitioner, it is important to get an understanding of Predictive Analytics (also known as Data Mining) as this subject definitely deserves a place in the wide spectrum of Business Intelligence disciplines. BI at a broad level is about optimizing business through “Hindsight, Insight and Foresight”. Predictive analytics adds the powerful “Foresight” part to business decision making.

Most BI practitioners tend to equate statistics with predictive analytics and this post explains why such a view is inaccurate. To understand this let’s start at the very beginning (a la Alice in Wonderland). Broadly, this world is divided into 2 types of systems:

• Physical Systems - Has causality and hence can be modeled mathematically with relative ease 
• Human Behavioral Systems - Lacks causality and can be modeled only with specialized techniques

Predictive analytics for business decision making is all about modeling human behavioral systems.

Why Traditional Statistics is insufficient?

Though the entry into predictive analytics requires that we understand the implications of traditional statistical analysis, statistics by itself is insufficient in the business context. Traditional statistical analysis allows us to understand the general group behavior and is primarily concerned with common behavior within the group – the central tendencies.

In business we generally develop models to anticipate human behavior of some type. Human behavior is inconsistent, lacks causality and distributions based on human behavior almost always violate the assumptions of traditional statistical analysis (like normal distribution of data, stability of mean and standard deviation etc). The strength of data mining comes from the ability of the associated techniques to deal with the tails of the distributions, rather than the central tendencies, and from the techniques’ ability to deal with the realities of the data in a more precise manner.

In the realm of predictive analytics, we are concerned with modeling human behavior and hence are interested with the tail of our distribution – small percentage of the population that responds to a campaign, commits a fraud, leave our business or purchase the next service.

Though there are specialized techniques used for Predictive Analytics (viz. Non-linear statistics, Induction Algorithms, Cluster Analysis, Neural Networks to name a few), a BI practitioner is only expected to appreciate its usage in different business situations, prepare and model data as required by the tools and interpret the results correctly (a much less daunting task indeed!)

Typically the model development process involves the following steps – a) Define Project, b) Select Data, c) Prepare Data, d) Transform Variables, e) Process Model, f) Validate Model, g) Implement Model. I will explain these steps in more detail in subsequent posts.

Fundamentally, an end-to-end BI view requires the practitioner to learn the concepts around statistics and predictive analytical techniques as available in tools (like say SQL Server Analysis Services) in addition to their technology bag of tricks around data integration, data modeling and OLAP.

Thanks for reading. Please do share your thoughts.

Once the code has been moved to the target platform (Moving the Code), whether it’s an upgrade to a newer version or migration to another newer platform, the next step is to validate the objects moved.

Validation Process involves verification or testing of the objects in the target platform to ensure that they deliver the same output as the older objects in the source platform.

Validation is a key process by which the migration or upgrade process is certified as successful, it’s usually laborious and a time consuming process. Let us see how the Validation Process can be broken into different steps and automated for saving time and for improved accuracy. We can look at the Validation process to encompass three steps, they are

• Metadata Validation
• Run Validation
• Output Validation

Metadata Validation involves comparison of the metadata definitions between the existing source environment and the target environment. This requires that the metadata of the source and the target environment be captured for the comparison.

Steps Involved:

• Capture the source metadata into a relational structure, as part of Object Consolidation we would have captured the source metadata
• Capture the target platform metadata in a similar way into a relational structure
• Run SQL queries to automate the metadata comparison process

Metadata Comparison would be done at the level of semantic layer definitions and individual reports. Let us take the case of metadata comparison between two semantic layers, in case of Business Objects; Universe is the semantic layer definition. After an upgrade from an older version of Business Objects to its newer version, the first level of metadata validation between the universes would be to check whether the object counts between the universes match like the classes, the objects, the filters and then further comparison on their definitions.

If there are any differences when comparing the definitions and if they fall within the known differences between the two versions (source & target) then they are good else would require code fixing in the upgraded object.

Since we always try to validate the reports by what it gives as output, the validation process is limited by the data fed in; we could miss scenarios of a filter clause not being tested. Metadata Validation can overcome the limitation in data preparation for different scenarios for testing. If a report passes through a Metadata Validation expectation then we could 100% say that the report has upgraded or migrated effectively.

Benefits:

• Sets up a strong base on the metadata understanding, as the objects between different platforms has to be mapped and the bridges gaps identified to run automated metadata validation
• Improved accuracy in the validation process, overcomes the limitation in data preparation
• Enables determining issues without running the report against the data

Run Validation is to perform a dry run of the reports in an automated way to determine whether the reports run (open) successfully or not.

When we give a report to a tester, the first activity he would perform is to run the report and if it doesn’t go through the problem is reported or analysed further. We try to foresee this problem in an automated way.

Steps Involved:

• Have scripts to invoke the reports in batch mode, as soon as the objects are upgraded invoke(open) all the upgraded reports in the batch mode
• Capture the errors while opening/running the report into a log
• Classify them into two categories ‘reports that ran’ and ‘reports that failed’

Some reports could fail to open because of incorrect connection details, some due to object not found etc. This process of quick run in an automated way enables to locate the failure reports immediately and also help determine the reason for the failures in one go. Limiting the data input should be considered while invoking the report.

Benefits:

• Saves time in determining errors due to report opening or running
• Enables building a common solution for the code fixing team, as the ‘run errors’ are consolidated

Output Validation, is to validate the output delivered by the reports. There are two levels of output validation; they are Format Validation and Data Validation.

Format Validation is to check on the format of the data presented like font size, colour, bold, label location etc which doesn’t relate to the data value.

Data Validation is to check cell by cell the data value content between the two reports.

Steps:

• Run the source report and export the output data to excel/word
• Run the target report and export the output data to excel/word
• Compare the outputs for the format and the data

The best means of comparing the output of two reports is to export them to Excel and then performing a comparison between the two Excel’s. If we can export the reports to a word format then we can leverage the word compare utility, even an export to XML would enable using available utility. In case of excel we would need to build a utility that can compare the two excel sheets.

The above three validations are some of the key aspects in validating the objects of semantics and reports; let me know your thoughts on the other means of validation …