Dashboards, Reporting & Business Intelligence

All of the below steps should not take more than 15-20 minutes (all-together)

• Build a Dashboard from scratch
• Connect to Database
• New Dashboard
• Add parameter to dashboard
• Wire the dashboard with Parameter
• Parameter Descriptions
• Add Time dimension to dashboard
• Enable ‘ALL Values’ in parameter

In the previous section we linked the Time dimension to the dashboard portlet.

We will wrap this tutorial with the addition of the store dimension. We will also make some query changes to enable the ‘ALL’ parameter value selection.

Create the Store dimension using the following query as the base

Select s.store_name
,         s.store_key
from video5.store s

Now link the Store parameter to the Sales Fact as shown below

Click ‘Apply’

You should see the results as shown below.

We will modify the query for the ‘Sales Fact’ to include the dimension joins in the query.

select
p.description   Product_Description
,   to_char(t.transaction_date,'Month') time_month
,    to_char(t.transaction_date,'YYYY')   time_year
, s.store_name        store_name
, f.sales
, f.unit_sales
, f.cost
, f.customer_count
, f.profit
from video5.sales_fact f
,       video5.product p
,       video5.store s
,       video5.times t
where f.product_key = p.product_key
and p.product_key like   'G_PARAM
'    /*p.product_keyproduct_key*/
and f.time_key=t.time_key
and t.time_key        like    'G_PARAM
'    /*t.time_key*/
and f.store_key=s.store_key
and s.store_key      like        'G_PARAM
'    /*s.store_key*/

Having the dimension tables in the query gives us more flexibility in terms of what information we can display with the portlet. You should notice that the parameter links are now pointing to the dimension table and the dimension keys are joined to the fact keys. In a star schema, it is always recommended to query through the dimensions as they have low cardinality and it makes better use of indexing (bitmap indexes?)
Click on ‘Apply’
Edit the ‘Store’ Parameter and select ‘Y’ for the ‘Enable ALL Value’ as shown below.

Using the above e.g. edit the Product and Time dimension and select ‘Y’ for the ‘Enable All value’
Once the parameters are enabled to show ‘ALL Values’, we will make a small change in the Fact query.
Technical Details: When you enable ‘All Values’ for any parameter, it adds a dummy value in the parameter list with a display value of ‘ALL Values’ and internally it sends a reference value of ‘%’. So in our Fact query we would change the ‘=’ operator with the ‘like’ operator.

Now you see, the fact portlet is ‘All Enabled’ and complete.

To see the actual query that is being submitted to the database and executed follow these steps

Click on ‘Run’ menu

Select the ‘Display Log’

Click on ‘Log Enabled’ and close the window

Now refresh any parameter or the Fact query.

Go back to the ‘Display Log’ or type ‘Ctrl+L’

you should see a query similar to as shown below

select
p.description   Product_Description
,   to_char(t.transaction_date,'Month') time_month
,    to_char(t.transaction_date,'YYYY')   time_year
, s.store_name        store_name
, f.sales
, f.unit_sales
, f.cost
, f.customer_count
, f.profit
from video5.sales_fact f
,       video5.product p
,       video5.store s
,       video5.times t
where f.product_key = p.product_key
and p.product_key like   '4'    /*p.product_keyproduct_key*/
and f.time_key=t.time_key
and t.time_key        like    '%'    /*t.time_key*/
and f.store_key=s.store_key
and s.store_key      like        '1'    /*s.store_key*/

We will modify the Fact query to display decimal values as shown below

So this completes our basic dashboard.

We will save the dashboard as a file.

Happy Dashboarding!

In the previous section we demonstrated on how to add the parameter description.

We will continue adding the Time dimension.

Here is a basic time dimension query (In the figure below, it is displayed as a Qlet and not as parameter)

We will extend the basic Time dimension to include the month and the year columns

Click on the new parameter button (follow the detailed steps of adding the Product dimension)

Click on ‘Apply’

You should see the Time dimension added to the dasboard

We will now link the Sales Fact to the Time dimension (wiring with Time dimenion)

Edit the Sales Fact portlet

In the Query region, add a condition ‘and f.time_key = ‘

After the ‘=’ sign, right click and select the Time parameter key value from the sub-menu as shown below

Your final ‘Sales Fact’ query should look similar as shown below

Click on ‘Apply’ and you should see a more restricted list in the Sales Fact portlet.

Concluding the Dashboard creation process with the Store Dimension and enabling ‘All’ values

In the previouse section, we saw how we linked the product dimension to the sales fact.

We will tweak the Sales Fact query to include the product description as below.

Edit the Sales Fact portlet (qlet)

In the Query region, click “Enter” after the select keyword

Right click and pick the product description value from the sub-menu as shown below

Your final Query should look similar to below

As you noticed, we removed the f.* and replaced with individual column names.

Click “Apply”

You should now see the product description in the Sales Fact as shown below

Continued… Now we will add the Time Dimension as parameter

In the previous post, we saw how to add a fresh parameter to the dashboard. We will continue and create a link between the parameter and the portlet (Qlet)

Click on the “Sales Fact” qlet and select “Edit”

In the query text region, add the condition

“and f.product_key=”

After the “=” sign, right click on the mouse and you will see a list of Dashboard parameters.

The parameter p31 is the parameter_id referenced within the dashboard, In the sub-menu you will see the available columns from the parameters. In this case you should see the product description and product_key.

Select the product_key from the sub-menu. Your final query should look like below

Click on “Apply”

You should see the results as shown below. As you see, the Sales Fact is showing results only for “Beautiful Girls” product selection (all the product_key =1 ).

Now change the product selection to ‘Pulp Fiction’

As you have noted above, the product keys in the Sales Fact does not serve any purpose.

Continued… We will now add some dimension descriptions in the Fact portlet

In the previous post, we saw how to create a simplistic dashboard portlet. Now we will spice it up by adding parameters and linking the parameter to the Portlet.

We will now add the product dimension as the dashboard parameter.

select p.description product_description
,      p.product_key product_key
from video5.product p

As shown below is the query and its corresponding results.

Now we click on the new parameter button

It will bring up the parameter editor dialog as shown below.

A parameter has two parts to it, one is the display column and the other is the internal reference column.

For e.g If you had a parameter showing Employee name, then the display column would be Employee name but the internal reference column would be the Employee_id or number.

Similarly for the Product dimension, we will display the description as the display column and the reference column would be the product key. You can have a parameter without any reference columns.

All the fields marked in yellow are required, you may opt to have the fields in green populated depending on the requirement.

And finally we need to provide the details for the from clause

Click on “Full Apply”

You should see the parameter on the dashboard as below

Now we have successfully added the parameter but there is no linkage between the Sales Fact Qlet and the product parameter.

The behaviour we are looking for is such that when we select any parameter value from the drop down, the sales fact should reflect only the selected parameter values.

Continued… Now we will create the link between the parameter and the portlet also known as “Wiring the Dashboard“

In the previous post, we saw how to connect to a database.

We will continue with the creation of first cut of our dashboard

Click on the New Dashboard button or from the menu bar, select Create -> Dashboard

Enter an appropriate name for the dashboard => Sales Analysis

Click OK

The result should be a blank dashboard as shown below.

Now we will add our first Qlet (short for Portlet based on SQL Query)

A Qlet dialog will open up as shown below.

Provide the name of the Qlet : Sales Fact

Select the Connection from the list

and enter the Sales Fact query in the text area shown above

Select *
from video5.sales_fact

NOTE: Optionally you may choose to add individual columns in the select query

Click on the Apply button

You should see the following results

continued…. Add parameter to Dashboard

In the previous post we saw an overview of what we are going to develop within the dashboard.

The next steps..

Launch InfoCaptor and create a database connection

Once you hit the connect button, it opens a connection dialog as shown below
In this example we are using Oracle as the database but you can accomplish the same using MS Access, MySQL, DB2 or any database of your choice (detailed instructions for each of the database will be posted later)

For connecting to Oracle, you should select the Oracle driver and enter the Host, Port and DB name as shown below. Enter the username/password and connect. Once connected, the connection dialog closes, you may click on connect button again to verify if there is an entry in the live connection region as shown below

Continued.. Create New Blank Dashboard

In this article, I will demonstrate how to create a dashboard on a blank slate using InfoCaptor: Rapid Dashboard Designer

Pre-requisites :

You should have already installed Dashboard Designer

We are going to use the Video5 sample data that comes along with Discoverer installation.

Here are the list of tables (Dimensions and Fact) that are going to be used in the exercise.

Product Dimension

Store Dimension

Time Dimension

Sales Fact

This Sales Fact table contains columns that are mapped to the Dimension table using their keys e.g Product_key, Store_key and Time_key.

The sales fact table contains all the transaction that take place at a very detail level. In order to get information out of this detailed fact table, there are a few summary tables which maintain a high level aggregation of the detailed transactions.

We will keep the summary tables out for now. Let us just go with the bare basics of a simple data-warehouse star schema (one fact surrounded by three dimensions)

In our dashboard there will be 3 parameters and one portlet showing the Fact information to begin with. The dimension tables will map to the parameters of the dashboard.

Here is a rought sketch of how our initial cut of the dashboard will look

So lets begin..

Launch Dashboard Designer and continue …