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. If you have then you have to test you FM for that to make sure you FM is creating stitched query or outerjoin query. If FM is not creating stitched query then you will not be able to get the right kind of results. If ther is lot of confusion among different tables joins then make separte start schema grouping for that particular report requirments so that when those 2 fact table are taken in the query, no other table affect that.
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) Never get confused by different 'Layers' of the FM. Layers are nothing but they are like 'Folders' of your desktop. It all depends upon you , how you arrange your objects. Same requirement may be fulfilled by creating 10 layers or 2 layers. There is no fix rule.But FM is a bit complex and all your reports depend upon the FM so we create differnt layers for each thing that we do like creating business names, creating dimensions, short-cuts for report author presentation just to makre sure if somebody else takes over he is clear.
7) 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...
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. If you have then you have to test you FM for that to make sure you FM is creating stitched query or outerjoin query. If FM is not creating stitched query then you will not be able to get the right kind of results. If ther is lot of confusion among different tables joins then make separte start schema grouping for that particular report requirments so that when those 2 fact table are taken in the query, no other table affect that.
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) Never get confused by different 'Layers' of the FM. Layers are nothing but they are like 'Folders' of your desktop. It all depends upon you , how you arrange your objects. Same requirement may be fulfilled by creating 10 layers or 2 layers. There is no fix rule.But FM is a bit complex and all your reports depend upon the FM so we create differnt layers for each thing that we do like creating business names, creating dimensions, short-cuts for report author presentation just to makre sure if somebody else takes over he is clear.
7) 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...
No comments:
Post a Comment