FM Model Creation procedure

[Nandini.t]

Hello All,

I am new to FM. First time i am building a model. can you please guide me the best practices to do the below scenario.

Imported all the tables form DB to FM(with cardinalities as suggested by client) later on they gave us some SQL & join conditions for every model Query Subjects like

SQL Select equivalent:

Query Subject 'Div':

SELECT ORG.NAME AS Name,
  PO.NAME       AS "Parent Organization",
  PO.UNIT_TYPE  AS "Parent Type"
FROM ORGANIZATION ORG
INNER JOIN ORGANIZATION_RELATION
ON ORG.ORGANIZATION_GK = ORGANIZATION_RELATION.SOURCE_ORGANIZATION_GK
INNER JOIN ORGANIZATION PO
ON ORGANIZATION_RELATION.TARGET_ORGANIZATION_GK = PO.ORGANIZATION_GK
WHERE ORG.UNIT_TYPE  = 'DIVISION'
AND RQCOWN.ORGANIZATION_RELATION.RELATION_TYPE = 'PARENT'

Query Subject : 'Customer'

SELECT RQCOWN.ORGANIZATION.NAME    AS "Customer Name",
  ORGANIZATION1.NAME                      AS "Business Unit Name",
  RQCOWN.ORGANIZATION_GK.ENTITY_ID AS "Business Unit Number"
FROM RQCOWN.ORGANIZATION
INNER JOIN RQCOWN.ORGANIZATION_RELATION
ON RQCOWN.ORGANIZATION.ORGANIZATION_GK = RQCOWN.ORGANIZATION_RELATION.SOURCE_ORGANIZATION_GK
INNER JOIN RQCOWN.ORGANIZATION ORGANIZATION1
ON ORGANIZATION1.ORGANIZATION_GK = RQCOWN.ORGANIZATION_RELATION.TARGET_ORGANIZATION_GK
INNER JOIN RQCOWN.ORGANIZATION_GK
ON ORGANIZATION1.ORGANIZATION_GK                 = RQCOWN.ORGANIZATION_GK.ORGANIZATION_GK
WHERE RQCOWN.ORGANIZATION_RELATION.RELATION_TYPE = 'CUSTOMER';


and so on for remiang QSs like 'Branch','Med' etc

> So i just created Model QS ORG & PO form Organization Data Source Query Subject and Organization Relation forn Organization Relation Data Source Query Subject & tried to join according to SQL but its throwing a message telling joins will be alterd. Please guide on this

> Also I created Folders for all the Respective Query Subjects to apply the SQL joins & conditions but its known fact like Query subjects/Items name cant be repeated in 2 different folder of 1 Name space so please guide me the structure to follow which makes much sense.

Thanks in advance for any help....
will be waiting for your replies...

[Nandini.t]

Any updates? Please guide me...

[BigChris]

I'm sorry New@Cog, but I've no idea what you're asking for. The structure that makes the most sense is the one that makes sense to you and your users...

[nblank]

I posted some tips on my own blog: http://www.howtocognos.com/design-of-fm-project/

Some extra tips:
- Don't use relations from source, define them yourselve
- Use an extra 4th layer "Reporting Objects", in here you can put shortcuts from objects in the dimensional layer of business layer.

And as BigChris mentions, create a structure that can help you answer the business's questions.