Sales status using Inline SQL

This section takes advantage of the various features of the Inline SQL view to develop reports and charts using sales data.

Create workspace for tutorial

Create a new workspace tutorial in the Navigator.

After logging in, go to the navigation tab.

1

After logging in, go to the navigation tab.

2

Click on the top workspace.

3

Click the New Item button and select a workspace from the pop-up button.

 

Enter the workspace name Tutorial and description and click the OK button.

 

Click the Refresh button on the folder title bar.

 

Code Mapping

Create code mapping table for code values M and F for Male and Female.

Click System Administrator menu and click Analysis - System Code Management.

1

Click the System Administration menu.

2

Click the Analyze - System Code Management entry in the menu.

 

Create a code mapping entry.

1

Click on the Code Mapping tree item and click the New Item button on the toolbar.

2

In the New Item drop-down list, click the folder.

 

Create a folder with the name General.

 

Click the General folder and then click the New Item drop-down to select the code mapping entry.

 

Enter the item name Gender and click the OK button.

 

Click Gender in the grid list to edit it.

 

Click on the generated code mapping entry Gender to edit it.

1

Select a static value for the mapping type attribute.

2

Click the Add Row button to add a code / value entry.

3

Enter the value by double clicking on the line.
M Male
F Female

 

Click the OK button to save.

 

Set SQL Query

The SQL query to use as an example is shown below.

SELECT
  fm_store.store_country,
  fm_store.store_city,
  fm_customer.gender,
  fm_sales_fact.sales_year,
  fm_sales_fact.product_id,
  fm_customer.fname, fm_customer.lname,
  SUM(fm_sales_fact.store_sales) as sales,
  COUNT(DISTINCT fm_sales_fact.product_id) as procucts
FROM
    fm_sales_fact
   INNER JOIN fm_store on fm_sales_fact.store_id=fm_store.store_id
   INNER JOIN fm_customer on fm_customer.customer_id=fm_sales_fact.customer_id
where
   fm_sales_fact.sales_year IN (1998, 1997)
GROUP BY
  fm_store.store_country, fm_store.store_city, fm_sales_fact.sales_year,
  fm_sales_fact.product_id, fm_customer.gender, fm_customer.fname, fm_customer.lname

 

Dynamic SQL query with prompts

 Query Pattern

Description and Example

IF Condition

If store_country is defined on filter, then execute the inside block.
 
@IF ${store_country}
    AND fm_store.store_country in (${store_country})
@END IF

IF ELSE Condition

If store_city is defined then execute the If block. Other condition is executed on ELSE block.
 
@IF ${store_city}
AND fm_store.store_city in (${store_city})
@ELSE
AND 1=1
@END IF

IF Compare Parameter

Execute block with checking parameter values.
 
@IF ${store_city}=*Califonia*
AND fm_store.store_city in (${store_city})
@ELSE
AND 1=1
@END IF

Variables

Replace variables with proper values at runtime.
 
${store_country}

Default variables

${SHEET_NAME} : Sheet name of request items.

 

Query example with dynamic filtering options

SELECT
  fm_store.store_country,
  fm_store.store_city,
  fm_customer.gender,
  fm_sales_fact.sales_year,
  fm_sales_fact.product_id,
  fm_customer.fname, fm_customer.lname,
  SUM(fm_sales_fact.store_sales) as sales,
  COUNT(DISTINCT fm_sales_fact.product_id) as procucts
FROM
    fm_sales_fact
   INNER JOIN fm_store on fm_sales_fact.store_id=fm_store.store_id
   INNER JOIN fm_customer on fm_customer.customer_id=fm_sales_fact.customer_id
where
   fm_sales_fact.sales_year IN (1998, 1997)



@IF ${store_city}
AND fm_store.store_city in (${store_city})
@ELSE
AND 1=1
@END IF
 
GROUP BY
  fm_store.store_country, fm_store.store_city, fm_sales_fact.sales_year,
  fm_sales_fact.product_id, fm_customer.gender, fm_customer.fname, fm_customer.lname

 

Create SQL InlineView

Within the Workspace folder, create a Customer Analysis folder and create a Business Object called CustomerBO.

Click the Navigation tab.

1

Click the Navigation tab.

2

Click the Tutorial folder to select it.

3

Click the New Item drop-down on the navigation toolbar menu and click on the folder.

 

In the Create New Item popup window, enter Customer Analysis as the item name and click the OK button.

 

Click the Refresh button to view the list again.

 

Create a Business Object.

1

Click the Customer Analysis folder to select it.

 

Click the New Item drop-down button.

2

In the drop-down list, select the Business Object.

3

Enter the name of the Business Object to be created and click the OK button.

 

Click on the name of the Business Object created in the navigation, and then click the Edit Cube button in the toolbar to open the edit screen.

 

1

Click on Business Object item to open in a tab window.

2

Select database source to use on this Business Object.

 

 

Registering Inline SQL View

1

Click Inline View button on toolbar to register SQL query.

2

Type or paste from clipboard for the SQL query.

3

Click validate button to check validation of the sql.

4

Click confirm to save to meta database.

 

Create dimension items

1

Click the folder where you want to store the metric

2

Drag the extracted field items from the SQL Query Wizard (3) and drop them into the area.

3

The dropped metric item is queried.

 

Click the Save button to reflect your changes.

 

Multiple Measure Register and Measure Definitions

  

Steps to follow

1

Check the items to be registered as the measure item.

2

Click on the multiple measure registration button on the toolbar to select the meter item.

3

Change the SQL function (SUM / COUNT) to the appropriate value for the column.

4

Click the OK button to reflect the changes and create new measures.

 

Click the Refresh button on the toolbar to confirm the creation.

  

Set dimension items

Set the dimension to be used for the value lookup.

 

Steps to follow

1

Click the Settings button for the generated Gender item.

2

In the code mapping section, click the Find button to register the Gender item in the generated management menu.

3

Click the OK button to reflect the changes to the server.

 

Creating Sales Report

Create a report on the sales situation using the pre-created analysis cubes.

Creating Report

Create a new report Customer Trend in the analytic cube.

1

Click the New Item drop-down button in the navigation.

2

Click the report item in the drop-down menu.

3

On the Create New Item pop-up screen, select the report item type.

4

Enter the name of the report to be created

 

Click the OK button to reflect the changes and create a new item.

 

Click the report item created in the navigation list to open the edit screen.

Select the analysis business object entity.

1

Select the business object entity to use for the analysis. Select the Customer you created.

2

A list of existing analysis business object is displayed. If it is not listed, click the 1 button to select it. Click the OK button after selecting.

 

Config Pivot Information

Set pivot information in pivot design mode.

1

Click the design button on the report toolbar to switch to design mode.

2

Drag items from the business analysis folder and drop them into the row / column / measure area respectively.

3

Target row area to be retrieved, column area, measure area

Click the Run button to view the report.

Adding Filter Panel

Add a filter panel item.

1

Click the design button on the report toolbar to switch to design mode.

2

Drag items from the business analysis folder and drop them into the row / column / measure area respectively.

3

Target row area to be retrieved, column area, measure area

 

Modify the layout properties.

1

Click the design button on the report toolbar to switch to design mode.

2

Drag items from the business analysis folder and drop them into the row / column / measure area respectively.

3

Target row area to be retrieved, column area, measure area

 

Drag the filter item and register it as a filter item.

1

Click the design button on the report toolbar to switch to design mode.

2

Drag items from the business analysis folder and drop them into the row / column / measure area respectively.

3

Target row area to be retrieved, column area, measure area

Switch to Run mode to view the report.

Dashboard using Drill Filtering

Add a drill target sheet item

1

Click the design button on the report toolbar to switch to design mode.

2

Drag items from the business analysis folder and drop them into the row / column / measure area respectively.

3

Target row area to be retrieved, column area, measure area

 

Configuring Filtering target sheets and options

1

Modify the layout properties.

2

Drag items from the business analysis folder and drop them into the row / column / measure area respectively.

3

Target row area to be retrieved, column area, measure area

 

Adding Drill to Detail Reports for selected region

 

1

Click the design button on the report toolbar to switch to design mode.

2

Drag items from the business analysis folder and drop them into the row / column / measure area respectively.

3

Target row area to be retrieved, column area, measure area