Adding Store dimension and enabling ALL values in dashboard

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

sales_fact_with_product_time_store_link.png

Click ‘Apply’

You should see the results as shown below.

sales_fact_results_product_time_store_link.png

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*/

sales_modified_query_joined_dimensions.png
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.
store_dimension_with_all_enabled.png
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.
sales_query_with_all_dimensions_enabled.png
Now you see, the fact portlet is ‘All Enabled’ and complete.
complete_sales_fact1.png

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

complete_sales_fact2_with_decimals_without_keys.png
complete_sales_fact3.png
So this completes our basic dashboard.

We will save the dashboard as a file.

save_sales_dashboard.png

Happy Dashboarding!

« « Adding Time dimension to Dashboard                        Summary of Dashboard Creation Steps » »

Comments are closed.