Subscribe to RSSArchive for the ‘Dashboard Vendor Blogs’ Category:
How to build Excel Dashboards – 7
Previous – Excel Dashboard Tutorial – 6
Create Bar Chart
Bar charts reveal important trend in your data. The very important reason we split the date into year, quarter, month, week and day components is to see if there is a trend in the sales or order information.
Right click on the Qlet title (”Name” ), and then click on the “Create Chart”
It opens a “Chart Factory Wizard”
1. The top drop down contains a list of all the chart types that are supported.
2. The X-Axis drop down has all the available fields from the Qlet
3. The Y- Axis drop down has all the available fields from the Qlet
4. The Y- Aggregation has (SUM, COUNT, MAX, MIN,AVG) values
The process is to pick the base column that will form the X-axis, the Y-axis is the numerical value column that will be used to plot the bar or appropriate chart type. The y-aggregation is required, usually we should pick “SUM” but you could use “AVG” or other aggregations as you see fit.
In our case, we want to see a “year over year trend for our sales growth”
1. Select “Bar” as the chart type
2. Select “Or Year’ as the X- Axis
3. Select “Amount” as the Y- axis
4. Select ‘Sum’ as the aggregation (by selecting sum, we are telling it to sum the amount and group it by year value)
5. Click on the “Create Chart” button
6. It shows the Glet Editor for your review which is a pre-built SQL query.
We change the Glet Name, and click on “full apply” button
It creates a bar chart as shown above, you can drag the borders and expand the view, it will scale automatically.
We just dragged the corners and expanded the view. Do not close the chart wizard yet, we will create other views like a rubber stamp.
By Quarter
Go back to the chart factory and only change the X-Axis column to ‘quarter’ field
Click ‘Create Chart’
Change the name to “By Quarter” and click “Full Apply”
Do not worry about the size and the placement. Just drag by the title “By quarter” and place it in a position where you can see it. At the end we can re-arrange the portlets on the dashboard canvas.
The column “Or_Qtr_Disp” has Qtr, 2 digit year and the last digit indicates the quarter number. The reason we chose this value is so that we can order the quarter across multiple years.
As you see, we can clearly see a trend emerging from our data. The Qtr-98-1 has the tallest bar. Now lets create a month view so we can nail down, which month contributed to that spike
By Month
We go back to the chart factory and select “Or_Mth_Yr_NN” which contains Year, month number and month name(3 letter).
So far the tallest periods are March and April of 1998. Why is that? Did you do special promotion, did you get extra word of mouth or publicity?
Similarly you can create slices by Week, or each Day. Note that as you go more granular, the chart may look more congested.
Now let us see which products are doing better.
Click on apply
Change the name to “By Product” and in the SQL query below, type, “Order by 2 Desc” which will sort the products by the revenue in the descending order
You have now found your winning product line.
But lets see how each of the products fair well within their own category.
Next – Excel Dashboard Tutorial – 8
How to build Excel Dashboards – 6
Previous – Excel Dashboards – 5
Create Year and Category Parameters
Remember, we have not built any charts yet. It is very important that we build our foundation Qlet and its corresponding parameters so that all of the drop down parameters can be leveraged in the charts.
It definitely helps to plan your dashboard development. Since the dashboard tool is easy to work with, we should still make an attempt to always plan so that we don’t duplicate our effort.
During the development, it may happen that you will get lots of ideas on implementing different ways. I would suggest to save the base dashboard definition once you have frozen all the parameters and Qlets. Before you start building charts, create a copy of the dashboard file as backup.
In our data, we have details about items that are sold on a particular date. We have the unit price, and quantity information. We also have the category for each item that they belong to. So it makes sense to include a parameter for the item category so that we can select only the items belonging to a particular category.
Right click on the “Category Name”, navigate to Create ==> Create Parameter
It creates a new parameter named “Category Name”
You may place this anywhere you feel appropriate.
Similarly, right click on the “Or Year” column and create the parameter
NOTE: Often it does not make sense to have a year drop down and a date range parameter.
Because if the year value and the date ranges fall apart or do not intersect then you will not see any data output. In this tutorial we provided the year and date ranges only for demonstration so if you need only one, do not use the other.
Next – Create Excel Dashboard – 7
How to build Excel Dashboards – 5
Previous Article – Create Excel Dashboards – 4
Add Date Hierarchy Columns
Most of the data, has some form of date information. Now date is very granular information. Very often, to generate trending information, you need to derive year and month columns.
In the dashboard designer, you can easily generate Date hierarchy columns for any kind of reporting.
In our example, we have order date, so we will generate the Year, Quarter, Month, Day , Week and other flavors of the same information for better representation.
Right click on the “Order Date” column and navigate to Create ‘ “Add Year, Quarter, Months column
Click on “Add” and it will add the following columns to the right of the primary column “Order Date”
You may not need all of the date derived columns but it is worth understanding what they stand for.
The derive columns, have a prefix of “Or” which are the first two letters of the primary date column “Order Date”. The remaining part explains the actual part of the date.
1. Year : represents the year component of the date
2. Mth N : represents the numerical month.
3. Mth Disp: represents the month number but padded with zero on the left
4. Mth NN: represents month number and 3 letter month name
5. Yr Mth : represents the year and month number
6. Qtr: represents the quarter number
7. Week : represents the week of year
8. Dy: represents day of year
9. … and so on.
The concept of date hierarchy even though simple to implement is very powerful. Within few minutes you can derive important “Insight” into your data. We will now begin the process of deriving intelligence out of boring data. Now begins the fun part.
Create Date Filter and Parameters
If you work with real transaction application, you already know that the amount of data is huge and the data has some form of date column. This date could be transaction date, invoice date, order date, payment date etc.
One of the dashboard and reporting best practices is to have some form of date filter and/or parameters so users can dynamically choose the date range. This helps in narrowing down the data for analysis.
Let us create a simple date filter.
One of the typical requirement is to see data for “as of today”.
In the dashboard designer it is few click process to implement this.
Right click on the Date column (order date), Navigate to
Create ==> Create Date Filter
The detail use of the Date Filter is covered in a separate tutorial. As we have very minimal use of the date filter for the dashboard that we are building we will just touch the basics.
In the date filter, on the left you see the column name and on the drop down you see different options such as below
” First Day of Month
” Last Day of Month
” First Day of Qtr
” Last Day of Qtr
” First Day of Week
” Last Day of Week
” First Day of Year
” Last day of Year
For our purpose we just limited our data to be less than today.
Click on “use this filter” and it will be applied to the Qlet.
One of the best practice is to avoid hard coding of any values in the Query. For e.g, The dashboard designer allows you to build drop down list of values as parameters to the query. We will leverage this option to the fullest so that the dashboard is extremely useful and can respond to data changes.
We will create a From and To Date range parameter
Right click on the “Order Date” column, Navigate to Create ‘ Create Parameter
It instantly creates two Data Parameters
We arrange them and size them appropriately as below
Once arranged and formatted, you can type the date or popup the date picker by click on the button on the side
Now, right click on the Title and select “Refresh”
Change the From and To Date values and refresh, the Qlet will change the data accordingly.
Next – Create Excel Dashboards – 6
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
