Dashboards - Business Intelligence

Successful Business Intelligence Software Implementation

Jun 1, 2009 BI News, Business Intelligence, Small Business Leave a comment

While the need for business intelligence is widely recognized, the use and implementation thereof can often be confusing, or misunderstood.

Most business intelligence initiatives start with the purpose of delivering the right information at the right time. This gives the correct priority to timeliness and accuracy of information, but does not indicate where said information should go. Though a real time BI software solution may deliver up to date information via dashboards or traditional reports, it may not be enough for employees who barely have time to log into their laptops when off visiting major customers. For such people, this information is not “real time” in the truest sense unless it is also readily accessible at the right place.

Another myth about business intelligence is that it provides analytical reporting while core transactional applications supply operational information. In actuality, a standard business intelligence software solution must meet both informational needs equally. Because information from both of these areas satisfies a diverse set of requirements, the most important ingredient for successful business intelligence implementation is getting the various parts of the organization properly aligned. It is critical that business analysts, in the early stages of implementation, interview all key stakeholders and document and reconcile critical business requirements from operational experts, analytical experts, line management, and staff management at multiple levels of the organization. Failure to understand internal customer requirements is one of the top reasons for failed business intelligence implementations.

Once requirements have been elicited and rationalized with each other, the proposed solution must be vetted with all stakeholders to ensure correctness and, even more importantly, drive buy-in so that all stakeholders will support and work towards a successful implementation rather than ignoring or, worse, actively resisting it. Once the initial requirements phase is complete, the next important step in an implementation is architecting a proposed solution and then piloting the solution on a small scale with key users to reduce any risks and prove out the implementation. During this phase, training plans can be created. A strategy for rolling out the implementation incrementally can be initiated and kinks in the implementation can be worked out.

Post-implementation, it is advisable to follow up with users to reinforce training and ensure that the full benefits of the business intelligence software solution are being realized. Any deferred features or capabilities can be addressed in the form of a small post-implementation revision project which takes an evolutionary approach towards continually improving the solution

Shopping for Open Source BI (Business Intelligence)

May 20, 2009 Business Intelligence Leave a comment

Open source BI is gaining popularity as more and more companies start using open source as opposed to commercial software for their businesses. Evaluating an open source product is different from evaluating a commercial product. Because there is no sales force for distribution, gaining comprehensive information on the business intelligence product requires a different approach. Assuming all other things are equal, open source BI shoppers should start by looking at the support forums associated with the community, the source code for the software, and the documentation and training options offered on the website.

The Support Forums

The support forums for any open source software project are one of the project’s greatest assets. By visiting the forums, a CIO can begin to assess whether or not that particular project with work for their company. Each community has its own personality and it is important to learn more about how welcoming the forums are, how capable the main contributors can be, and how active members are in the community.

The Source Code

The nature of the source code is also very important to look at. The source code holds the secrets to what the product is like and what its functionality is.

Documentation and Training

Most open source BI projects will have some kind of documentation that will help those who don’t read code understand how to use the product. Often there are also training options available, as well. If these documents are difficult to understand or if the training is not comprehensive enough, neither will be much use to any company using the open source software.

Once a company finds the open source BI program that they want to use, they have a variety of options on how to proceed with their new product. They can start by actively becoming a part of their community in order to garner good will, with the hope that they can eventually have some influence on the applications that are developed by the community. Although becoming involved is a good idea regardless of a company’s situation, some companies may need to customize the software with urgency. In these cases, the company can use one of their IT people to develop the applications internally. If the applications are developed internally, the company will then need to decide if they want to release the new code to the community or if they should keep it in house. The third option for companies new to open source BI is to hire an outside developer to customize their new open source BI software for them. This option will cut into the upside of avoiding commercial licensing fees, but can end up being a great decision in the end for the right company.

Shopping for open source BI can be a challenge for the CIO who has never dealt with an open source software project before, but it is a task worth taking on. Because the source code is public, the assessment a CIO makes can be more accurate than relying on hearsay or the advertising pitches that are part of shopping for commercial software. The result can be a great program that is constantly improving and offers support and community to its users without a large initial cost.

OBI Apps - Project Analytics

Apr 29, 2009 Business Intelligence, OBIEE Leave a comment

Oracle recently released OBI BI Apps 7.9.6 and the major change is the addition of Project Analytics.

Oracle Project Analytics offers organizations a comprehensive analytics solution that delivers pervasive insight into forecast, budgets, cost, revenue, billing, profitability, and other aspects of project management to help effectively track project life cycle status. It provides consolidated and timely information that is personalized, relevant, and actionable to improve performance and profitability. Oracle Project Analytics is also integrated with other applications in the Oracle BI Applications family to deliver cross functional analysis, such as AR and AP invoice aging analysis and procurement transactions by project.

Oracle Project Analytics provides role-based reporting and analysis for various roles involved in the project life cycle. Typical roles include Project Executive, Project Manager, Project Cost Engineer/Analyst, Billing Specialist, Project Accountant and Contract Administrator.

Executives can closely monitor the organization’s performance and the performance of the projects that the organization is responsible for by looking into a particular program and project and verifying how the period, accumulated, or estimated-at-completion cost is doing compared to budget and forecast. Cost variances and trends can be analyzed so that prompt actions can be taken to get projects on track or make any necessary changes in estimates, minimizing undesired results and reactive measures.

Oracle Project Analytics shows past, present, and future performance, and includes estimated metrics at project completion. Further analysis can be done on each project by drilling down to detailed information including profitability and cost at the task level.

Project managers can view the projects that they are responsible for, compare key metrics between projects, and analyze the details of a particular project such as cost distribution by task, resource, and person. Oracle Project Analytics provides a comprehensive, high-level view of accumulated and trending information for a single project or group of projects, as well as detailed information, such as budget accuracy and details by project and financial resource. Project managers can view cost and revenue by task, expenditure category, or resource type; and by project or resource. The level of analysis can be as granular as cost, revenue, or budget transaction.

Oracle Project Analytics provides out-of-the-box adapters for Oracle EBS 11.5.10 (Family Pack M) and R12, and PeopleSoft 8.9 and 9.0. It also provides universal adapters to extract and load data from legacy sources such as homegrown systems or from sources that have no prepackaged source-specific ETL adapters.

Oracle Project Analytics application comprises the following subject areas:

Funding. A detailed subject area that provides the ability to track Agreement Amount, Funding Amount, Baselined Amount, and all changes in funding throughout the life cycle of the project. In addition, it provides the ability to do comparative analysis of Agreement Amount, Funding Amount, Invoice Amount, and the remaining funding amount across projects, tasks, customers, organizations, and associated hierarchies.

Budgets. A detailed subject area that provides the ability to report on Cost Revenue, Margin for Budgets, and Budget changes including tracking original and current budgets across projects, tasks, organizations, resources, periods and associated hierarchies at budget line level.

Forecast. A detailed subject area that provides the ability to report on Cost, Revenue and Margin for Forecasts, and Forecast changes. Forecast change analysis includes tracking original and current forecasts across projects, tasks, organizations, resources, periods and associated hierarchies. It provides the ability to track the metrics that indicate the past, present and future performance of cost, revenue, and margin.

Cost. A detailed subject area that provides the ability to report on Cost (Burdened Cost), Raw Cost, Burden Cost for the past and current periods including inception-to-date, year-to-date comparisons across projects, tasks, organizations, resources, suppliers, and associated hierarchies. It provides the ability to track the cost at cost distribution level.

Revenue. A detailed subject area that provides the ability to report on Revenue transactions for the past, and current periods including inception-to-date, year-to-date comparisons across projects, tasks, organizations, resources, and associated hierarchies. It provides the ability to track the revenue at Revenue distribution level.

Billing. A detailed subject area that provides the ability to report on Billing Amount, Retention Amount, Unearned Amount, and Unbilled Receivables Amounts across the projects, tasks, organizations, resources, and associated hierarchies. It provides the ability to track the invoice amount at invoice (draft invoice) line level only. Note: Invoice tax amount is not captured in this release.

Performance. A consolidated subject area that includes combined information from Budgets, Forecasts, Cost, Revenue, and provides the ability to do performance by comparing the actual (cost, revenue, margin and margin percentage) with budgets, and forecasts across projects, tasks, organizations, resources, and associated hierarchies.

—–

The Source in Open Source BI – Source Code

Apr 22, 2009 BI News, Business Intelligence Leave a comment

With open source BI, users have access to the source code for the business intelligence software that they use. This gives them the ability to make changes and add applications in a way that they wouldn’t be able to with an out of the box platform. Commercial software does not give its users access to the original source code, nor does it give them any right to change or make modifications to the product. Users of commercial business intelligence must wait until the provider releases a new version of the platform or a new application to get added functionality for their system. With open source, users are able to make modifications to the code as needed as long as they adhere to the restrictions dictated by the license that governs the original code.

What is Open Source Code?

Open source code is software that has been written by a person or a company and copyrighted, but is also licensed to include a large user population, who is able to make changes and fix bugs in the code. As users need different applications from their open source software, they can change it to fit their demands without facing the repercussions of copyright infringement. There are a couple of different licenses that designate code as open source and users must adhere to the guidelines put forth by these licenses in order to use and modify the software.

Open Source Licenses

There is a variety of licenses that have been approved by the Open Source Initiative or OSI. Two common licenses are the Berkeley License and the GNU General Public License (GPL). The Berkeley License allows users to take the source code and make changes to it before releasing it again under a separate propriety license. With the Berkeley License, the original creators of the code would have to be acknowledged publicly when the new software is released. With GPL, if derivative software is created using the original code, it has to be made available as a GPL product, allowing other users access to the new code and allowing other users to modify it for their needs.

Freeware vs. Open Source

Open source is different from freeware, in that the actual code is made available to users, not just the software. Freeware is software that you can download for free for a trial period or permanently, but has to be used as is without any modifications. There is some very reliable freeware that can be downloaded permanently, but freeware is also used by commercial software providers to entice buyers to buy their product after the trial period is up.

Because they have ability to make changes to the code in open source, users can customize their business intelligence software to best serve their company. Users also have access, the majority of the time, to upgrades and applications that other users have developed, in addition to any bug fixes. This means that there is no waiting on the parent company to come out with an upgrade or develop add-ons.

OBIEE - BI Apps - Finance Analytics - Group Account Number Configuration

Apr 7, 2009 OBIEE Leave a comment

If you are configuring Financial Analytics (OBIEE BI Apps), it is critical that the GL account numbers are mapped to the group account numbers (or domain values) because the metrics in the GL reporting layer use these values. For a list of domain values for GL account numbers, see Oracle Business Analytics Warehouse Data Model Reference (available from metalink only)

You can categorize your Oracle General Ledger accounts into specific group account numbers. The group account number is used during data extraction as well as front-end reporting. The GROUP_ACCT_NUM field in the GL Account dimension table W_GL_ACCOUNT_D denotes the nature the nature of the General Ledger accounts (for example, cash account, payroll account). Refer to the GROUP_ACCOUNT_NUM column in the file_group_acct_names.csv file for values you can use. For a list of the Group Account Number domain values, see Oracle Business Analytics Warehouse Data Model Reference. The mappings to General Ledger Accounts Numbers are important for both Profitability analysis and General Ledger analysis (for example, Balance Sheets).

The logic for assigning the accounts is located in the file_group_acct_codes_ora.csv file. The table below shows an example configuration of the file_group_acct_codes_ora.csv file.

Basically we specified the Financial Statement Item configuration through a CSV (comma separated value) file.
This is an example of the Financial Statement Item configuration file.
The Financial statement item configuration is part of the configuration steps required for the Finance module. It needs to be constructed by the user before the ETL program starts running.

In this CSV file, the user specify the GL accounts, and the nature (which we call Financial Statement Item) of the GL accounts.

The nature is indicated by the values in Financial Statement Item column.

If consecutive GL accounts have the same nature, you can specify them in ranges as shown above.
There are 6 possible domain values for the Financial Statement Item: they are AP, AR, Revenue, TAX, COGS, and Others.
The 6 possible values corresponds to our 6 base fact tables: IA_AP_XACTS, IA_AR_XACTS, IA_GL_REVENUE, IA_TAX_XACTS, IA_GL_COGS, and IA_GL_OTHERS.

The set of books is an accounting entity. It may be an Oracle specific term. A company can use one single set of books or multiple set of books to keep track of its accounting.
When defining a set of books in Oracle, the user specified the chart of account to be used to organize its GL accounts, and a common currency to keep all the transaction amount in.
For instance, Siebel US may use a set of book called ‘US Set of Books’ to keep track of its accounting entries, Siebel Europe may use a different set of book to keep track of its accounting entries.

The set of books ID is basically the numeric ID of that set of books in the OLTP system.
In the above example, accounts 1000 to 1100 for set of books 100 are assigned to AP. Accounts 1200 to 1300 are assigned to AR.

A GL account can be assigned to only one Financial statement item.

We have another configuration file similar to the Financial Statement Item Configuration file. It is called the Group Account Number configuration file. It allows the user to configure the GL accounts at a more detail level than Financial Statement Item.

This is an example of the Group Account Number Configuration file.
This configuration is mainly used during the PLP process when we want to aggregate records from the Base fact tables to the Base Aggregate tables.

The base fact tables stores records at GL account level whereas the base aggregate tables stores summarized records at Group Account Number level.

The group account number is also used in the Siebel Analytics RPD to define metrics definition. For instance, I can have a metric called ‘Sales and Marketing Cost’. The underlying definition of that metric would be, the total amount of all transactions charging to any accounts with Group Account Number ‘SM COST’. In this case all transactions charging to accounts between 4000 to 4100.

Install OBIEE BI Apps on Linux and Windows

Apr 3, 2009 OBIEE 2 Comments

The documentation on installing OBIEE and BI Apps configuration from Oracle is pretty good overall but extremely ovewhelming… Just too many components and configuration steps. For a beginner it always helps to get a overview of what is involved and how to proceed.

Typically for demonstration purpose, you could get a 4 gb PC/LAPTOP with enough disk space (150 GB) and get the entire BI Apps installed and configured. You can install 10g Database, OBIEE, BI Apps, Informatica and then configure the DAC and run the ETL all on the same windows box.

For real implementation you would preferably use linux/unix/solaris boxes for your installation. In this situation where you have to install on Linux, there are components which can be installed only on Windows and then the files need to be FTP’d over to the linux box.

At minimum, you will need to find a windows PC to install the following

• JDK
• OBIEE
• BI Apps
• Informatica client components

If you are going to install the above on the windows, you might install the server components on the windows machine so that it provides you an extra play area for local testing and troubleshooting.

In this article, I will just cover the high level step to install on windows.

• Install Database
  • Download 10g database and install on windows
  • Create olapdb, infadb,dacdb users
  • Grant grant dba, connect, resource to olapdb,infadb,dacdb
• Download JDK 1.5* (there is some bug with JDK 1.6)
• Download OBIEE for windows
• Download obiee bi apps (available for only windows)
• Download informatica for Windows
• Once downloads are done
  · Install OBIEE on windows
  · Install OBIEE Apps on windows
• · Install Informatica Client and Server
• Download and install JDK on linux
• Download and install OBIEE on linux
• Download Informatica for Linux
  • Unzip disk1 and disk2
  • Run install.sh from disk1
  • Run install.sh from disk2 (sp5 patch)
• Copy the DAC, oracle_bi_dw_base.rep and the source and lookup directories from OBIEE/dwrep/informatica directory on window to server in the appropriate informatica directory on the linux server. On windows you can assemble all the needed files into a new folder, zip them up and ftp to linux.
• Start Informatica services
• Launch the informatica repository console (admin)
• Restore the repository oracle_bi_dw_base.rep
• Launch the DAC client
• Create new configuration for the Datawarehouse
• Create the datawarehouse tables
• Configure Informatica workflow manager
• Configure DAC server

This is just the Mt Everest view for the complete install and initial config. Once this is done you will need to launch the DAC Client, setup the containers, map the domain values, parameters for the required Analytics and finally run the ETL.

In a later post I will try to cover some more details on the DAC configuration but thats it for now.

I frequently provide remote assistance to lot of clients for installation and configuration of entire BI apps. If you need similar help just shoot me an email at njethwa@gmail.com

OBIEE and Daily Business Intelligence

Apr 1, 2009 Business Intelligence, DBI, OBIEE 2 Comments

At the current client, the customer is using Oracle CRM and the packaged OBIEE BI Apps has CRM content sourced from Siebel CRM. BI Apps can help us provide the content for Finance, Supply Chain, Order Management and HR (peoplesoft). For the CRM piece we have enabled Daily Business Intelligence dashboards for Customer Support, Depot Repair and Field Service.

The good thing is Oracle provides the repository for DBI content (which I think they have discontinued promoting it). Using this repository and the OBIEE BI Apps repository we can merge them two and provide a single point of entry for almost all of their Analytics requirements.

The Case for Open Source BI Software

Mar 23, 2009 BI News, Business Intelligence Leave a comment

For both software users and software creators, the advent of open source business intelligence has created much controversy. Since 1998, when the Open Source Initiative was formed, a steadily increasing wave of commercial and non-commercial licensed software has been developed and distributed over the internet. Currently, in the realm of open source BI software, over 60% of companies and governments are active users, with these numbers steadily rising. But even with such statistics, some potential users still prefer closed source software to its open source competition. On the other hand, some new software creators also prefer the world of closed source applications, with little interest in developing software for open source business intelligence.

For these closed source minded software users and creators, there are a few common misconceptions about open source software that should be examined more closely.

Concerns about Open Source Software

In the case of business intelligence, potential users may worry about the safety and security of software that is free for download over the internet. Would valuable and potentially damaging business intelligence information really be safely filtered through free software? Also, such potential users may also be worried about bugs within the open source software, or even a “back door” for the software creator to access the user’s information.

For software creators looking to tackle business intelligence, the open source software model may look like a worthless investment of time and expertise. Why would anybody make business intelligence software available as a completely free download? How would a software creator make any money if the source of his or her labor is freely distributed to anybody who owns a computer?

The Truth about Open Source Software

For those users worried about the safety capabilities of open source business intelligence software, they can rest easy. Since its inception, the creation of open source software has been targeted towards benefitting users, not scamming them. Also, there can be no such thing as a “back door” in open source software, because all of the software’s coding is fully available for everybody to see. If large companies and corporations are using open source business intelligence software, you can be sure that it is a safe and reliable alternative to any of the pricy closed source competition. In addition, open source business software allows users to come together over forums and discussions, where everybody can work together and troubleshoot to update the software into an even better free business intelligence tool.

Hesitant software creators should also realize that they can make big profits from creating something that everybody downloads for free. Unlike closed source companies, open source companies make money by shifting the commercial value away from their business intelligence software “product,” and instead, generate money from something called the “Product Halo.” This means software creators offer the open source software for free, but charge money for such valuable services such as tutorials, technical support, and system integration. Basically, if the open source business intelligence software creator is the most knowledgeable about his or her product, then users and companies will pay them for help in maintaining and updating the free software.

Though closed source software is here to stay, new software users and creators are wise to look into the benefits of the increasingly popular open source business intelligence software model.

The Benefits of Self Service Business Intelligence

Mar 6, 2009 Business Intelligence, Small Business Leave a comment

To understand the benefits of self-service business intelligence, you first need to know what self-service BI is. It is, as it sounds, a way for business users to easily access the data that they need and create the necessary reports by themselves without having to involve the IT personnel or power users at their company. In essence, it is user-friendly business intelligence that any business user can operate.

Who Can Use Self-Service BI?

The whole point of self-service business intelligence is that anyone who needs to use it can use it. While BI tools are normally used by management or the key decision-makers in a company, it is now also being used by project managers to improve day-to-day performance and by real-time users in an operational capacity. Self-service BI doesn’t cut out the need for your IT personnel: they are still very necessary in creating applications and dealing with the more complicated business intelligence issues. But self-service BI does offer the benefit of streamlining the reporting process and allowing business users to handle much of it themselves.

Taking Advantage of the Benefits

Because self-service BI users don’t have to rely on your IT team to gain access to data and create reports, the decision-making process in your company can become that much faster. When a business user sees a problem that requires data to analyze the problem and strategize for a solution, that business user no longer has to first approach the IT department to access the data and build the necessary report. Removing that step in the process brings your business user to a solution much faster and improves the performance of your company on a much more immediate basis. Another benefit of self-service business intelligence: it frees up your IT personnel to work once again on the larger picture, rather than getting bogged down in the reporting needs of business users.

The Tools You Need for Self-Service Business Intelligence

How can you get your hands on self-service BI tools? Find a vendor that utilizes rich internet applications in its BI software. This allows for a user-friendly interface, web-based tools and reports, and reporting formats that will look and feel familiar to the user.

Business intelligence can be complex, but more and more, vendors are creating tools that are more user-friendly and allow business users to work directly with those tools. Take a look at self-service BI and streamline the decision-making process in your company.