Session Parameter and Cubes using Transformer

[gosoccer]

Friends,

We are using the following code as a part of the Framework Package and Report Author which sends it as a parameter to the package.

The package has the following as a part of a Filter in the Query.

Database Layer.SAT.PERSON_ID =
#sq($account.parameters.orgPersonId)#.

This works great and the paramter is passed through a Report Author Report to the Package and
we get the right info.

Now, I have to build the same thing in Cognos Cube using Transformer. The cube and the reports are build and ready to go for the all sites but it is missing the following code for user specific queries.

Database Layer.SAT.PERSON_ID =
#sq($account.parameters.orgPersonId)#.

If anyone can provide me a good document/example/explanations on how to implement in Cube using Transformer, I would greatly greatly appreciate it.

This is the last part of the project and I'll be happiest guys in town!! :)

[cognostechie]

It works because there is an inner join between the table SAT with other tables so it filters the data based on the Person-Id. You cannot use this in the query that populates the cube. You will need to create a dimension for Person-Id which will include all users. After that , you can create views in the model and set the permissions accordingly based on Person-Id.

In Report Studio, you can use this expression against a cube but not in Analysis Studio so setting the security in the model will be the only way to do it, 

[gosoccer]

Thank you for the reply! I do have a dimension called CurrentUser that all users. Now, I could create a customized views in the model and set the permissions but I guess I don't know how to establish the permission based on the user who will login to the report? using session.parameter in the report is giving me an error. How would you set the Model Security that would related the user to the currentuser? We pass many user specific parameters to the Report such as the Smart card information and their site specific information; but I just need to related the user unique site_id to what I have in Cube?

Thx in advance for your time.

[cognostechie]

So you will have to include smart card and site information in the cube and then once the categories are generated then you will have to set the security using cube views . Ex: For user A, exclude all smart cards that do not belong to him so only his/her smart card will be included.

In Transformer, setting security is always a lot of manual work. In your situation, I would have created a DMR in Framework Manager instead of creating Transformer cubes. DMR will automatically inherit the security and joins of Business Layer and Database Layer so your session parameter of current user will work without any additional efforts.

Are you familiar with DMR? It is a virtual cube inside Framework Manager.

[RKMI]

Hi,

I agree and also disagree with the points made. I think reason clients still choose to work on transformer is to use things like alternate drill drill path and faster data access incase they have a robust fact table. It's much easier to create summaries in a cube and it just gives additional level of flexiblity in terms of data massaging such as supress, omit,... I know DMR can do all of that but it's impair based on the design of the DB. But, I do agree it also alot of work when you design the model with security.

Thanks,
RK

[cognostechie]

Where did I say that Tranformer is not a good tool? I merely said that in this situation,  a DMR can be more effective ! 

Was that very difficult to understand?

For the sake of your knowledge, DMR allows multiple heirarchies (multiple drill paths) within a dimension. The purpose of alternate drill path in Transformer is to reduce the no. of categories by combining the categories of the lowest level. This is because Transformer cubes physically capture the data so the concept of alternate drill down so that the cube does not bloat.
DMR does not capture the data so there is no need of alternate drill down, hence the concept of multiple drill downs. DMR is not always slow. It depends.

Agreed that DMR can only work when you have the relationship defined whereas Tranformer can read data from multiple databases without pre-defined relationships. A DMR would run into performance issues when relationships are defined across multiple DBs whereas Transformer would not. 

[bdbits]

We have a number of Transformer cubes here with security spread out over 60+ departments. We use AD groups attached to the views to cut down on needing to modify the Transformer models themselves when security changes, but I wholeheartedly agree with cognostechie that it is indeed a *lot* of manual work, and very tedious and it is easy to make a mistake applying the custom views.

If DMR performance will be acceptable (DQM help here?), or maybe the newer dynamic cubes (I have not tried yet) in 10.2, I would give strong consideration to using that first. We mostly went down the Transformer cube path 4-5 years back in the 8.x days, not so sure we would do that today unless it performance required it. Cubes still have a place, but I think the Cognos technologies introduced in the last few years offer alternatives that are at least worth giving consideration.