Getting Started

Logging in and creating a folder for your work

1. Because of high interest in today’s session we are using a single 96 core 768 GB RAM Cognos server with anonymous access allowed. This makes logistics much easier but means we don’t have access to the ‘My content’ feature of Cognos.
2. Click here to access Cognos: http://3.216.29.72:9300/
3. Click ‘Team content’ and navigate to the ‘BACon Users’ folder.
4. Click the ‘+’ button in the navigation window and select ‘Folder’.
5. Give your folder a name with the following format: Initials – favorite movie – favorite color. In my case it would be ‘RPD-Solyaris-Green.’ Hopefully this ensures a unique folder for everyone.
6. We are now ready to begin today’s class.

Organizing for Self Service with Data Sets

In our first example we will prepare a data set for self-service. This data set is sourced from an existing Framework Manager package and is a good example of a technique PMsquare often uses to simplify presentation and improve performance for end users. We will cover the basics of creating a data set, optimizing its performance and using it in a dashboard.

Creating a data set off a relational package

1. Click ‘Team content’ on the left side of the screen and Navigate to Team Content>BACon Modules.
2. Hover over ‘Go data warehouse (query) package and click the ‘More’ button.
3. Select ‘Create data set’ from the ‘More’ menu. The data set screen will launch.
4. The data set screen might look familiar – it is a stripped down version of Cognos Analytics report authoring. You should see a ‘Source’ section with the ‘Go data warehouse (query)’ package loaded, and an area that reads ‘Add data here’.
5. Expand the ‘Sales Target (query)’ folder under ‘Insertable Objects’.
6. Expand the ‘Sales Target (query)’ namespace located within.
7. From the namespace, drag the following fields to the ‘Add data here’ section of the screen. A list will appear and populate with data from the package:
8. Retailer.Region
9. Retailer.Retailer country
10. Retailer.Retailer name
11. Retailer.Retailer code
12. Employee by region.Employee name
13. Employee by region.Position name
14. Time.year
15. Time.Month key
16. Time.month
17. Time.month (numeric)
18. Sales target fact.Sales target
19. Click the ‘Page design’ drop down and select ‘Page preview’.
20. Turn off  ‘Summarize detailed values…’ and observe the effect of summarization on the data displayed in the list. The underlying data is at the level of individual Target transactions.
21. Turn detail summarization back on.
22. Click the ‘Year’ column and select ‘edit layout sorting’ from the sorting drop down menu in the toolbar.
23. Drag ‘Year’ and ‘Month (numeric) to the ‘Detail Sort List’ and click OK. We have sorted our data set by year and month. Sorting on commonly used columns can improve performance.
24. Click ‘Position name’ and select ‘Create custom filter’ from the filter drop down menu in the toolbar.
25. Click ‘Condition’ to access the conditional filter screen
26. In the ‘Input condition’ next to ‘Contains’, type ‘Sales’. This will filter the Position Type column for values that contain the word ‘Sales’. Removing unnecessary rows or columns can improve performance.
27. Click the ‘Save’ icon and select ‘Save as’.
28. In the ‘Save as’ window, navigate to ‘Team Content > BACon user>your folder. This is the folder you created in step 5 of Getting Started.
29. In the ‘Save as:’ text box, type ‘Sales Target Data Set’.
30. Click ‘Save’. The ‘Save as’ window will close.

Finding and loading a data set

1. Click the ‘Home’ icon to return to the Cognos Analytics welcome screen.
2. Click ‘Team content’ on the left side of the screen and navigate to ‘Team Content>BACon users>your folder’.
3. Hover over ‘Sales Target Data Set’ and click the ‘More’ button.
4. Select ‘Reload’. The Data Set loading notification will appear at the top of the screen. It should load and disappear fairly quickly.
5. In the ‘More’ menu, select ‘Properties’.
6. In the ‘Properties’ menu, notice the created, modified and data refreshed dates & times in the upper right.
7. In the ‘Properties’ menu, expand ‘Advanced’ and scroll down to see statistics about this data set:
8. Size
9. Number of rows
10. Number of columns
11. Time to refresh
12. Refreshed by
13. Close the properties menu.

Dashboard Creation

1. Click the ‘More’ button next to ‘Sales Target Data Set’ and select ‘Create dashboard’ in the ‘More’ menu.
2. Select the fourth available template in the ‘Create dashboard’ screen. It has a large rectangle in the center with four rectangles equally spaced at the top.
3. Click ‘Okay’. The ‘New dashboard’ screen will load.
4. On the left side of the screen, click on the ‘Assistant’ icon.
5. Type ‘Show average sales target for country’ and hit enter. Note that Sales target total is a sum measure, not an average. Cognos is able to compute new aggregate types on the fly using natural language query.
6. Click and drag the map that appears into the ‘drop here to maximize’ icon in the center of the screen.
7. Drag ‘Retailer region’ next to the ‘This tab’ icon at the top of the screen to add it to the tab filters.
8. Click the ‘Retailer region’ filter and select ‘Northern Europe’. The map will zoom to extent.
9. Add additional visualizations to the dashboard as you see fit.
10. Save your dashboard as ‘Employee Target Dashboard’.

Combining data sets with other sources using data modules

Data sets alone are great for simple requests; for more complex modeling requirements containing multiple tables, complex relationships or relative time you are better off creating a data module. Data modules can contain multiple data set, data base or excel data sources and are great for IT and power users alike.

Creating a data module

1. Click the ‘Plus’ button in the lower left side and choose ‘Data Module’
2. In the ‘Select sources’ menu, select ‘Team Content’. Navigate to ‘your folder in ‘BACon users’
3. Select ‘Sales Target Data Set’ and click ‘OK’. The data modules screen will open.
4. Click the table ‘Sales Target Data Set’ in the source view. This will load a preview of the data from your data set into the ‘Grid’ tab. Here you can easily make comparison for join compatibility and instantly see the results of any filters, groups or calculations you build

Adding additional data sources

1. Click the ‘+’ icon in the data source view and select ‘Add new sources’
2. Click ‘Data servers and schemas’ and click ‘GOSALESDW/gosalesdw’. Click ‘OK’.
3. Click ‘Discover related tables’ and explore the natural language model generation capability a bit. Then click ‘Previous’
4. Click ‘Select tables’ and click ‘Next’.
5. Choose ‘Go Time Dim’ from the available sources and click ‘OK’. This will add the Go Time Dim table to our data module.
6. Repeat the above process to add:
   • Great_outdoor_sales data server
     • GOSALES/gosalesrt schema
       • Retailer table
       • Retailer Site table
       • Retailer Type table
   • Team content>BACon>BACon files
     • Sales data excel.csv
7. At this point you should have six tables in your data module:
   • Sales Data Excel.csv
   • Retailer
   • Retailer Site
   • Retailer Type
   • Go Time Dim
   • Sales Target Data Set
8. Save the model as ‘Sales Analysis Date Module’ in ‘your folder’.

Data cleanup and prep

1. Click the arrow next to the ‘Go Time Dim’ table to expand it. Notice the large number of fields with a ruler icon. Cognos has incorrectly identified these as measures.
2. Ctrl-click each ruler icon the click the ‘properties’ icon in the upper-right corner of the screen. This will open the properties window.
3. Change ‘Usage’ from ‘Measure’ to ‘Attribute’. Change ‘Represents’ to ‘Time’.
4. Click the ‘Month De’ field then shift-click the ‘Weekday Tr’ field. This will select all the non-english fields in the table.
5. Click the ‘more’ button and select ‘Remove’. This removes these fields from the model.
6. Repeat this process in the ‘Retailer Type’ table to leave only ‘Retailer Type Code’ and ‘Type Name En’.
7. Save your work.

Simplify table structure

1. Click the ‘more’ button next to ‘Sales Analysis Data Module’ in the source view. Select ‘Table’ in the menu that appears.
2. Click ‘Select Tables’ and select the three retailer tables.
3. Click ‘Create a view of tables’ and click ‘Next’
4. Rename the new table ‘Retailer Dim’
5. In the ‘Selected Items’ menu, select the following fields:
   • Retailer Code
   • Company Name
   • Rtl City
   • Rtl Prov State
   • Rtl Country Code
   • Type Name En
6. Click ‘Refresh’ to check your work. Click ‘Finish’ to create your new table.
7. Examine your handiwork in the ‘Custom tables’ tab. This tab makes understanding the data flow in your model much easier.
8. Save your work.

Create relationships

1. Ctrl-Click ‘Sales Data Excel.csv’ and ‘Go Time Dim’.
2. Right-click one of them and choose ‘Relationship’ under the ‘New’ header. This will open the new relationship screen
3. Click each table to select it, then right click and select ‘Create relationship’. The create relationship screen will open.
4. Ensure the 1 indicator is under ‘Go Time Dim’ and the N indicator is under ‘Sales Data Excel.csv’. You can swap these by clicking the wheel icon in the lower left corner of the screen.
5. Select ‘Day key’ from each table and click ‘Match selected columns’ This will create the necessary join criteria.
6. Click ‘Refresh’ to see a preview of your join.
7. Click OK to build the join.
8. Repeat this process to create the following joins:
   • Go Time Dim 1-N Sales Target Data Set
   • Sales Target Data Set N – 1 Retailer Dim
   • Retailer Dim 1 – N Sales Data Excel.csv

Refining and enhancing the model

Adding additional business logic can greatly enhance the value of your data module and make it much easier for professional and self-service authors to quickly create valuable content.

Creating calculations

1. Ctrl-click ‘Quantity’ and ‘Unit sale price’ in the Sales Data Excel table.
2. Click the ‘more’ button and choose ‘create calculation’.
3. Name your calculation ‘Sale Total’ and select ‘x’ from the calculation drop down menu.
4. Note the ‘Calculate after aggregation’ option on this screen.
5. Click ‘OK’
6. Repeat the steps above to create the following calculations:
   1. Cost total: Quantity x Unit cost
   1. Sale margin: Sale total – Cost total
   1. Plan variance: Sale total % Planned revenue
7. Click the ‘more’ button next to the Sales Data Excel table and select ‘calculation’. This will open the advanced editor.
8. Name your calculation ‘Sales Margin’.
9. Copy-paste the following logic:
   1. (Revenue – Cost_Total)/ Revenue
10. Click the ‘more’ button next to ‘Sales margin’ and select ‘Format data’.
11. Select ‘Percent’ in the drop down. Click ‘OK’.
12. Ctrl-click ‘Cost total’ and ‘Sale total’. Click the ‘more’ button and select ‘format’
13. Select ‘Currency’ in ‘Format type’.
14. Select ‘USD’ in the ‘Currency’ drop down.
15. Click ‘OK’

Adding drill paths

1. Expand the ‘Go time dim’ table and click the ‘more’ button next to ‘Current Year’.
2. Select ‘Create navigation’ path.
3. Name the navigation path “Time’
4. Drag ‘Current Quarter’, ‘Current Month’ and ‘Day Date’ into the navigation path. Click ‘OK’
5. In the source view, click the ‘Identify navigation path members’ button.
6. Repeat the process to create the Retailer location path:
   1. Rtl Country Code
   1. Rtl Prov State
   1. Rtl City
7. You can create any hierarchy you want – feel free to make something crazy!

Adding relative time

1. Click the ‘Add new sources’ button and select ‘Add new sources’.
2. Navigate to ‘Team content>Calendars’ and select ‘Fiscal calendar’. Click OK. The Fiscal Calendar table should be visible in your module.
3. Expand ‘Go Time Dim’ table and click ‘Day date’. Open the column properties.
4. Under ‘Lookup reference’ select ‘Fiscal Calendar’
5. Expand ‘Day Date’ and note the large number of relative time filters.
6. Expand the ‘Sales Data Excel.csv’ table and shift-click the calculations we made above.
7. Click the ‘more’ button and open the properties
8. Set the lookup reference to Go Time Dim.Day Date.
9. Expand any of the calculations and note the large number of relative time calculations.

Cleaning up for consumption

1. Open the properties for ‘Sales Data Excel.csv’ and change the name to ‘Sales’.
2. Change ‘Sales target data set’ to ‘Sales target’.
3. Click the ‘more’ button next to ‘Sales Analysis Data Module’ and select ‘Folder’ in the ‘New’ section.
4. Name the folder ‘Retailer tables’
5. Click and drag the ‘Retailer’, ‘Retailer Site’ and ‘Retailer Type’ tables into the ‘Retailer tables’ folder. This will nest the tables within the folder.
6. Click the ‘more’ button next to the ‘Retailer tables’ folder and select ‘Hide from users’.

Managing modules

Securing models

1. Click the ‘Source view’ button.
2. Expand ‘gosalesrt’ data server.
3. Click the ‘more’ button next to ‘Retailer’ and select ‘set data security’.
4. Click ‘Add security definition’ in the properties window. This will open the security window.
5. Name your security filter ‘Analytics users’
6. Expand the ‘Cognos’ namespace and select ‘Analytics users’. Click ‘OK’.
7. Selected ‘Company Name’ from the Filters drop down and click ‘Add a filter’. The filter window will open.
8. Select ‘4 golf only’ and click ‘OK’
9. This sets the data level security at the data server level, not on the level of your individual model! Changes made here will effect ALL models built on this data server, for better or worse.
10. Click ‘Cancel’ to leave the security window.

Model inheritance

1. Click ‘New’ and select ‘Data module’
2. Navigate to the ‘Sales Analysis Data Module’ located in ‘your folder’. Click ‘OK’
3. Notice how each table is now turquoise with a ‘link’ icon on it. These are linked tables and will inherit changes made to their parent module.
4. Check the ‘more’ menu on the ‘Sales’ table. Note that many options are missing. The same is true for individual data items.
5. Note the ‘Break link’ option in the ‘more’ menu. This will disconnect the linked table from it’s parent and it will no longer inherit changes. The ability to click this button can be turned off in security.

Conclusion

In this workshop we combined a data set sourced from a Framework Manager package tables from a Microsoft SQL Server database and an Excel spreadsheet into a single data module, which we made available to self-service and professional authors.

I hope this workshop has unlocked a deeper understand of how the Cognos Analytics data prep features can drastically reduce the time it takes to acquire, model and visualize data for both self-service users and IT professionals. As always, please contact me at rdolley@pmsquare.com with any questions!