Numbers To Info

Leaf members of a level-less hierarchy

2015-04-01T06:43:00.001-07:00

SSAS and TM1 cubes can have unbalanced parent-child (recursive) hierarchies which end up not having any real levels as we are used to see in normal hierarchies. Plus their lowest level of detail (leaf members) could be on any level within the hierarchy...

Problem

No level references and referencing a level is darn useful when making reports.

One example of an issue related to having no levels appears when we try to show all the leaf members in our report. We just don't know where the leaf member might be ending up...

The official workaround in IBM website suggests to filter all members and pick just those that have no children. Something like this:

filter(

[Cube].[Dimension].[Hierarchy],

count([Cube].[Measures].[Value] within set

children(currentMember([Cube].[Dimension].[Hierarchy])))

= 0)

However, this just gives all leaf members. What if we need to find leaf members of just one part of this hierarchy, which the user chooses in their prompt page?

Solution

This is what I made for dealing with such a request for a level-less hierarchy:

filter(

filter([Cube].[Dimension].[Hierarchy],

ancestor(currentMember([Cube].[Dimension].[Hierarchy]),

level([Selected Member])) = [Selected Member] )

count([Cube].[Measures].[Value] within set

children(currentMember([Cube].[Dimension].[Hierarchy])))

= 0)

[Selected Member] is a member from this hierarchy selected in the prompt page.

The inner filter returns all members of the hierarchy which have the [Selected Member] as their parent. Then we can use this narrowed set to find just the leaf members in it. That we do with the help of the outer filter expression which is the solution provided by IBM.

Jep, sometimes we do miss the MDX capabilities of SSAS where this code would be so much shorter... :)

Force Cognos portal to give more feedback to users

2015-02-08T11:27:00.000-08:00

I don't see the numbers for yesterday. Has the data been loaded? Is all OK? These questions start getting asked every time data load goes wrong. And they pop up in our users heads every day before they run their reports. We have to shape up and be more proactive in giving feedback to our users so we get less questions and get bonus points in user-friendliness.

One way to give data load status feedback is to create a special report (maybe even push it out as the starting portal tab, if you dare). However that would not be cool at all. Having to run a report forces the user to go and look for this information. In real life this type of information can only be consumed if it is in the users sight at all times and without any extra effort.

Currently there is no built-in functionality helping dedicate a small piece of the portal to provide a system health-check report. It is time to hack the Cognos portal! Amm.. I mean customize.

Can you have too much pie?

2015-02-04T02:03:00.001-08:00

Jep, you can!

One of the leading contestants at the Error Message of 2015 contest is another gem from good old Cognos:

One of the reports from the default Audit report package produced this one :)

Another good reason to not use these charts!

M.

Framework Manager Error BMT-MDC-0001 Unable to publish

2015-01-15T01:58:00.001-08:00

You might encounter this error message while publishing a FM model.

Usual cases are that the user trying to publish has no Write access to the destination.

I ran into this myself and well... I am admin... So the real cause in this case was that there already was a folder with exactly the same name. There obviously is no feedback from the system about this, so it is all about finding that out yourself!

Can not insert a level from SSAS cube into Report Studio crosstab

2014-06-10T07:16:00.000-07:00

You might run into strange Cognos Report Studio behavior when inserting a level of a hierarchy into a crosstab. First time you drag and drop the level it does nothing and the second time it would generate a warning/error that some insert operation is already in progress, like the one below:

Problems with SSAS role creation are soon to be history

2014-04-22T12:17:00.001-07:00

It used to take for ever, I would always make some careless mistake and would always dread the day when I had to create many SSAS security roles or, even worse, make changes to the existing ones. Well, soon enough we will no longer have to worry about this little annoyance. Check out SSAS role management tool easyROLES! It is currently in beta and you can sign up for testing if you want.

Here is a video showing how fast it is to create many SSAS roles with the tool:

The whole concept is based around the idea that you always get some kind of access list from "the business" according to which you set the roles up. So, why not just copy/paste the specs and let the machine do the dirty work? Try in test environment, verify that all looks as it should, paste into production and be done with it.

Fast and painless!

Upgrading large Cognos content stores

2014-03-20T08:03:00.001-07:00

When you finally decide to upgrade your Cognos environment you will be facing the challenge of the Content Store upgrade. You have a few options available at your disposal of which most popular are to export the CS via Administrative Tools and import into a fresh environment same way and the other method would be to make a copy of CS database, connect your newly installed environment to the copy of CS db and start the services.

The first method is good when your Content Store is not too big. Then you get a clean new db, import all of the contents and all happens fast and trouble-free. However, once you start growing towards 2GB in CS size, all sorts of odd things start to happen and eventually your export starts failing without telling you much more. It just does not know how to handle big chunks of data...

So for the big ones we have to revert to copying the DB. The first time you start up your newly installed environment from Cognos Configuration, it will tell you that db upgrade will be performed. When that process begins, the countdown for service startup starts as well. With a really a big CS and a slower computer you might get an error saying that Cognos service could not start in time and a big red cross in your face. This is the moment when you need to breathe and relax. Check the task manager: is java process consuming one CPU core? If so, don't touch anything just yet. Let it work. Once the machine goes idle, then stop Cognos service and start it again. All should be just fine by then and you will be greeted into your new version of Cognos BI portal.

I have not noticed this odd upgrade behavior on previous versions of Cognos, but 10.2.1 did make fun of me this way. The CS in this case was just over 4GB in size. Well, so far so good with the new version.

Debugging macros in Cognos Report Studio

2013-11-18T03:40:00.000-08:00

There is little to no help when debugging macros in Cognos Report Studio. One can never be really sure of what the Data Item with a macro function inside will return and there is no clear built in way to find that out.

The quick tip/workaround is to temporarily enclose all of your macro into the sq (single quote) statement. That way when you are trying to construct, for example, a member unique name, you can run the Tabular Data view and will receive cognos macro output as plain text item.

Being able to see the exact output before cognos query optimizer gets its hands on the value makes life a whole lot easier! It is here you will find all the extra square brackets that are added by array function and all the single quotes that come together with default csv function behavior.

Cognos promptmany and SSAS member unique name

2013-11-09T13:48:00.000-08:00

When you want to supply prompt values to a Cognos Report Studio report via URL (or for any other reason it is only possible to pass through values as plain text ID's and not member unique names) dealing with the parameters might not be as straight forward as it seems in the beginning. As an example let us use CRM application that links customer ID to a sales report in Cognos BI. That means we would be running the Cognos report via a url that looks similar to this:

http://servername.yourcompany.com/ibmcognos/......&p_CustomerID=302156

Interactive reports

2012-10-31T15:00:00.000-07:00

Came across this great tutorial on how to integrate jQuery into a Cognos report.

That is just brilliant. I never thought that far about reports, but there is a ton of potential in using this functionality! Drop a comment if you have some dynamic report project to share!

On the note of great resources- on the right navigation bar of this blog you will find updated link section. Take a look at those sites- tons of good stuff!

I have also included them in the site search of Numbers To Info, so when ever you search for something on this blog, all of those websites are queried as well. If you know of (or own) a website that is not included in the list but you feel it should- do let me know.

M.

Default prompt values in Report Studio

2012-10-24T13:36:00.002-07:00

I was playing around with some JavaScript today, getting a default value selected in a time prompt. This was a Report Studio report with TM1 cube as a data source. Now we all know how static the default selection in Cognos is... I would never even consider opening the report definition every month to make sure it is pushed ahead one month... So lets take a look into how a real selection of a custom default value could be achieved.

Cognos 10 Error DPR-ERR-2088 The requested Server Group ' ' does not exist

2012-06-27T02:11:00.000-07:00

So you got a DPR-ERR-2088 The requested Server Group ' ' does not exist? I got mine during first startup after upgrading from Cognos BI 8.4 to Cognos 10... So read all the available solutions on line, restarted things, cleaned up stuff- nothing. System wakes up without errors and portal shouts DPR-ERR-2088 The requested Server Group ' ' does not exist!

But the original setup never had any groups defined. I even did a Content Store DB restore and fired up version 8.4 to make sure that was the case. Also cleaned up CMCAPACITY table and rid it of all the old entries (you can just delete all rows from that table while the system is down and while starting up the Content Managers will be re-registered into CMCAPACITY).

OK, so nothing works. I still see DPR-ERR-2088 The requested Server Group ' ' does not exist in fron of me. Let me just grab a coffee and think about this for a while. Come back in five minutes... It works...

So eventually I noticed, that this is a freakish way that Cognos 10 starts up. Just give it a few moments after start up of services and DPR-ERR-2088 should disappear.

Hands-on Cognos 10

2012-03-13T11:25:00.000-07:00

I have been making a bit of fun of Cognos 10 release lately, but now it is time to give it a fair hands-on review. Some of the topics I will cover in this post:

Speed of various components within Cognos 10 BI;
64bit & DQS;
Nice new features;
Upgrading from Cognos 8.4

These are just some things that I came across so far. Will be posting more later on, but for what I've found so far, read on.

Read more »

Cognos Error PRS-CSE-1258: Problem encountered during verification of session capability information.

2011-12-02T01:36:00.001-08:00

Users go into Cognos portal but get an error message saying 'PRS-CSE-1258: Problem encountered during verification of session capability information'. And you have already googled for it, haven't you? All you can find is some IE tab related nonsense that does not really help? Are you running a multi-server environment? Well, it might just be that one of your servers has gone nuts!

Read more »

Sorting a list in Cognos

2011-11-28T04:25:00.001-08:00

There are numerous ways to sort lists in Cognos. Usually the 'proven practice' will tell you to do it via the query and parameters and a lot of other complicated ways. Sure, if you don't mind a simple sort being unresponsive (the whole report needs to be re-processed for this to work) and if you have just one table in your report.

However, if the list you are sorting is in 3rd page of your report... Then, after re-sorting you end up in the first page again... Please do post a comment on how to explain this to a user if you have a good way of dealing with this...

Now, assuming you are dealing with a nice list you could use a JavaScript hack provided by Stuart Langridge and updated for Cognos 8.3 by Gilberto Walker. All you need to do is paste the code below into HTML object in your report.

All lists will then become sortable. The user will only need to click on column name to sort on that column.

Seems to be not working with crazy layout lists (grouping, sectioning can make it funky), but works like a charm on standard lists. Also tried and working with Cognos 8.4 and 10.1.1.

Enjoy your ultra-responsive dynamic sort for Cognos lists!

Read more »

A little bit of pie chart fun

2011-11-14T06:42:00.001-08:00

I know I've been ranting about pie charts a lot... But there are more people who think pie charts are meaningless.

Have fun with Dilbert ;)

(all images are copyright sourced directly from dilbert.com)

Another punch in the face to the anti-pie-chart comunity

2011-11-14T00:51:00.000-08:00

IBM had published a video tutorial on... Dynamically exploding pie charts... "WTF?" is the only thing I could come up with...

Seems that they have not bothered fixing anything that would be useful for C 10.1 and instead wasted their time on worthless clicking on pie charts...

Read more »

Zoomable scatter chart in Cognos 8/10 Report studio

2011-11-12T11:03:00.000-08:00

Haven't we all been in the situation when a scatter chart meant to show all customers or products displays one big blob of dots? Cognos has not managed so far to create a zoomable scatter chart thus living in a stone age through out version 8. Did you just get the idea that maybe they've managed to pull that off in the 10 series? Dream on! Or read on.

Here is a workaround to make your chart zoomable.

Read more »

The progress of user requests

2011-11-08T06:19:00.000-08:00

Almost two years ago I have written a rant about customers asking to build Business Intelligence solutions that deliver only data extracts in tables, not being able to think further etc etc etc... (By the way, reading it one more time today made me smile at how upset I was at the time... She was a tough nut to crack!) Recently Anonymous (sorry, don't know your name...) posted a very insightful comment on that blog post and got me thinking on how the customers have progressed throughout the two years.

And it actually is a positive development. With a little help, surely.

Read more »

Gettig rid of that pesky checkbox border in cognos

2011-10-20T02:42:00.000-07:00

This annoying border arround Cognos checkbox prompt.... Man oh man... It was in my face all these years and one gloomy evening it was enough. So a bit of digging arround the html source and problem is solved!

Read more »

Kerberos and other things you need for fully functional authentication of SSAS through Cognos (Part 2)

2011-10-06T01:42:00.000-07:00

So far we have set up our OS and it is ready to use Kerberos for user authentication in Cognos and SSAS cubes displayed within Cognos. Now it is time to take a look at the web server configuration.

Read more »

Kerberos and other things you need for fully functional authentication of SSAS through Cognos (Part 1)

2011-10-04T02:20:00.000-07:00

In previous post I gave a little overview of what needs to be done in order to get Kerberos authentication working between your Cognos and Microsoft SSAS environments. Now we will look into the Windows related bits and pieces of the process.

Read more »

Kerberos and other things you need for fully functional authentication of SSAS through Cognos (Intro)

2011-09-07T07:05:00.000-07:00

Kerberos is one of the topics that gives the creeps to most BI folks... The setup is seriously full of strange abbreviations and vaguely explained features which might look scary the first time you encounter them (sure got me confused...). Anyhow, upcoming will be a series of posts with which I will try to demystify Kerberos setup for your Cognos installation so you can finally enjoy a fully functional data level security setup in your SSAS cubes.

Read more »

CFG-ERR-0103 Unable to start IBM Cognos 8 service

2011-07-19T07:19:00.000-07:00

If you get an error message while starting up Cognos 8 service and it says: "CFG-ERR-0103 Unable to start IBM Cognos 8 service. Execution of the external process returns an error code value of '-1'." all you need to do is to make sure the service is not disabled.

Go into Control Panel/Services and locate the Cognos service. Make sure it is not set to Disabled. If it is- change to Automatic and try starting up again.

M.

Optimizing Row Level security macro (Cognos Framework Manager)

2010-12-07T13:35:00.000-08:00

A while back I wrote about a method to apply row level security to relational database based Cognos Framework Manager model. The setup in my previous post is valid and relevant still. What you might want to do is aply some more macro functions in order to optimize the output a bit.

Optimization might come in handy if you have a massive authentication provider. Say your company is using MS Active Directory not only to authenticate the workstation users but also is profiling software and mirriads of other things whilst also providing authentication user groups for Cognos. Using just the CSVIdentityNameList() macro you can potentially end up with a huge querry where every row is being matched with an array of 100 items. It is just as inefficient as it sounds.

How to deal with this? Well, there is a bunch of things to be done here in order to smooth things out. Click into the full post to read the details!

Read more »