Cognos Framework Manager Data Source in TM1

Cognos Framework Manager​ package as a Data Source in TM1

How do I use a Cognos Framework Manager package as a data source in TM1?  Your Cognos Framework Manager Development Team has gone through the process of identifying, converging, and formatting various data sources to create a single business view of your corporate data. However, you need the exact same information for budgeting and forecasting models. Instead of reinventing the wheel and going through the entire process (identifying, converging and formatting) again, you can import data from a Cognos BI package via a Turbo Integrator process into the TM1 server.

You will need to install Cognos TM1 Package Connector. This requires the following:

  • Cognos TM1 9.5.1 or later

The Cognos TM1 Package Connector must be installed on both the Cognos TM1 server and the administrative client machines where Turbo Integrator processes are being created for any data source you are using

  • IBM Cognos Business Intelligence Server (see the Cognos BI Information Center for specific versions supported)

http://www-01.ibm.com/support/knowledgecenter/SSEP7J_10.2.0/com.ibm.swg.ba.cognos.cbi.doc/welcome.html?cp=SSEP7J_10.2.0%2F0&lang=en

  • IBM Cognos Framework Manager for package creation
  • If your data source is SAP, then SAP Business Warehouse is required
  • 32-bit database client software is required on all platforms where the Cognos TM1 Package Connector is installed for the specific data source being used (for SAP BW, this requires the SAP GUI or the SAP RFCSDK library files and DLL’s)
  1. Once Cognos TM1 Package Connector is installed, create a new process either in TM1 Architect or Performance Modeler:
    cognos framework manager
  2. Enter in the IBM Cognos BI Server Address (the portal address). Test it and then log on.
  3. Finally, select the Package Location from its location in Cognos Connection.
    cognos framework manager

Did you like this blog on cognos framework manager packages as a data source in TM1?  If so, then check out this blog on Exporting TM1 Dimensions by clicking here

Cognos BI PY Calculation using DMR Data

​Cognos BI PY calculation in a DMR Tips

In theory, time calculations are simple; however the DMR (dimensional model relational) data I was working with last week was presenting a challenge to create Cognos BI PY calculation. DMR is a modeling technique in Framework Manager that identifies predefined hierarchies in relational data; hence providing a pseudo dimensional view of the data. This allows end users to drill-up and down through aggregate levels of data. However, do not mistake DMR for an OLAP environment. In an OLAP environment, you point the system to an intersection by selecting multiple dimensions. DMR in Framework Manager uses filters and aggregations to build hierarchies in relational data.

The second data nuance in this particular data set was that versions and measures were combined as a single fact. The facts I had available were actual period dollars, actual YTD dollars, period budget dollars and budget YTD dollars. This did not create any issues. It just needed to be taken into consideration while developing the reporting strategy.

Since DMR is relational data, end users must select an associated reporting period and fiscal period. This particular data set had 7 years’ worth or data; meaning that if I wanted to report on fiscal period May, there are 7 potentially different reporting periods (“May’s”) to choose from. To eliminate the error of mixing and matching unassociated years and periods, a tree prompt was used with instructions to the end user to select a reporting period. This forced the end users to select a period from the fiscal year on which they wanted to report.

Before the tree prompt was built, a current year parameter was created using a data item in the tool box. This is a crucial step if you are going to use a prompt in parallel period or a cousin function.

Once you have your current year prompt established, select a calculated member from the tool box to build your Cognos BI PY calculation and follow the calculation listed below.

Cognos BI PY calculation

  • tuple (parallelPeriod ([PNLCube].[Fiscal Year and Periods].[FiscalYear].[Fiscal Year],1,([SelectedMonth])),[PeriodDollars])
  • ([PNLCube].[Fiscal Year and Periods].[FiscalYear].[Fiscal Year] = used level in CY prompt
  • [SelectedMonth] = data item parameter
  • [PeriodDollars] = fact query item

Copy and adjusting the fact query item for each version needed in the report.

Cognos BI PY calculation

Hopefully you learned something valuable in this blog about creating Cognos BI PY calculation.  If so, check out this blog on BI admins now being able to use SDK by clicking here.

How to Test Cognos Framework Manager Models with Query Studio

There has been a paradigm shift in how business users receive their information.  Not too long ago, if the business needed data to answer a critical question, they would call IT, submit a formal request for a report to be written, and wait.  The timeframe from when the request was queued to when it was delivered used to take weeks.  Through lost opportunities and poor decisions based on old data, businesses quickly realized how expensive this process was. Enter IBM Cognos and Query Studio.  Today I'm going to write about steps to take to Test Cognos Framework Manager Models in query studio.

Today, IBM Cognos BI Studio allows the business users to query and report from the data directly, providing timely information to decision makers and alleviating IT resources.  However, many organizations still struggle with this transition.  Although Cognos can put more control into the business users’ hands, IT still plays a critical role.  It is now the job of IT to build and provide user friendly models and packages.  This includes building packages with proper business terminology and ensuring that all query items displayed are confirmed and produce expected results.

It is easy to spot a company that has gone from a culture heavily dependent on IT to produce BI and one that has not properly UAT’d their models and packages before rolling them out to production.

The best place to UAT your models and packages is in Query Studio.  This will allow you to ask some very basic, but important, questions.  Can business users easily find the data items they need?  Do the relationships built in Framework Manager produce expected results?  Is all the data necessary to answer the business questions available?

Additionally, Query Studio is simple to understand and learn.  Business users only need a 30-minute training before they are able to create simple list and crosstab reports.  Although it is a good idea to develop test scripts, it is also recommended that business users are encouraged to try to recreate reports already being used, or build queries to answers common questions.

For more information about Query Studio or other IBM Cognos software, contact us at Sales@LodestarSolutions.com.

Framework Manager Time Periods

Have you ever been asked to create a report containing prior year and current? The request is common and most report writers would respond by doing a union of the current year data, plus the prior year data, then present the results on a crosstab. If you have a cube modeler like Transformer, it does the current year and prior year work for you, but what if you don’t have a cube modeler with a time wizard build in? You can create a Time Dimension using a Time Period field in Framework Manager. The technique to derive a hierarchy containing custom time periods will be covered in a later blog post, but for now we will talk about the concept of year of year report and how Time Periods works.

What are Framework Manager time periods?

One would logically think about the calendar when they think of time periods. However, there are many businesses that do not adhere to the calendar for their fiscal reporting. A time period is a concept defined by the business to report on financial or sales results over a standard period of time. These periods are usually defined by years, quarters, months, weeks and sometimes one less thought about such as the seasons of the year.

Where should a time period be defined?
For best performance, time calculations would ideally be defined within an enterprise date table dynamically controlled by a stored procedure. However, if this is not an option, then a query subject within the business view of Framework Manager can also control how you define your business time periods. Doing either one of these eases the process of creating calculations for period comparisons.

Why is time period comparison reporting important?
Year over year or even time period over time period, reporting is important to evaluate the performance of the business or a department. This style of reporting is important in helping a business identify measurable recurring events. The reports can help to identify a negative trend that needs corrective action or a positive trend, which requires increased productivity in specific areas of the business. Such as manufacturing or purchasing to stock trending item(s) at the beginning of the appropriate trend.

Creating Filters in Framework Manager

Filters in Framework Manager - ease the frustration

Creating a filter in IBM Cognos is normally straight forward. Most people are familiar with creating them in Report Studio and Query Studio. For more advanced folks, you might even know how to create a slicer in Analysis Studio that works just like a filter but for dimensionally modeled relational (DMR) data. However, if you have ever tried to create filters in Framework Manager you might be ready to pull your hair out and jump off a tall building. Okay, maybe a short building, but you are definitely losing your hair over it.

Filters in Framework Manager
Sounds simple … you just create a filter on the query subject and include the query subject in the package and click ‘publish’. So far, that is pretty simple - but what about getting fancy and making the filter a prompt and then formatting that prompt so that it is a drop down or a date? Now you are thinking, well … they covered prompting or parameters in IBM Cognos Training Class….what is that class called? Uh … that would be Report Studio Training and you would be correct so, you reach for your class material and thumb through the book to find parameters or prompts. You find it and go step by step and create a filter using the question mark enclosure to create a parameterized filter. Now you are cooking with gas and you do a little happy dance until you notice that your parameter is not right. You are prompting on a date and you want the prompt to show a calendar so that your end users will only see a calendar. So you think “How do I format a prompt in FM?”. You think “Oh, I know I will use that thing they showed us in Framework Manager Training Class…what’s that called? Right! A Parameter Map!” So with confidence you build a parameter map and realize that didn’t work like you planned and now you are stuck.

Well, you are not really stuck because you Google “How to make filters in Framework Manager” and you arrived here … ok, happy dance - someone is going to give me the steps and end my madness.

1. What you filter on must be in the query subject – this will ensure that the parameterized filter will show up no matter what your end user picks from the query subject.

2. You must use question mark enclosures – the syntax looks like this [query subject item] = ?Parameter1?

3. You must use the prompt info – on the item that you are filtering by, you will set the prompt info to be a drop down, date, search type … etc. You will also input the display values and the use values.

4. Publish – publish the package

This little post has been a service announcement sponsored by a frustrated framework manager modeler. You can simply pay thanks by sharing this post with someone else that might be standing on the ledge of a short building.

If you need help navigating framework manager or training on more advanced techniques please contact us at services@lodestarsolutions.com

If you like this blog on filters in framework manager, please check out this blog on IBM Cognos Report Studio: Bursting Cognos Reports by clicking here.

Improving Framework Manager Performance

Framework Manager (FM) is the engine of IBM Cognos Business Intelligence. It is one of the intermediaries between BI and your data sources. The foundation of FM is that it can help you visualize your data and prepare it for reporting. However, Framework Manager is only as good as its data source.

Top 5 things you can do to improve Framework Manager performance:

1. Indexing – creating proper indexing will help FM traverse your data source more efficiently. Although FM can use a database that is not indexed, it will impact performance as the database grows.

2. Normalization – Framework Manager can work with different data structures, but works best when the sources are normalized. Don’t worry about trying to achieve the highest form of normalization – 3rd to 4th will do the trick.

3. Star Schema – Using a star schema will ensure FM creates the most efficient database request. In the rare case, you can also use a Snowflake Schema and still achieve good results.

4. Consolidate – If you have to put together multiple tables in order derive a single value, you might consider doing such work in the database and then present the results in a table for FM. Framework Manager can join multiple tables, however, the more layers you build the longer it takes to return a value. Performance is further compounded by the amount of data that must be returned.

5. Required Elements Only – When building FM only, bring in the elements you are going to use. If you have fields in a view that are not used in the report request, FM will still build the query command with all the fields in the view.

Improving Framework Manager performance is fundamental in creating a successful Business Intelligence environment. Feel free to contact us at Lodestar Solutions for more information at Sales@LodestarSolutions.com.

Understanding IBM Cognos BI Security

Managing security within your reporting environment can be a difficult and risky task if you don’t fully understand your software’s security structure. In this blog, we will discuss the various levels of security and how you can mix and match to come up with the best security plan for your business. IBM Cognos BI security is object oriented. That means you can set security on many different objects within Cognos, but the lowest level of security will win if you have multiple levels of security. In order to understand security and permissions, let’s talk about two examples:

Example 1: Security applied at the data level

Data-level security is administered within Framework Manager. The diagram below shows the highest level of security down to the lowest within Framework Manager :

A Namespace is the highest object you can assign permission to within Framework Manger. The second option for permission is the view level. This level is based on Lodestar’s best practice for Framework Manager design, which encourages modelers to create the following views:

Database View – this level is an exact mirror of your data source. We believe that by making the database view the same as the data source, it allows the modeler to change the business view without having to change the database view. Moreover, by making the database the same as the data source it is easier to find information within Framework Manager.

Business View – this level contains all business terms, necessary joins, calculations, and business nomenclature. This level is published for use in the Cognos Studios.

You can apply security to a query subject and a dimension within a query subject. The dimension is the lowest level of security. You can mix and match security, which means you, can apply security at any level, but the lowest level will win.

Example 2:  Security applied within BI

Framework Manager security is administered outside of Cognos BI, but it still impacts objects created based on packages published from Framework Manager. You can also apply security to objects within Cognos BI, consider the following:

The highest levels of security within BI are folders. When you publish packages from Framework Manager, a folder is created for the package and you can place security on the folder. Now if you have security on the folder, package, and dimensions then when a conflict is encountered – the lowest level of security will win. Mixing security can be tricky to keep track of so I use a matrix to determine which security setting will win. I have to admit the matrix is about 90% effective because there have been times when I couldn’t figure out how the security was applied from my matrix so I had to roll back the security and then reapply noting the impacts. Once I had applied the desired security structure and noting all the steps I was able to update my matrix.

Top 5 Cognos Framework Manager Tips

Cognos Framework Manager is the brains of IBM Cognos 8. It is often misunderstood and therefore abused by the misguided. So let’s talk about Framework Manager do's and don’ts.

Don’t think Framework Manager is an ETL tool? – Framework Manager is a tool that sets on top of your data sources which includes databases. It can do complex calculations, table joins, execute stored procedures, and manage data level security. However it does not convert data nor does it apply business rules to data to improve quality.  If you need an ETL tool, of course you can purchase Cognos’ Data Manager, or my personal favorite Microsoft SSIS. You decide which one will work for you but be sure to work on your data because Framework Manager cannot disguise poor data quality.

Do create calculations – if Cognos business users will be using the same calculations for multiple reports, then make the calculations in Framework Manager. It will institutionalize the calculation and it will be the same in every report.

Do use business terms – when published in business and presentation view, change query subjects and query items to business terms and names. If you are unfamiliar with Lodestars recommended Framework Manager Vies, stay tuned I will cover that in another post.

Don’t work on Framework Manager at the same time – if two people are in the same project at the same time and try to save their work, the last to hit the save button wins. If you need to work on a Framework Manager Model as team, then consider using source control software.

Do use Parameter Maps – Parameter maps are a prompts best friend. When a report writer creates a prompt using a description instead, a key performance can suffer.  I point that out because description fields are not typically indexed while keys are. A parameter map allows the report writer to still use the description field, but Framework Manager will pass the key to the database instead of the description.

Avoid Writing Cognos Report Studio SQL Queries

IBM Cognos Report Studio is a powerful tool. It is flexible which makes it powerful and dangerous at the same time. You can create a report based on a Framework Manager Package or go directly to the database. I tend to advise that you should always go through Framework Manager to create your reports instead of building custom Report Studio SQL Queries within the report.

Often, people bypass Framework Manager because they may feel that it is rigid. I will admit that it is rigid and for good reason. Whenever I see custom sql code in a report, it is there for really one reason. The code is compensating for what is lacking in the data, whether it is data structure or data quality. I know some report writers are getting all worked up saying that Framework Manager takes a lot of work to do one thing that can be accomplished faster in a report. I agree, but how scalable is the fast route? If you have another report that needed that same code, you would have to report with the same code that has to be managed and maintained separately. However, if it were maintained in Framework Manager you would have one place to make the change and it would apply to all the reports built on the package. Framework Manager allows you to scale your work and custom sql in a report is more of a one off…or as I like to call it …a run off…they tend to run off with your time.

The point of this conversation is to point out that every tool has its place. It is my position that code in a report is not the best use of Report Studio capability. It merely masks the real issue. Data quality and structure must be addressed at the database level. Any attempt to bypass that work will only lead to more headaches later. Ok one more thing before I get off my soap box, even the most talented report writer would run up and kiss the person that could fix their data. I have been asked to write a post on how to use and leverage Microsoft SSIS to scrub your data and I promise I will get to such a post.

Ok so back to this post. When you get into writing code directly into a report, you have moved into programming and out of report writing. You can write the code in the report, then place that code on the database and call it from Framework Manager by using a stored procedure to build the query subject. After that, you can write your report based on the query subject. This is a much more efficient way to build a report using custom code. The database can handle commands and syntax better than the report and this method is more scalable.

If you lessen the amount of custom work in the report and correct the issues in the database directly, you will see a marked improvement in report runtime and happier report writers.

X