Joining, Filtering OLAP from Relational SQL.

[raviahuja21]

Hello Folks,

I have created a powerplay cube in Cognos 10.2.1, now I am creating some dashboard reports using my cube as a datasource. However since the cube is disconnected I could not apply the security on the cube, nor can i apply custom views since the security is very specific.

Now the security is something like this, my Cube contains dimension Organization  & Facility, a particular user is associated to Organization and Facility (this is defined in the backend SQL DB) , one user can have multiple facility/organization.

Now since the fact is stored at the Organization & Facility level, I cannot take my user dimension into the cube (perhaps I dont want to), Now what I have done is that I have written a SQL in Report Studio which connects to the database and gets the Organization & facility of the user. ( I have put the userid filter on this query, since I get this information from the logged in user in Cognos) Now I get the list of Organization & Facility of the user but when I join this with the Cube Query it gives me error ..which is quite likely since we cannot join OLAP and Relational Sources.

I can do a master detail but then it ends up showing as many number of crosstabs as the user's organization.

Finally what I decided is that I will create a prompt page, write a javascript code which will select all prompt values (Prompt of Organization & Prompt of Facility) and will submit the prompt page through javascript but unfortunately I am unable to do so, since it gives me the below error:

The secondary request failed. The requested session does not exist and the secondary request does not contain enough information to recreate the session


<sub><script type="text/javascript">
listBoxOrgId.selectAll() = true;
promptButtonFinish();

</script></sub>



I dont really want to rely on javascript for this, but as of now I cannot even think of any alternative either. Can anybody tell me if I am thinking on the right lines or am I missing some trick.

Regards
Ravi Ahuja.

[CognosPaul]

The issue with this is that you'll need to disallow adhoc queries to ensure nobody can access what they shouldn't. If needed, drill ups will need to be secured. Member sets will be able to prevent users from drilling up more than he should, but it's still something to consider. A security view approach is the best way to handle this, can you explain why you can't use them?

A JavaScript solution won't work as it's trivial to break it. Instead, you could use macro functions to achieve your goal. There are a few solutions, but at the most basic level it boils down to building a parameter map based on a query.

Depending on your organizational or security requirements there are a few different directions. If it's defined on a per user basis, you could create a table that has two fields, user and set. user would be the username, and set would be the default set the user sees.


user   |   set
-------+--------
UserA  | set([cube].[dim].[hier].[level]->[MemberA])
UserB  | set([cube].[dim].[hier].[level]->[MemberA],[cube].[dim].[hier].[level]->[MemberB])
UserC  | set([cube].[dim].[hier].[level]->[MemberB])
UserD  | set([cube].[dim].[hier].[level]->[MemberC])
UserE  | set([cube].[dim].[hier].[level]->[MemberA],[cube].[dim].[hier].[level]->[MemberB],[cube].[dim].[hier].[level]->[MemberC])

Set the parameter map key to user, value to set, push the package again and use:
#$setLookup{$account.personalInfo.userName}#

Another way would be to handle the security through Cognos roles. Create a role for each secured member. Again, create a table similar to the users one:

role   |   set
-------+--------
Role1  |  Key1
Role2  |  Key2
Role3  |  Key3
Role4  |  Key4


This solution is a little more difficult, as you need to retrieve multiple values from parameter map.
#CSVIdentityName(%roleLookup)#

Will return the matching keys for the roles, but in a comma separated format. A bit useless for dimensional, and someone with a support contract should open a request with IBM to get rid of that damned CSV bit. I once found a way of extracting the values from the csv, but I'll need to do some testing.

Another way might be to use a bit of fancy SQL work to pivot multiple rows into a single field. Then you could use a master/detail, passing the values into a parameter.

[raviahuja21]

The issue with this is that you'll need to disallow adhoc queries to ensure nobody can access what they shouldn't. If needed, drill ups will need to be secured. Member sets will be able to prevent users from drilling up more than he should, but it's still something to consider. A security view approach is the best way to handle this, can you explain why you can't use them?

Hi Paul, thanks a lot for the help :). The reason why I cant use the cube security by creating custom views is because there are 1000 facilities and some 20 org's and there are around 1000 users and the role assignment for the security to the user is maintained by an ERP App which should be replicated in Cognos.

So my User Facility Org Mapping table is something like this. 

User|Facility|Organization
----|--------|------------
Bob |1       |1
Bob |2       |1
Bob |3       |1
Bob |4       |1
Bob |101   |2
Bob |102   |2
Bob |103   |2
Bob |104   |2
Sco |1        |1
Sco |2        |1
Sco |6        |1

Now my fact table is something like this:( I have built a Powerplay Cube out of this fact)

Facility|Organization|Occurrence(Measure)
--------|------------|---------
1     |1        |20
2     |1        |15
3     |1        |12
4     |1        |18
5     |1        |45
6     |1        |22
101     |2        |65
102     |2        |74
103     |2        |63
104    |2        |91


Now in the report studio I get my fact from the Powerplay Package and I have created a query to get data from the User Facility Org mapping table. I cannot create groups and role since there are 1000 facilities and 1000 users and 1 user can have access to 1000 facility as well and on top of that the mapping is likely to change in the ERP, and it should be reflected back in Cognos.

I somehow want to filter the orgs and facility that I am getting from my relational query. 


Can you tell how can i use the macro's instead of javascript?

[CognosPaul]

From the sample you gave, it looks like each facility is unique to a specific organization. Is that correct? Can you make a single hierarchy with Org as Level 1 and Facility as Level 2?

Are there multiple levels of Organization or Faciltiy? If so, are they unbalanced or ragged? Are keys shared between levels?

What type of database do you have? How often is the data refreshed? Can you make a table or view if needed?

How will users consume this data? Will it be through adhoc tools, like Workspace and Analysis Studio, or only through predefined reports?

[raviahuja21]

Hi Paul

From the sample you gave, it looks like each facility is unique to a specific organization. Is that correct? Can you make a single hierarchy with Org as Level 1 and Facility as Level 2?

Yes you are right here.

Are there multiple levels of Organization or Faciltiy? If so, are they unbalanced or ragged? Are keys shared between levels?

What type of database do you have? How often is the data refreshed? Can you make a table or view if needed?

It is a relational db (SQL Server 2010), the data is refreshed daily. Yes I certainly can make views and tables if needed.

How will users consume this data? Will it be through adhoc tools, like Workspace and Analysis Studio, or only through predefined reports?

Well right now I am trying to achieve this for pre-defined reports, but I would really like to implement this data security for Ad-hoc reporting as well.

[CognosPaul]

I'm beginning to get a clearer picture.

Pivoting the rows into a single string isn't an issue, we can use xml path for that. My concern now is the raw number of possible members. Do you have any other levels in the org chart that can make things easier? I honestly don't know how long it would take a powercube to aggregate so many members.

One of my clients is an international manufacturer, with dozens of factories throughout the world. The security hierarchy there is set up by region, country, province, factory, department. So while there are hundreds of departments, nobody has more than 10 members.

adapt and run this query:

Code Select Expand

Select
    t.username
  , left(t.facility,len(t.facility)-1) as "facilities" //get rid of the last comma
  , len(t.facility)-1 "length"
From(
  Select distinct
      OM2.username
    , (
        Select OM1.facility + ',' AS [text()]
        From dbo.orgMap OM1
        Where OM1.username = OM2.username
        ORDER BY OM1.username
        For XML PATH ('')
      ) facility
  From dbo.orgMap OM2
  ) t

That should effectively pivot the facilities for each a single row for each user.

[raviahuja21]

One of my clients is an international manufacturer, with dozens of factories throughout the world. The security hierarchy there is set up by region, country, province, factory, department. So while there are hundreds of departments, nobody has more than 10 members. adapt and run this query:

Hi Paul,



Thanks for your reply and help. I am sorry to bother you But I am not really understanding how will getting all facilities for a user in a single row help.

Well in my case a user can have access to 1000 facilities.

Do you mean I should create a group in Cognos of this value and use a CSVIdentityList to filter? or is there something else that you are trying to say.

[raviahuja21]

basically my question is what do I do once I get these facilities.

[CognosPaul]

Once the facilities are in a single row, there are several things you could do. The easiest thing would be to do a a master/detail into a parameter. Let's take a simple example using a relational table.

Let's say you have a query on that view. You pull in facilities and filter by username = #sq($account.personalInfo.userName)#

You drag a singleton onto the page, associate it with the query and put the facilities field into the properties.

Next, drag a list into the singleton, put facilities into the list and the following filter:
[facilities] in (#prompt('prm_facilities','token')#)

Set the master detail to facilities -> prm_facilities

The token prompt and filter will resolve to [facilities] in (1,2,3,4)

Another way would be to create a parameter map on that view. Username is the key, facilities is the value. Then the filter would be [facilities] in (#$facilitiesLookup{$account.personalInfo.userName}#)

To use it in your cube, you should modify the view to make the set:

Select
    t.username
  , 'set('+left(t.facility,len(t.facility)-1)+')' as "facilities" //get rid of the last comma
  , len(t.facility)-1 "length"
From(
  Select distinct
      OM2.username
    , (
        Select '[Cube].[Dim].[Hier].[Level]->:['+OM1.facility + '],' AS [text()]
        From dbo.orgMap OM1
        Where OM1.username = OM2.username
        ORDER BY OM1.username
        For XML PATH ('')
      ) facility
  From dbo.orgMap OM2
  ) t

At that point it's the same idea, master detail with a token prompt, or parameter lookup:

#prompt('prm_facilities','token')# for the master detail or #$facilitiesLookup{$account.personalInfo.userName}#

[raviahuja21]

Once the facilities are in a single row, there are several things you could do. The easiest thing would be to do a a master/detail into a parameter. Let's take a simple example using a relational table.

Let's say you have a query on that view. You pull in facilities and filter by username = #sq($account.personalInfo.userName)#

You drag a singleton onto the page, associate it with the query and put the facilities field into the properties.

Next, drag a list into the singleton, put facilities into the list and the following filter:
[facilities] in (#prompt('prm_facilities','token')#)

Set the master detail to facilities -> prm_facilities

The token prompt and filter will resolve to [facilities] in (1,2,3,4)

Another way would be to create a parameter map on that view. Username is the key, facilities is the value. Then the filter would be [facilities] in (#$facilitiesLookup{$account.personalInfo.userName}#)

To use it in your cube, you should modify the view to make the set:

Select
    t.username
  , 'set('+left(t.facility,len(t.facility)-1)+')' as "facilities" //get rid of the last comma
  , len(t.facility)-1 "length"
From(
  Select distinct
      OM2.username
    , (
        Select '[Cube].[Dim].[Hier].[Level]->:['+OM1.facility + '],' AS [text()]
        From dbo.orgMap OM1
        Where OM1.username = OM2.username
        ORDER BY OM1.username
        For XML PATH ('')
      ) facility
  From dbo.orgMap OM2
  ) t

At that point it's the same idea, master detail with a token prompt, or parameter lookup:

#prompt('prm_facilities','token')# for the master detail or #$facilitiesLookup{$account.personalInfo.userName}#

Once the facilities are in a single row, there are several things you could do. The easiest thing would be to do a a master/detail into a parameter. Let's take a simple example using a relational table.

Let's say you have a query on that view. You pull in facilities and filter by username = #sq($account.personalInfo.userName)#

You drag a singleton onto the page, associate it with the query and put the facilities field into the properties.

Next, drag a list into the singleton, put facilities into the list and the following filter:
[facilities] in (#prompt('prm_facilities','token')#)

Set the master detail to facilities -> prm_facilities

The token prompt and filter will resolve to [facilities] in (1,2,3,4)

Fantastic solution. You are a genius Paul :) Thanks for the help it works fantastically well.

Cheers :)

Regards
Ravi Ahuja.