Store Dimension as dashboard Parameter

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.