Cognos 8 Powerplay Example

COGNOS 8 POWERPLAY TRANSFORMER WORKSHOP

Document content prepared by Viswas Cheppuri

Working with Existing Project:

Ø Open the Frame work Project “gosales_goretailers.cpf” from the directory “C:\Program Files\Cognos\FM\c8\webcontent\samples\Models” from your PC

Ø Save the project into some other location in the PC as we are going to modify the same.

Ø Try to test any of the query subject and if it gives error then change the Data Source Properties for the project

For the Data Source: gosales

And for the Data Source: goretailers

Set the Schema Name as GORT

Ø Now test the query subject it will give data.

Specification for the Sample Cube:

Cube Name: Revenue Cube

Dimensions

S.No	Dimension Name	Levels	Source Field	Source Table	Remark
1	Time	Year	ORDER_DATE	ORDER_DETAILS
		Quarter	ORDER_DATE	ORDER_DETAILS
		Month	ORDER_DATE	ORDER_DETAILS
		Day	ORDER_DATE	ORDER_DETAILS
		Current Month
		Previous Month
		Last 12 Months
2	Products	Product Line	PRODCT_LUNE_EN	PRODCT_LUNE
		Product Type	PRODUCT_TYPE_EN	PRODUCT_TYPE
		Product	PRODUCT_NAME_EN	PRODUCT_MULTILINGUAL
3	Order Method	Order Method	ORDER_METHOD_EN	ORDER_METHOD
4	Location	Sales Territory	SALES_TERRITORY_EN	SALES_TERRITORY
		Country	COUNTRY	SALES_BRANCH
		Region	REGION	SALES_BRANCH
		City	CITY	SALES_BRANCH
5	Sales Staff	Resp	FIRST_NAME, LAST_NAME	SALES_STAFF
6	Retailer	Retailer Type	RETAILER_TYPE	RETAILER_TYPE
		Retailer Name	COMPANY_NAME	COMPANY_ANME

Measures

S.No	Measure Name	Source Filed	Source Table	Remark
1	Quantity	QUANTITY	Order_details
2	Gross Profit		Order_details	((QUANTITY * UNIT_SALE_PRICE) - (QUANTITY * UNIT_COST))
3	Revenue		Order_details	(QUANTITY * UNIT_SALE_PRICE)
4	Planned Revenue		Order_details	(QUANTITY * UNIT_PRICE)

Preparing Data Source File (IQD) from Framework Manager

Case 1: With Single IQD:

Ø From the Framework Manager Create a Name Space called “Data Source Files” under “gosales_goretailers” Namespace

Ø Under the new namespace Create a folder called Revenue Cube so that all the data sources of this cube can be placed under this folder.

Ø Under this folder create a Model Query Subject namely “Revenue_Cube_Source”

Ø Drag all the required files from the Source into this Query Subject.

Ø From the properties pane set the Externalized Method property of the Query Subject as ‘iqd’

Ø Crete a package namely Revenue Cube and Publish to any location in the local PC.

Ø Now verify the same location we can see the IQD file.

Preparing Model:

Ø Open the Transformer

Ø Go for the New model

Ø Set the data source type as Impromptu Query Definition

Ø Set the path of the IQD file

Ø Un select Run Auto Design Button

Ø And Click OK.

Ø If it gives an error bcz there is no entry in the Cognos.ini file for this data source

Ø Search for the Cognos.ini file in the local system

Ø And make an entry for this data source

Ø And Click Ok, so that the IQD file can be accessed into the model.

Ø Create the Time Dimension by using Date Wizard with the levels of Year, Quarter, Month and Day

Ø Prepare the dimensional model by inserting all the dimensions and Measures

Ø Do the Calculations for the remaing measures

Ø Insert a Power Cube

Ø Generate the Categories

Go to the Diagram and Look at the category Viewer

Remove all the categories not needed

Calculate the current Month in the Category Diagram

Previous Month:

Last 12 Months

Now Built the Power Cube.

View the Power Cube in the Powerplay for windows