FM making you go MAD ???

I have created a prompt for query studio,this prompt is basically made for inforcing row level security. User can select using which role he wants to run report. For that you have put filters in the corresponding FACT table so that whenever something is dragged from the FACT table and query studio report is run.. user is asked to select the ROLE.

But when you run the report , you see all the roles of ROLE TABLE are coming in the report and not those roles that are assigned to you.

ID	Role
John	Manager
John	Executive
David	Analyst

So when John is running report , he is saying all the roles in the prompt , Manager, Executive and Analyst. But this is wrong.

But why this is happening ???

So to correct this you go and put this filter exclusively in the ROLE TABLE query subject, you republish the package. But still you get the same kind of result. You would go made surely.. becuase the same thing happened with me  :(

You do one thing , you go one layer down and put the same filter in ROLE TABLE query subject. Re-publish the package and now run the report. Now you would see only Manager and Executive roles appearing for John.

Why Why Why ???  I also don't know .. This is weird!! This is absurd !!

Only thing I can guess is upper layer was inforcing the row level security so below layer should bring all the things which are required

Thanks

Vishwas

Preparing FM

Following points must be taken into consideration before creating FM model

1) What kind of reports you have. Dimensional or Relational i.e are you going to have drillup/down on reports (dimensional) or you have simple reports(relational). You might have a case where few reports are dimensional and few are relational , in this case you have to decide which table is going dimensional and which table going relational.

2) What kind of filters your reports is going to have.Are there common filters.?If there are common filters then you should create them in report itself.

3) Do you have any report requiring 2 facts join ? Getting the answer of this question if very importatnt.
4) Ask client if he has plan to use model in query studio. If he says "YES" then , : ) your job is doubled. You have to make sure from the beginning that your model is satisfying needs of report authors(Report Studio) as well as Adhoc reports(Query Studio).If you can afford to make 2 model , one for query studio and one for Report Studio , do that if your model is big and complex.

5) All the joins should be defined at the Data Layer. Never do anything at the Data Layer other then making joins between the tables if they have not been imported from the database or not created by FM automatically while importing tables from the database.

6) Please read carefully what is a namespace and what are other objects of  FM. Namespace is very different thing then other objects. Also take care when you copy and paste objects.And take very much care while building new query subjects from below layer, be mindful of what are you doing. Becuase any error and your entire day will be gone.

8) You have to setup row level security at the FM level. And if model is going to be used in query studio as well you have to alter the defintion of all those facts so that if anything is used from those facts row level security is applied. But at the same time you have to also make sure it should not hamper the report studio reports. So to achieve that make sure prompt name ( ?xyz? ) is same in FM query subject(Fact) as well in report studio.

..to be continued...