Subscribe to RSSArchive for the ‘infocaptor’ Category:
The Source in Open Source BI – Source Code
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
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
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
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.
Export Access Dashboards to PDF
Are you using Excel Templates to build your Ms Access Dashboard?
If yes, then how are you distributing the dashboards to your team?
The chances are that you are probably sending the entire bulky Excel file as attachment to your team, right?
Wouldn’t it be convenient to export the Excel dashboard to PDF and then just email the PDF?
Here are the steps to build a true Access Based Dashboard that has PDF and HTML export ability.
And the best thing is, you can put it on the webserver and then just provide the Dashboard URL to your team so they can interact with the dashboard in real time.
Access to HTML
Are you looking to convert your Microsoft Access Tables into HTML or a web page document?
In this tutorial, we will walk you through the steps in exporting the Access tables to a browsable HTML document.
Launch Access Dashboard Designer and click on the connect button
In the Driver, pick “MS Access .mdf.. “
Click on the “Access/Excel File” and pick the access .mdb file
Click on “Connect”
Click on the “New Blank Dashboard”
Give any descriptive name to the Dashboard
Next, it will show the Query browser and list all the available MS Access tables
Double click on any Table that you wish to export to HTML
Next, click on the alias field to select all the columns (you may select individual columns)
Click on the “Create Table” button
Click on the “Tree Browser icon” in the toolbar
Right click on the node name of the table,
Select ‘Export to HTML’
You may select “Queried Rows” or “All Rows” to HTML
Queried Rows: Will export whatever you have already queried in the dashboard. This means if you have put a restriction in the Qlet to pull only 100 records then it will export only 100 rows to HTML
All Rows: This will re-query the database and export all rows in the table to HTML
Here is the HTML output
You can output multiple tables in the same web page or html document
Goto Menu Create >> Query Builder
Click on the Reset Button
Now double click and bring any other tables you may need. Keep repeating the process until you have added all the tables on the dashboard.
Once added, rename the Qlet name by editing its properties (Hint: Right click on the “Name” title and select Edit)
Right click on Each node in the tree browser and select to include in the report
Now right click on the “Access Dashboard” node to export all the TAbles to HTML
This is the HTML output with the table names as bookmarks
Changing Colors, Formatting Data
With Access dashboard Designer, you get full control over the formatting options on each individual tables
Right click on the table name and select “Edit”
http://www.infocaptor.com/user_help/qlet_properties.htm
Access to PDF
Are you looking to convert your Microsoft Access Tables into PDF document?
In this tutorial, we will walk you through the steps in exporting the Access tables to a browsable PDF document.
Launch Access Dashboard Designer and click on the connect button
In the Driver, pick “MS Access .mdf.. “
Click on the “Access/Excel File” and pick the access .mdb file
Click on “Connect”
Click on the “New Blank Dashboard”
Give any descriptive name to the Dashboard
Next, it will show the Query browser and list all the available MS Access tables
Double click on any Table that you wish to export to PDF
Next, click on the alias field to select all the columns (you may select individual columns)
Click on the “Create Table” button
Click on the “Tree Browser icon” in the toolbar
Right click on the node name of the table,
Select ‘Export to PDF’
You may select “Queried Rows” or “All Rows” to PDF
Queried Rows: Will export whatever you have already queried in the dashboard. This means if you have put a restriction in the Qlet to pull only 100 records then it will export only 100 rows to PDF
All Rows: This will re-query the database and export all rows in the table to PDF
Here is the PDF output
You can output multiple tables in the same pdf
Goto Menu Create >> Query Builder
Click on the Reset Button
Now double click and bring any other tables you may need. Keep repeating the process until you have added all the tables on the dashboard.
Once added, rename the Qlet name by editing its properties (Hint: Right click on the “Name” title and select Edit)
Right click on Each node in the tree browser and select to include in the report
Now right click on the “Access Dashboard” node to export all the TAbles to PDF
This is the PDF output with the table names as bookmarks
Changing Colors, Formatting Data
With Access dashboard Designer, you get full control over the formatting options on each individual tables
Right click on the table name and select “Edit”
http://www.infocaptor.com/user_help/qlet_properties.htm
MS Access Visual Query Builder
Once you launch Access Dashboard Designer, you can click on the connect button.
In this example we are using the northwind sample database
Click on connect
Once connected it will open the Option wizard
Since there is no dashboard open, the “Build New Query” is disabled.
Click on the “New Blank Dashboard”
Enter some name for the dashboard “Northwind Dashboard”
Click OK
This Action immediately launches the Query Builder interface
Expand the node on the left
This displays a list of available tables
Double click on any table and it brings the table in the Query Builder window
You can now select columns and join then
To create joins, just drag the mouse on a column in the orders table. Once you see the join link appear you may release the mouse button. Now go to the second table and click on the column to which it needs to be joined.
Click on the SQL tab and you can see the SQL query that is automatically built for you
Now click on the “Create Table” button
This action creates a Qlet and places it in the dashboard
You can now right click on any column and create a drop down parameter
The parameter now automatically refreshes the qlet when you change the value
Call Center Report
Call centre ratios related to business performance assists a company in assessing itself against other competitors. To place themselves side by side with another company offering the same service or product serves to motivate improvements. It also informs decision making on every level.
The BPO industry has grown exponentially these few years. More and more call centers are being established all over the world. This makes having an edge over competitors all the more important.
Possessing the right tools and information, such as a call center ratios report could provide, gives a company an advantage. BPO companies are quite competitive when it comes to the accounts they handle and the quantity of employees they hire. Efficiency and customer satisfactions are always good indicators for company and employee performance levels.
Call centre ratios can compare trends, profits, growth, and employee dynamics in order to create an expansive or extensive assessment of a business. Competitor analysis would push for improvements and development in order to maintain a lead against other companies.
The results analyzed from ratio reports assist companies in which direction to take when making business decisions. For example, if the ratio of the number of outbound calls in comparison to profit is disproportionate, it indicates weakness in the service and performance. This information would then prompt management to form a strategy in order to address the weakness and improve upon it.
Another example where call center ratios are significant to the BPO industry is when a trend is noticed. Let us say that the ratio of profit inbound calls is higher compared to outbound calls. This would then guide companies to a direction where they concentrate on building their inbound call service to take advantage of the trend.
Call center ratios are great business and management indicators. They have the ability to influence strategic planning and decision making in a BPO business. They also take measure of the entire industry by comparing one company to another.
Call Center Dashboard
A predictive dialer report consists of: agent names, hours logged in, talk time, contacts per hour, etc. This helps with scheduling issues, performance evaluation and more. Call center reporting software helps you understand how well your call center agents are doing and where changes need to be made. Some call center reporting software deals with activity history by agents, activity history by queues, agent performance trends, call volume by time of day, call logs, talk, wait, ready times, call outcome activity, service level activity, list progress activity, outbound dialing activity, hourly productivity, summary productivity, etc.
In any call center environment, reports are of great importance. Call center reporting consists of the data that is collected from daily inbound and outbound calls.
Typical call center reporting might include: call attempted, calls completed, calls dropped, messages left, busy signals, etc. A clear understanding of when calls are placed and to whom they are placed makes call center staffing and productivity issues and planning easier to understand and undertake.