We provide online trainings on data warehousing, data visualization and analytical tools like Cognos BI series, Cognos TM1, Informatica, Qlikview, Tableau. Please feel to contact us - bharati.dwconsultancy@gmail.com or cell # +1-562-646-6746 (Whatsapp enabled).
Advanced Framework Manager Model - Using Scripts
Framework Manager Model maintains a log of the activities we do while creating a model. We can utilise the script to get the change log. These scripts can also be used for rebuilding the Framework Manager, while debugging model issues.
STEPS: 1- open the Framework Manager Project for RMR. 2- From the Project Menu --> View Transaction History 3- Check the logs. 4- Select only steps till database layer creation. We exclude the presentation Layer steps; 5- Save it as script. 6- Close the project. 7- Create a new blank project. 8- From the Projects --> Run script. 9- Accept the changes. 10- Save the Project....
SO THATS IT...
Active Reports - Data Toggle Button Bar.
STEPS: 1- Logon to Cognos 10 Portal. 2- Launch --> Report Studio --> Create Active Report. 3- From the toolbox Drag and Drop Data Toggle Button Bar. 4- Add country. 5- From the toolbox add a list. 6- Drag and Drop Country, Year, Sales Amount, Sales Quantity in the list... 7- Create a New connection. 8- Link Data Toggle Button Bar with the list on Country. 9- Run the report.
Active Reports - Data Tab Control
STEPS: 1- Logon to Cognos 10 Portal. 2- Launch --> Report Studio --> Create Active Report. 3- From the toolbox Drag and Drop Data Tab Control. 4- Add country. 5- From the toolbox add a list. 6- Drag and Drop Country, Year, Sales Amount, Sales Quantity 7- Create a New connection. 8- Link Data Tab Control with the list on Country. 9- Run the report.
SO THATS IT....
Creating Calculations / Filters in Framework Manager
We can create calculations/filters in the framework Manager itself so that it can be used across multiple reports.
Steps: 1- Open the Framework Manager project. 2- Click on the Sales Fact Query Subject. 3- Click Add for Calculations Tab. 4- Create a calcuation - Total Cost = Sales Amount * Quantity. 5- Click Ok.
Filters: 6- There are two types of filters in FM. a: Standalone filter - Independent filter. Will be applied when its explicitly part of the report. b: Embedded filter - Part of a query Subject. Is applied automatically when any query item is used from that Query Subject.
Embedded filter 7- Double click Customer Dimension. 8- Click on Filter Tab. 9- Create an Embedded filter - Country in USA, IN 10- Click Ok and close.
Standalone Filter 11- Right Click the Presentation Layer and create a filter. 12- Country in USA
Converting Embedded Filter to Standalone Filter: 13- Double click Customer Dimension. 14- Click on Filter Tab. 15- Convert filter to Standalone.
16- Re-publish the Package. 17- Create the report and use the calcualtions and filters.
SO THATS IT......
Creating a Framework Manager Dimensional Model - DMR.
Dimensional Model enables Drill Up and Drill Down functionality, Where we can traverse through the Hierarchy.
The Steps from #1 to 13 are similar to Framework Manager Relational Model - RMR.
We will use the Project created using the Scripts as shown in Advanced FM- Using Scripts.
SO for this please refer videos:
a- Framework Manager Relational Model - RMR
b- Advanced Framework Manager Model - Using Scripts
STEPS:
1- Open the Framework Manager.
2- Click on New Project. Give a name.
3- Please notice that a Folder same as Project Name is automatically. The project file has an extension .cpf --> Cognos Project File.
4- Select the Language for FM --> English.
5- Select the type datasource.
6- Select a datasource --> DB from where you get the data for your reports.
7- Select the tables / views which are required for reporting.
8- Uncheck the options for identifying joins from DB.
9- Import the tables/views, Known as Query Subject in Cognos world.
10- Identify the usage property for each Columns (Query Items).
Identifier --> Ids/ Codes / Dates
Fact --> Number / Figures / Measures
Attribute --> Remaining Query Items :-)
11- Right click on the project, create 2 Namespaces:
Database Layer
Presentation Layer.
12- Drag and Drop all the Query Subjects in the database Layer.
13- Create Joins between the Query Subjects. Specify the correct Cardinality. From Dimension:Fact --> 1:n
NOW, we will use the TestProject that we created using FM-Scripts:
14- Create Regular Dimension & Measure Dimension the Presentation Layer.
15- Right click Presentation Layer
--> Create Regular Dimension.
--> Create a Hierarchy
--> Create 4 Levels - Year/Quarter/Month/Date
--> From the Database Layer, associate the query items to the different levels.
16- Similarly create Regular Dimensions for Customer Geography --> Country/City/Customer
17- Create Measure Dimension add Sales Amount and Sales Quantity.
18- Create a Package: Right Click Packages--> Create Package.
19- Hide the Database Layer and check the Presentation Layer.
20- Select a location to publish the package.
21- Verify the model and publish the package.
22- Logon to the Cognos 10 Portal --> Open the package in Report Studio and start creating reports.
23- Drag and Drop Level Country, Level Year along with Sales Amount / Sales Quantity.
24- From the data menu, Drill Behaviour enable Drillup-DrillDown
25- Run the report.
SO THATS IT.......
Cognos 10 Framework Manager Advanced Concepts - Working with Parameter Maps.
Working with Parameter Maps.
a- Parameter maps are used to create conditional query subjects that allow for substitutions when the report is run. b- Parameter maps are objects that store key-value pairs.
c- Parameter maps are similar to data source look-up tables. d- Each parameter map has two columns, one for the key and one for the value that the key represents.
Steps:
1- Open the framework Manager 2- Right click on Parameter Map, create 'Lang_Thanks' and enter Key Value Pairs You can also import these values from a file or use an existing
Query Items from the Query Subject.
3- Create three Query Item Calculations - English, Dutch and Hindi English - 'Thank You' Dutch - 'Dunkiwell' Hindi - 'Dhanyavad'
4- Depending upon the run-locale session parameter, we would like to select a query Item Either English or Dutch or Hindi.
5- Create a Calculation; as shown #'[Database Layer].[Customer Dimension].[' + $Lang_Thanks{$runLocale} +']'#
6- Based on the run-locale value passed to this calculation, we can dynamically select the required query item. 7- Now lets over-ride the run-locale to another value and test. 8- Test the calculation
Cognos 10 Framework Manager Advanced Concepts:
Working with Prompts and Session Parameters:
Working with Prompts:
1- Open the framework Manager 2- Right click a Namespace and create a calculation. 3- Create a prompt on Country; use this calculation #prompt('p_Country','[Database Layer].[Customer Dimension].[Country] ')#
as shown 4- Create a Filter using this Prompt. This prompts can be used on the Reports. 5- Test the query subject.
Now lets see the session parameters. 1- login with your user id. 2- create a session variable 3- define a value for country. 4- use this variable in filter.
So, THATS IT......
Steps: 1- Goto C:\Program Files\cognos\TM1\Custom\TM1Data\PlanSamp 2- Copy tm1s.cfg and tm1s.lic and paste to c:\LearningTM1\BDCS\Data 3- Create a folder for log files: c:\LearningTM1\BDCS\logs 4- Open tm1s.cfg in a text editor. 5- Indicate the right ServerName and DataBaseDirectory. 6- Change the following: a- ServerName from ServerName=Planning Sample to ServerName=BDCS. b- DataBaseDirectory path:- c:\LearningTM1\BDCS\Data. c- Leave AdminHost=ACER-700 d- PortNumber=12345 to PortNumber=23456. e- LoggingDirectory=c:\LearningTM1\BDCS\logs 7- Save tm1s.cfg. Creating a new data Server desktop shortcut: 1- Goto C:\Program Files\cognos\TM1\bin and create a shortcut of tm1s.exe. 2- Rename tm1s.exe BDCS and right click properties. 3- In the target box: "C:\Program Files\cognos\TM1\bin\tm1s.exe" -z "c:\LearningTM1\BDCS\Data" ** -z indicates the location of the server configuration file 4- Double-click the BDCS shortcut on the desktop, the new TM1 server is started. 5- Open Server Explorer, and then from the File menu, click Refresh Available Servers. The new BDCS appears
Advanced Report Studio - Dynamic Grouping
Excercise: Here we have a Report --> Country, Year, City, Sales Amount, Sales Quantity. We need to give an option to the user to Dynamically Change the Primary Group to Country or Year.
Steps: 1- Create a Report with Country, Year, City, Sales Amount,Sales Quantity. 2- Create a Static Prompt (?Choice?) on the Report Prompt Page. 3- Specify the Static Choices: Country / Year 4- Specify a Default Choice --> Country 5- Goto Report Page
6- Create two Query Calculations Group-1: if(?Choice? = 'Country') then ([Country]) else ([Year]) Group-2: if(?Choice? = 'Country') then ([Year]) else ([Country])
7- Change the Header Text for Group-1 or Group-2. 8- Hide Country and Year Columns.
9- Run the Report.
SO THATS IT.....
Cognos 10 Training - Advanced RS - Creating Unions - Intersect - Part 7 of 25 Advanced Report Level Joins - Unions - Intersect:
You can create Joins - Unions - Intersect at a report level.
These should be sparingly used, as they impact the report performance.
Excercise: On a Report, from two queries, create a Join Query
Application: Join on the report is used when, there is no join defined at the Framework Manager Model level.
Steps for Creating a Join at report Level:
1- Create two queries in report studio
2- Query-1 Drag City, Year, Sales Quantity and Sales Amount
3- Query-2 Drag Country, City
4- Goto Query Explorer: Drag and Drop Join from the left hand side.
5- Drag Query-1 on the first Box in the Join Query.
6- Drag Query-2 on the Second Box in the Join Query.
7- Specify the Join condition
8- double click the Query-3: Drag and Drop the columns as required from Q-1 and Q-2.
9- Goto Page explorer & Drag and Drop a list.
10- Associate Query-3 to this list; Select the entire list and in the properties pane, Link Query-3
11- From Data Items Tab: Drag the columns from Query-3 in the new list
12- Run the report
SO THATS IT.....
********************************************************************************************
Advanced Report Level Joins - Unions - Intersect:
You can create Joins - Unions - Intersect at a report level.
These should be sparingly used, as they impact the report performance.
Excercise: On a Report, from two queries, create a Union Query
Steps for Creating a UNION at report Level:
1- Create two queries in report studio
2- Query-1 Drag Country, City, Sales Quantity and Sales Amount
3- Apply filter for Country-USA,UK for Query-1
4- Query-2 Drag Country, City, Sales Quantity and Sales Amount
5- Apply filter for Country-IN,UK for Query-2
6- Please note that for UNION, the columns and data type across two queries should be same.
7- Goto Query Explorer: Drag and Drop UNION from the left hand side.
8- Drag Query-1 on the first Box in the UNION Query.
9- Drag Query-2 on the Second Box in the UNION Query.
10- Double click the Query-3: Drag and Drop the columns as required from Q-1 and Q-2.
11- Goto Page explorer & Drag and Drop a list.
12- Associate Query-3 to this list; Select the entire list and in the properties pane, Link Query-3
13- From Data Items Tab: Drag the columns from Query-3 in the new list
14- Run the report
Steps for Creating a intersect at report Level:
15- Goto Query Explorer: Drag and Drop INTERSECT from the left hand side. 16- Drag Query-1 on the first Box in the intersect Query.
17- Drag Query-2 on the Second Box in the intersect Query.
18- Double click the Query-4: Drag and Drop the columns as required from Q-1 and Q-2.
19- Goto Page explorer & Drag and Drop a list.
20- Associate Query-4 to this list; Select the entire list and in the properties pane, Link Query-4
21- From Data Items Tab: Drag the columns from Query-4 in the new list
22- Run the report
