Right Time for BI when Implementing ERP

Despite the economy and the related IT budget cuts ERP implementations are still a prevalent part of the IT project landscape. These include both upgrades to a new version of the same ERP system as well as implementations of new ERP systems to replace legacy systems. One of the questions that should come up when a company is implementing an ERP system is 'When do we start the BI project that will provide reporting and analysis of this ERP data?'

On a regular basis my firm is engaged to implement a BI solution when the ERP system has been implemented or is nearing completion. The prevailing wisdom is that we can't build a BI solution without data so why would we begin to do anything until the ERP system is complete. The prevailing outcome is that we expose design decisions made when implementing the ERP system that limit the ability to deliver the reporting and analysis requirements desired by the business. Examples include lack of detail or granularity; business processes that don't ensure critical information is recorded; data entry methods that make reporting more complex; etc. Sometimes a company lives with these decisions and their limitations but sometimes they may go back and change their ERP system as a significant cost. We have had more than one customer restructure their GL accounts and post at a lower level of granularity. After all, after implementing a BI solution capable of accommodating very large amounts of data it became obvious that having access only to highly summarized data was artificially limiting business insight. Another customer changed the way their sales order entry people processed price adjustments - they were creating a second sales order with a negative price but with the same quantity resulting in non-additive sales orders. Not hard to handle with a bit of ETL coding but still not ideal for business analysis.

So when is the right time to begin the BI project. My personal feeling is that it should start as the ERP requirements are becoming firm but are not yet set. That is the time to gather reporting and analysis requirements (the most difficult but most important part of a BI project and a topic for another day) and track those back to the data in the ERP system. The high level design of the data model and ETL for the BI solution can then begin as the configuration of the ERP application progresses. Ideally about the time the ERP project is ready for the conference room pilot, complete with test data, the BI team is ready for an initial load test of the ERP data. And about the time that the ERP application is ready to be moved to production the BI solution is in it's final testing phases and ready for deployment shortly after the ERP system is put into production.

There is a legitimate concern about encountering rework of the BI solution should changes be made to the ERP system as an outcome of the conference room pilot. The risk should be less costly than the risk of reworking the typically more complicated and costly ERP system.

This approach has another advantage. The team that implements the BI solution will be engaged while the resources with first hand knowledge of the ERP design are still available. We have all encountered the situation where much of the knowledge of the inner workings of the ERP system walk out with the implementation team and the documentation lacks the needed detail or accuracy. Interaction between the BI team and the ERP should be encouraged. A good BI implementer will be able to make efficient use of the ERP team's time (they will be very busy with their implementation) by packaging up their questions and by documenting the answers so that they ask them once. And while we are talking about the implementation teams, BI involves a very different set of design principles and skill sets than ERP. Choose your BI implementer based upon their references and experience specifically with BI projects. Your ERP implementer may offer up a BI resource or two and you may be tempted to go with a trusted partner with whom you already have a relationship. A good BI implementer will be able to work very effectively with your ERP implementer and will provide you with the best of breed resources to ensure your success.

Using Smart View with OBIEE - Part 1

Oracle's big push on the EPM/BI space right now is the "Fusion" Edition. This is their best-in-class product and the amalgamation of what were once separate standalone products into a single collaborative offering. The fusion of these great tools really highlights the combination of performance management and business intelligence as a solid roadmap for analytics in the business place. So, now is the perfect time for those that may be more geared toward EPM to take a look at Oracle BI and vice-versa.

One of the products bridging the gap is SmartView. This is a product that was basically brought over from the Hyperion acquisition. However, even for a lot of Hyperion-centric users, they have still not ventured to study, learn, and understand the breadth and power of this tool. SmartView is much more powerful than its well-known predecesor, the Hyperion Excel Add-In. It not only connects to Essbase but can connect to Hyperion Planning, and now....drumroll please...Oracle BI. This post looks at using SmartView to connect to OBIEE. In a really simple example I'll show you how to connect to your Oracle BI Server, create a Smart Slice, and retrieve data. For those new to the integration between OBIEE and Hyperion integration, there is a great post at the Art of Business Intelligence on how to download the SmartView add-in from OBIEE Presentation Services.

Pre-Requisites

1. Know the server locations where the following services are running (Oracle BI Server, Provider Services), Shared Services, Essbase, Planning, nor OpenLDAP are required when just connecting to the Oracle BI Server.
   
2. This example uses the OBIEE SampleSales.rpd, this should be configured on your OBI Server
3. Microsoft Excel (2003 or 2007). This example's screenshots are from MS Excel 2003.
   
4. Installed SmartView.exe add-in on your workstation
   

Instructions

After the SmartView.exe add-in has been downloaded an installed, open MS Excel. From the file menu an option for "Hyperion" will be available. If this option is not available to you please review the smartview installation instructions or verify you MS Excel add-in/macro settings.

From the Hyperion menu select Data Source Manager...

The Smart View Data Source Manager will open to the right-side of the window. Click the Connect to Provider Services link.

Once connected, the following options appear. If this the first time using Smart View on your machine you will be prompted to enter the APS server path. Please obtain this from your administrator.

1. Verify the APS server

2. Ensure "All" is selected here to see all connections and server options

Ensure that Common Provider Connections is selected.

1. Click to highlight the Oracle BI Server folder

2. Click the "+" button to add a new Oracle BI Server connection

When prompted, complete the following fields.

Product = Oracle BI Server

Product Server Name = Your Oracle BI Server Name

Product Server URL = Configure with correct OBI server name

User Name = Administrator

Click OK

Once the configuration is accepted expand the Oracle BI Server folder, expand the server, and the subject areas that are available to the user will be exposed.

Part 2 of this series can be found here. When you have successfully completed connectivity to your Oracle BI Server using Smart View continue on to Part 2 to learn how to create a Smart Slice and retrieve data from the Sample Sales subject area. Then you'll really be on a great start to becoming a Smart View guru.

TDWI Dimensional Data Modeling Primer: From Requirements to Business Analytics

This third course is a step above an introductory course and discusses these topics without getting into the "nuts and bolts" detail. It was compromised of five modules, "Dimensional Modeling Concepts," "Requirements Gathering for Dimensional Modeling," "Logical Dimensional Modeling," "From Logical Model to Star Schema," and "Dimensional Data and Business Analytics."

In the "Concepts" module (Module 1) the course covers business metrics, conceptual/logical/physical levels of modeling, differences between relational (normalized) and dimensional modeling especially at the logical level, and dimensional modeling definitions.

In the "Requirements Gathering" module (Module 2) the course covers the business context for data modeling, business questions as requirements models, fact/qualifier analysis, and a summary. After discussing business questions the course had students fill out a list of business questions regarding tracking the instructor's performance and a goal of continuous improvement. This module finished off by having the students map out a business question into an existing matrix. Then the students were asked to take their list of questions and fill out a blank matrix.

In the "Logical Dimensional Modeling" module (Module 3) the course described how to take the fact/qualifier matrix and turn it into a logical model by finding the meters in the facts and the hierarchies in the qualifiers, then completing the dimensions by expanding them with additional attributes. The model is then refined by determining the granularity, examining the measures and updating to meet the granularity, and adding measures. Sounds easy no? The module finished off by having the students map out their matrix from the end of Module 2. This does not prove as easy as it sounds. It took several iterations for the entire class to get a dimensional model that was agreeable. We discussed several different ways to include the idea of the location's event having an effect, attendee attrition during the class, is the class even based or self based, etc.

In the "Star Schema" module (Module 4) the course described how to move from the logical to the physical levels, degenerate dimensions, defining keys, supporting calculated measures, conformed dimensions, different types of changing dimensions, and semi-additive and non-additive facts. The module finishes of by having the students take their logical model from Module 4 and create a physical model from it.

We ran out of time for Module 5, "Dimensional Data and Business Analytics," but it basically was to consist of an OLAP demonstration.

Take aways:
The concept of turning a business requirement into a physical model.
The use of a fact/qualifier matrix.
Differences between a conceptual, logical, and physical model.

TDWI Buisness Intelligence Fundamentals: From Data Warehousing to Business Impact

This second course is an introductory course and spoke of these subjects at a conceptual level with no "nitty-gritty" detail. It was comprised of five modules, "Introduction to Business Intelligence", "Business Application Fundamentals", "BI Architecture and Process", "BI Infrastructure", and "Summary and Conclusions".

The "Introduction" module (Module 1) is brief and describes some of the different technology and business solutions in BI (DSS, EIS, OLAP, Supply Chain Analysis, Customer Analysis, etc). It also discusses the OBI framework as it regards to the Organizational roles and responsibilities.

The "Business Application" module (Module 2) covers the topics of business requirements, value, impact, applications, and analytics. It describes how and in what different ways BI can make an impact on business from the importance of business drivers through dash boards and score cards.

The "BI Architecture and Process" module (Module 3) covers the topics of warehousing definitions, warehousing data stores, data warehousing architectures, data warehousing processes, and business intelligence processes. The definitions section covers the definitions of the keywords included in the definition of a data warehouse (integrated, subject-oriented, time-variant, non-volatile, and accessible). The data stores sections covers the types and roles of the differnet collections of data (source, stage, ODS, warehouse, data mart). The data architecture section covers independent data marts, conformed data marts, hub and spoke versus bus, transient versus persistent versus semi-persistent staging, ODS positioning, and ETL. The business processes sections covers data access and delivery.

The "BI Infrastructure" module (Module 4) is really the meat of the course and covers the topics of BI infrastructure and BI readiness briefly while discussing the BI processes, technology, and roles and responsibilities in depth. In the processes section, the course discusses program management, change management, quality management, data governance, depolyment methodologies, project management, data warehouse administration, and metadata management. In the technology section, the course does not list all the different names of technologies, but it does provide the knowledge that tools are available to handle warehouse capactiy planning and other administration/operations tasks, data integration, etc. The BI roles and responsibilities section covers a fairly exhaustive list of business roles and what portion of the BI program/project for which they are responsible.

The "Summary" module (Module 5) has a list of top ten mistakes by job, a best practices list, and a list of references and resources.

Take aways:
While this course covered much of what TDWI Data Warehousing Concepts and Principles: An Introduction to the Field of Data Warehousing covered in the first three modules, the fourth module really took that knowledge to the BI level from the data warehouse level. It also seemed to try to stress the business impact the BI program has.
Concepts like change management, data governance and metadata strategies.

TDWI Data Warehousing Concepts and Principles: An Introduction to the Field of DataWarehousing

This initial course is an introductory course and spoke of these subjects at a conceptual level with no "nitty-gritty" detail. It was comprised of four modules, "Data Warehousing Concepts", "Data Warehousing Architecture", "Data Warehouse Implementation", and "Data Warehouse Operation".

In the "Concepts" module (Module 1) we discussed the various definitions of a Data Warehouse from Inmon, Osterfelt, Barquin, Kimball, and the TDWI summary. We discussed the framework of "components" that make up BI and D, ETL and its various targets (Data Mart, Data Warehouse, Staging), and the role of those targets. We finished off the module by going through an overview of the data warehousing process, discussing the differences between BI and IT Projects and BI projects and BI Programs, and going through the deliverables of the program and projects. We took the charter and readiness assessment in detail to lead into Module 2.

In the "Architecture" module (Module 2) we continued our path down the process by discussing Business Architecture, Defined Data Architecture, Technology Architecture, Project Architecture, and Organizational Architecture. In the business architecture we discussed the the business context for the data warehouse (drivers, goals, strategies, tactics, and results), and business metrics and management (BPM, BAM, CRM, SCM). In the defined data architecture we discussed data analysis (scenarios-based, goal-based, process-based), business questions, data modeling concepts, warehousing targets analysis (i.e. fact-qualifier table), and metadata requirements. We also discussed technology, project, and organizational architechure.

In the "Implementation" module (Module 3) we discussed Implementation Planning, Warehouse Data Modeling, Implementing Data Warehouse Architecture, Data Warehouse Process Model, Deployed Technology, Implementation Components, and Delivery Results. Warehouse data modeling covered the differences between relational (normalized) and dimensional data in the logical model as well as defining the role of the logical model. Implementing data warehouse architecture introduced the differences between the Inman and Kimball architectures as well as the role of the data warehouse in each architecture. It also introduced the independent data mart, the staging area, and the ODS. The data warehouse process model covered the ETL process in a bit more depth and reviews metadata. The technology, implementation concepts, and delivery results were brief overviews of the subjects.

In the "Operation" module (Module 4) we briefly discussed Business Services, Managed Quality, and Managed Infrastructure. We discussed Data Warehouse Administration in some detail. The administration subject covered data refresh, managed platforms, managed environment, and customer service.

Takeaways:
Inman versus Kimball architecture (Hub and Spoke versus Bus)
John Sackman Model (Contextual, Conceptual, Logical, Structural, Physical, Implemented)
Data Warehousing Program/Project Process and Deliverables

Keys to BI Acceptance

This is not meant to be an inclusive list, but rather three of the keys that I found to be interesting from the TDWI courses I have attended so far.

A Good Data Steward - this person exists to makes sure the definition of a product, unit, dollar, and every other attribute of data is consistent across the enterprise. The definition of data is key to quality data at the other end of the project. It is up to the Data Steward to achieve this consensus without imposing it. When you consider projects that span across a legacy source and a source from a business merger or acquisition, this can be very difficult to accomplish. As I sat and ate lunch today, I spoke with a gentleman who was complaining about this very thing. Someone had not defined the data correctly, and produced reports with this inconsistency. "It was about 96% accurate," he said. Well, is 96% accurate good enough? Will it give the people who used that information the ability to make informed decisions? Maybe, but consider that it is not. Consider that nobody realizes its wrong until it is too late. You probably will not get kudos for providing a system that works as intended or better. You will surely hear about it if it doesn't work. It doesn't matter if you fix it either. The perception is there. It will become that "system that always produces wrong data."

Good Usage Data - Not as straight forward as a data steward, good usage data can provide the means to a well accepted BI Program. The natural progression of a good BI Program is to evolve into something different. After so many iterations of change, without good usage data, you may never know x number of reports, tables, or facts are no longer being used because the department that needed them is no longer in the company. Your job of fighting for batch time becomes easier if you can lessen the load by getting rid of those unused items. Again, it may only take one time for the BI system will gain the reputation of the "system that is always late" or "using too many resources". Usage data can also help gain the ability to effectively market the BI Program to its users. Imagine, "Any BI Program: over 1 billion queries served," or "Any BI Program: 15 minutes could save you 15% on the bottom line." Well, even though that is not a stretch, at the very least usage data can provide you with data to help budget IT resources including capital. "At the current rate of increase in network traffic we are going to have to...." This lets you avoid the bad system reputation.

*Warning* Unabashed schmooze alert *Warning*

Good Program/Project Manager(s) - These guys have it tough give 'em a break! :)
If a project or program does not meet its intent, is late to start, or can not be maintained then it does not matter how well it is documented, how accurate it is, or how nice it looks. Managing the timing and dependencies of projects or tasks, resource planning (availability, capability, roles), effective time estimation, budgeting, adhering to schedule (overcoming roadblocks, personnel issues, under estimation), risk assessment and management are some of the critical roles they play that directly influence BI Acceptance.

TDWI Fall Conference - 2009

At first I wondered why anyone would schedule a conference to start on a Sunday at 8:30 am, but as I started my travels I quickly discovered that this was a blessing in disguise. The flight on Southwest Airlines was not only dirt cheap (less than $200 roundtrip) and non-stop, the two things that make a flight a good flight in my opinion, but it was on time and had many, many open seats. These are the things that make a good flight a great flight. This is when I started to hope that the people planning the TDWI Conference might know what they are doing. This hope was realized when I pre-registered and found everything, like my name spelled correctly, classes in the correct order, and books waiting for me. Classes started, ended, broke for breaks, and re-started on time, and there was even a back-up instructor ready when there was an emergency with the planned instructor.

The content of the two courses I have attended so far, "TDWI Data Warehousing Concepts and Principles: An Introduction to the Field of Data Wrehousing" (Day 1) and "TDWI Business Intelligence Fundamentals: From Datawarehousing to Business Impact," (Day 2) was packed full into the time alloted for the courses, 6.5 hours each. The information delivered was relevant to the subject area, and the only criticism I have is that the information from one course to the other overlapped quite heavily. And I'll admit as the class in Day 2 started, I asked myself, "Did I need to attend Day 1?"

While they two courses were very similar, Day 1 covered the process of developing a data warehouse/BI program (architecture, implementation, operation, etc) and Day 2 covered the infrastructure of BI (program management, change management, data governance, etc). And how do you cover these without at least touching some of the same subjects such as, business requirements, impact, data warehousing architecture? Day 2 also, expanded on Day 1 in some areas, for example while it was said in Day 1 BI should provide a measureable value (ROI), Day 2 provided different examples of what that value could be and that it is not always easily measured (e.g. more reliable information). Operational Data Stores were introduced to me in both Days, and while I understand the difference and use of them, I admit that there is still something puzzling about them or at least with the architectural structures they show them in.

All-in-all, TDWI did a great job of making sure things ran smoothly. I have only two gripes about the conference. First, the internet access was not free outside of the lobby area, and the lobby area was only free for the first three hours per day ($14.95 per day after that). Now, internet was not free in the hotel I booked either, but the free lobby did not havea time restriction on it. Second, parking was not free. Come on, for the price we have to pay for these courses, I think they could convince the hotel to let us park for free. That being said, this training has been very valuable to me. I have learned more about data warehouses, data architecture, the impact of BI on business, and dare I say, I am looking forward to my next course "TDWI Dimensional Data Modeling Primer: From Requirements to Business Analytics."