Powerplay Transformer Model checklist

This guide gives a check list when quality reviewing a Transformer model. The check list is a suggested proven practice. By comparing your models against the items within this check list you will be able to go a fair way in stating that you have a good Transformer model that follows a number of proven practices.
These rules are not set in stone. Users may have valid reasons to deviate from these rules. Any deviations should be documented so that those following in your footsteps understand why standard proven practices have not been followed.
The guide does not state if a Transformer model is good or bad from a business perspective, nor if the resulting powercube will be efficient. Such judgement needs business knowledge. When performance tuning, knowing the business goal and designing a solution that better achieves these often gives a greater performance increase.
Applicability
The guide is applicable to all versions of IBM Cognos PowerPlay Transformer.

---

General Checks for the Transformer Model
These checks relate to the entire Transformer model.

1. Use the Tools > Check Model menu item to easily identify any errors or warnings.
   This tool is often overlooked yet it gives a good summary of any issues found with the Transformer model and you can easily identify whether any errors or warnings are acceptable.
2. The model file is in MDL format.
   The PY? format should only be used if model load time forms a significant part of the cube build times.
   • An MDL version of the model ensures the model does not bloat or fragment over time when categories are deleted.
   • Ensures forward compatibility that the model can be loaded into later versions of Transformer.
   • N.B. For datasources that require passwords, MDL does not store these.

---

Data Sources
This section deals with the data sources used within Transformer.

Figure 1 Data Sources Window

General Data Sources Checks

1. Verify queries are split into dimension and fact queries.
   An exception to this may be when a dimension is populated from a fact query. In this circumstance it may be foolish to read the fact table twice –once to get the dimension and again to get the facts. However if the query to get the distinct dimension list is of a low cost then two queries should be used.
2. Verify whether a naming convention is in place to easily identify fact and dimensional queries.
3. Verify dimensional queries appear before all fact queries.
   In Cognos 8 Transformer the help documentation states that this is not necessary anymore however the way Transformer decides what is dimensional vs. fact has not been documented. This best practice should still be followed.

Individual Data Sources Checks

1. Use the 'Show Scope' tool.
   You can use this tool on all Data Sources to ensure queries are only impacting expected dimensions. This tool can be found by right-clicking the data source and selecting 'Show Scope'.
2. Make sure no unreferenced columns are used.
   If a column is not used by model then we should not bring that data into Transformer. Right-click the data source and select the ‘Show Reference’ tool to verify this.
   
   Figure 2 Column References Dialog
   
   
   If the data source is not a Framework Manager or a Cognos 8 report then remove unused columns from the underlying data source itself, not just from the Transformer query. For example if an IQD is used, it contains 10 columns but only 2 of those columns are used by Transformer then edit the IQD to remove the 8 unused columns.
3. Check Timing attributes.
   For dimensional queries only use Generate Categories or/and PowerCube creation Generate Categories only.
   
   Figure 3 Datasources Properties Timing attributes
   
   
   For Fact queries, which do not populate dimensions, set the attributes to Create the PowerCubes.
4. If you are using a data warehouse where surrogates keys are guaranteed to be unique then consider Maximise data access speed.
   
   Figure 4 Datasource Uniqueness verification attributes
   
   
   
5. Set the current period attribute only set on appropriate queries.
   Consider having a query that does nothing but set the current period.
6. Auto Summarize property is checked /unchecked.
   This setting depends upon the source data being read. If the source data is being used at the same granularity as the underlying table this should remain unchecked. If the source data is not consolidated then this should be checked. Consider the effect this setting has upon the generated SQL of the data source.
   – introducing summary functions.
   • Ensure that the query has appropriate identifier and fact usage attributes set for this setting to be effective. These will need to be set in the source – either Framework Manager or the report. Again review the SQL to ensure appropriate grouping and summary functions are being applied.
7. Fact Data is consolidated.
   If the auto summarize option is not available then ensure the fact query is consolidated. That is it only brings in one row of data for a unique key combination.
   For example if an IQD is used you may have to edit the IQD to introduce appropriate Grouping and Summary functions.
8. Compare Native and Cognos SQL.
   Ensure that the Cognos SQL is being turned into efficient Native SQL. For example use native sql functions where available.

---

Dimensions
This section deals with the dimensions used within Transformer.

1. Category Codes are unique.
   Use the Find Category option to search for category codes that contain a ‘~’character. Ensure this test is performed on a populated model.
   
   Figure 5 Find Category Dialog
   
   
   The only category codes where this is acceptable is blank where blanks are suppressed. Having unpredictable category codes can impact MUNs and reports based upon them.
2. Calculated Categories / Special Categories do not use categories that themselves use Surrogate Keys / Unpredictable keys for their category codes.
   There is no guarantee that such keys will be the same between Development, Test and Production systems. If these keys /codes change between environments or over time then and Calculated Categories and Special Categories will become invalid.
3. Dimensions with alternate drill paths excluded from auto-partitioning.
   Partitioning on the primary drill path could cause poor performance on the other hierarchy.
4. Flat dimensions excluded from auto-partitioning as they will not be useful for partitioning.
5. Dimension levels that are 1:1.
   If a category always drills to one and only one category then the two levels should be merged. A quick way to see if this is the case is to populate the model and check the category counts on the levels of the dimensions.
   This is commonly seen when a data warehouse is used as a source and a level is created for the lowest level of the dimension and another level is created for the surrogate key level. The surrogate key level is often suppressed.
   Either bring the fact in with the key of the dimension, or merge the two levels, making the SKey the source and the appropriate field the name etc. Beware of the impact that this may have on previous points.
6. Lowest level of a dimension is unused.
   If the lowest level of a dimension is suppressed then is it really needed? This is a last resort.
   If it has to be in the model and it is suppressed consider using the summarizing function on the level above. This will reduce cube sizes.
7. Scenario dimensions have a default category set.
   
   Figure 6 Dimension Properties Dialog
   
   
   
8. Alternate Hierarchy root categories are given meaningful category labels and codes.
   
   Figure 7 Category view
   
   
   
9. Dimensions which have the same or similar categories can be distinguished by the user. Consider if a sales cube has Ordered Date and Shipped Date as dimensions. These two dimensions contain identical years, months and dates. If a user places either of these dimensions on a report how would they know what month they are looking at?
10. All dimension levels are given meaningful names. In the latest reporting studios level names are very visible to users.
11. If all categories within a level have the same category action applied. If all categories within a level have a category action applies (for example exclude) then consider applying that action to the dimension level rather than the individual categories. If the majority of categories have this applied then again consider applying to the dimension level and then un-actioning the other categories. This statement is also applicable to custom views.
12. Category Uniqueness is only ticked on levels that are appropriate –not all levels.
13. Refresh options are only ticked when using a populated model. If part of the build process is to generate all categories within the model then there is usually no need to refresh labels etc.

---

Measures
This section deals with the measures used within Transformer.

1. Set an appropriate measure storage type. If you are using a small set of the overall data when developing then beware of using a 32 bit measure – whilst this may be fine for development the total data volume may be too large for this data type.
   64 bit measures require more storage so will result in larger powercube files. Only use a 64 bit measure if you are sure your data set requires it. When using 64 bit measures ensure appropriate scale and precision values are set.
   This ensures the model can cope with large amounts of data. Ensure appropriate scale and precision values are set.
2. Appropriate Missing Value is set. Consider the impact 'NA' may have on any calculated measures that may use this measure. Customers with prior versions of Transformer may prefer to set this to zero rather than the new default of NA.
3. Time state roll up of non-additive measures is set. For example Closing Balance or Stock Level measures may be more appropriate to roll up using Current Period for actuals but Last Period for budgets.
4. Format is set.
5. Consider using an internal model name for the measure name and a user friendly Measure Label and Short Name. This give better maintainability of the model and any reports should the measure name need to change in the future.
6. Show Scope. Use the 'Show Scope' tool on all measures to ensure scope is as expected.

---

PowerCubes
This section deals with the PowerCubes used within Transformer.

1. PowerCube Partition status.
   Check that the summary partition size is smaller than the desired partition size to ensure partitioning was successful.
2. Auto Partition.
   On modern server architecture a desired partition size of 2.5m is a good starting point. Ensure the Estimated number of records reflects the environment the PowerCube will be eventually built. Increase the Maximum number of passes to create smaller partitions should the build be unable to hit the desired partition size.
3. Remove the file path from the PowerCube filename and use the preference options to build in the appropriate location.
4. Enable Crosstab Caching on the cube Processing options.

Cognos Administration

Administrators for IBM Cognos environments are able to control user access to object such as reports, packages, and data sources, as well as access to functionality within the application. Users can access current activities, past activities, upcoming activities, and schedules on the Status tab in IBM Cognos Administration to monitor the server activities and manage schedules (as long as they have been granted access). To grant access to the scheduling functionality independently from the monitoring functionality, use the scheduling capability.

The scheduling capability controls access to the scheduling functionality for items that can be run, such as reports. The following secured features are associated with this function:

Schedule by day

Users can schedule entries daily.

Schedule by hour

Users can schedule entries by the hour.

Schedule by minute

Users can schedule entries by the minute.

User denied access to schedule by minute: If a user is denied access to the schedule by minute capability, by minute scheduling is also denied for other capabilities that allow by minute scheduling, for example, the schedule by month capability.

Schedule by month

Users can schedule entries monthly.

Schedule by trigger

Users can schedule entries based on a trigger.

Schedule by week

Users can schedule entries weekly.

Schedule by year

Users can schedule entries yearly.

Scheduling Priority

Users can set up and change the processing priority of scheduled entries.

Sam Carter is the Administrator for the IBM Cognos environment at the Great Outdoors company. Sam is busy and decides to delegate several of his more simple tasks so that he can pay more attention to other work. Sam decides to permit certain users at the Great Outdoors company to access the IBM Cognos scheduling features so that they can create and manage their own schedules. Because Lynn Cope is an Advanced Business User and Report Author at Great Outdoors company, Sam decides to grant her access to IBM Cognos scheduling capabilities.

To simplify the addition of this capability for more users in the future, Sam creates a new role in the Cognos namespace called self-serve scheduling and adds Lynn Cope to that role. Sam now grants scheduling capabilities to the new self-serve scheduling role. 

Cognos Finance System Administration Guide 

To grant access to scheduling capabilities within IBM Cognos BI:

1.	Log on to IBM Cognos BI using credentials for a user with access to administrative features and open IBM Cognos Administration.

2.	Click the Security tab.

3.	Click Capabilities.

4.	The default display settings for IBM Cognos BI only display 15 items per window. Scheduling capabilities are controlled through a capability called Scheduling. If necessary, you can display the scheduling capability by clicking the Next icon.

5.	Note that the scheduling capability is highlighted as a live link. This indicates that there are additional features associated with this capability that can be secured individually. Click Scheduling. Figure 10-40 shows the Capabilities panel.

Figure 10-40 The Capabilities panel within IBM Cognos Administration

6.	Access to secured functions can be configured through the capability's properties. Click the down arrow next to Schedule by hour, and then click Set Properties. Figure 10-41 shows the features that are available within the scheduling function.

Figure 10-41 Setting properties to grant access to schedule by hour capabilities

7.	When the Set Properties window opens, click Permissions.

8.	Click Add and browse to the Cognos namespace by clicking Cognos.

9.	Select the role by selecting Self-serve Scheduling, click the add arrow, and then click OK. Figure 10-42 shows the process of selecting the role that will be granted access to the schedule by hour capability.

Figure 10-42 Selecting the custom self-serve scheduling role

10.	Grant self-serve scheduling execute and traverse rights for the schedule by hour capability by selecting Self-serve Scheduling and then selecting Execute and Traverse under the Grant column. Click OK. Figure 10-43 shows an example of granting access to a capability for an IBM Cognos role.

Figure 10-43 Granting access rights

11.	Members of the self-server scheduling custom-created role can now access scheduling functionality and schedule their own activities without administrator involvement.

You can also use the process that we described previously to restrict access to scheduling capabilities. Administrators can permit or deny users the right to schedule their own activities based on schedule frequency or by trigger. For example, one group of users can be granted full access to scheduling functionality, whereas another can be limited to only scheduling activities on a weekly basis.

---

IBM Cognos Active Report

IBM Cognos Active Report is a new report output type that allows professional report authors to create interactive report applications for offline or disconnected use.

Cognos Active Report provides full-featured report viewing, so casual BI users can explore business information without having to rely on network connectivity.

The Active Report provides gains in productivity by providing business intelligence to individuals regardless of their location, situation or connectivity.



Truly portable business intelligence

* Cognos Active Report delivers a disconnected reporting application that is a stand-alone, self-contained file.
* Professionally authored functions provide an engaging report or dashboard experience for the user.

Easy delivery

* End users realize the benefits of the Cognos platform, all in a contained report.
* Active Reports can be burst, scheduled, emailed or picked up by individuals from a private URL.
* Schedule deliveries of Active Reports to a large or small set of users.

Business benefits

* Deliver BI to a broad set of users quickly on small to medium data sets.
* No training or authoring skills required to view the interactive report/dashboard.
* Persistent connectivity no longer required for obtaining and viewing key business intelligence data.
* Provide mass deployment of business intelligence economically and easily.