Issue in counting

[cognos_sourabh]

Hi All,

I am facing an issue in FM.

Extract from the report:

VIP_CODE     CONTACT_KEY     AMT_BALANCE
    A                     201                        10
    B                     202                        20
    C                     203                        40
    A                     204                        30
    B                     205                        20   

VIP_CODE and CONTACT_KEY are from the CONTACT table
AMT_BALANCE is from ACCOUNT table

Now if I remove the CONTACT_KEY

     VIP_CODE       AMT_BALANCE
          A                      120
          B                      120
          C                      120

It is summing up all the Amount balances and showing it against each VIP code

I want to do a change at FM level rather than for a particular report because these columns will be used for adhoc reporting too.

Please suggest.

Thanks.

[Raghuvir]

Hi All,

I am facing an issue in FM.

Extract from the report:

VIP_CODE     CONTACT_KEY     AMT_BALANCE
    A                     201                        10
    B                     202                        20
    C                     203                        40
    A                     204                        30
    B                     205                        20   

VIP_CODE and CONTACT_KEY are from the CONTACT table
AMT_BALANCE is from ACCOUNT table

Now if I remove the CONTACT_KEY

     VIP_CODE       AMT_BALANCE
          A                      120
          B                      120
          C                      120

It is summing up all the Amount balances and showing it against each VIP code

I want to do a change at FM level rather than for a particular report because these columns will be used for adhoc reporting too.

Please suggest.

Thanks.

hi,

have u set the aggregate function for AMT_BALANCE field to total ?

Regards

[bus_pass_man]

I would like for you to consider the possibility that you have double counting.   

Round up the usual suspects:
1.  What's the grain of the fact table?
2.  What's the grain of the other table?
3.  What grains are the columns that you are using?
4.  What determinants are you using?
5.  What keys are in them?
6.  What attributes do you have in them?
7.  What's the usage of the query items?
8.  What does the sql look like for both of the queries that you mention?
9.  Just to confirm, are both queries done with the autosum setting on or off?
10.  If you have just CONTACT_KEY  and   AMT_BALANCE in the query what does the sql look like?
11.  What's the cardinality of the relationships between the query subjects?
12.  In response part of query information are there any relevant messages coming back from the query engine?
13.  What's the regular aggregate for the query items?

[cognos_sourabh]

Hi,

This is the Cognos query generated.

select
       CTCT.VIP_CD  as  VIP_CD,
       XSUM(AR_ACCT_BAL.BAL_AMNT at AR_ACCT_BAL.CUST_KEY )  as  BAL_AMNT
from
       ORADB..ODS_DEV_VIEWS.CTCT CTCT,
       ORADB..ODS_DEV_VIEWS.AR_ACCT_BAL AR_ACCT_BAL,
       ORADB..ODS_DEV_VIEWS.CUST_CTCT_REL CUST_CTCT_REL
where
       (CUST_CTCT_REL.CTCT_KEY = CTCT.CTCT_KEY) and
       (CUST_CTCT_REL.CRM_CUST_KEY = AR_ACCT_BAL.CUST_KEY)
group by
       CTCT.VIP_CD

From this query i understand that BAL_AMT is calculated for each VIP CODE by summing up all the balances of customer

How can i change it?

Thanks

[bus_pass_man]

...and when you went through the list of questions what did you learn?

[cognos_sourabh]

Hi,

I have attached the screenshot of the dimensions in use. Also please find the answers to the question as below

1.  What's the grain of the fact table? -  Currently we have some dummy data where CNCT and ACCOUNT has a 1::1 relation.
2.  What's the grain of the other table? -  CNCT table has CNCT_KEY, AR_ACCT_BAL has ACCT_KEY
3.  What grains are the columns that you are using? Using VIP CODE from CNCT (many contacts can be under same VIP code). ACCT_BAL from ACCT (each acct will have a balance)
4.  What determinants are you using? I tried to use VIP CODE as a group by but the results are same
5.  What keys are in them? CNCT table has CNCT_KEY, AR_ACCT_BAL has ACCT_KEY
6.  What attributes do you have in them? attached screenshot
7.  What's the usage of the query items? Acct_bal is a fact and vip_code is attribute
8.  What does the sql look like for both of the queries that you mention? attached in the previous thread
9.  Just to confirm, are both queries done with the autosum setting on or off?  when setting the autosum it is double counting. without autosum it provides individual vip_code with each acct_bal
10.  If you have just CONTACT_KEY  and   AMT_BALANCE in the query what does the sql look like? attached in the previous thread
11.  What's the cardinality of the relationships between the query subjects? screenshot attached
12.  In response part of query information are there any relevant messages coming back from the query engine? nope
13.  What's the regular aggregate for the query items? Sum

[MFGF]

Hi,

I have attached the screenshot of the dimensions in use. Also please find the answers to the question as below

1.  What's the grain of the fact table? -  Currently we have some dummy data where CNCT and ACCOUNT has a 1::1 relation.
2.  What's the grain of the other table? -  CNCT table has CNCT_KEY, AR_ACCT_BAL has ACCT_KEY
3.  What grains are the columns that you are using? Using VIP CODE from CNCT (many contacts can be under same VIP code). ACCT_BAL from ACCT (each acct will have a balance)
4.  What determinants are you using? I tried to use VIP CODE as a group by but the results are same
5.  What keys are in them? CNCT table has CNCT_KEY, AR_ACCT_BAL has ACCT_KEY
6.  What attributes do you have in them? attached screenshot
7.  What's the usage of the query items? Acct_bal is a fact and vip_code is attribute
8.  What does the sql look like for both of the queries that you mention? attached in the previous thread
9.  Just to confirm, are both queries done with the autosum setting on or off?  when setting the autosum it is double counting. without autosum it provides individual vip_code with each acct_bal
10.  If you have just CONTACT_KEY  and   AMT_BALANCE in the query what does the sql look like? attached in the previous thread
11.  What's the cardinality of the relationships between the query subjects? screenshot attached
12.  In response part of query information are there any relevant messages coming back from the query engine? nope
13.  What's the regular aggregate for the query items? Sum

Hi,

The root cause of your problem lies in the design of your model. The (tiny) screenshot you attached in response to question 11 shows three query subjects - CTCT, ACCT_CTCT_REL and AR_ACCT_BAL. These have relationships as so:  CTCT (1..1) <--> (1..n) ACCT_CTCT_REL (1..n) <--> (1..1) AR_ACCT_BAL

If you want to create a model that will deliver accurate, consistent results, you need to model the objects so that the end result looks like star (or snowflake) schemas. In a star schema, you find two types of tables - a fact table and dimension tables. The fact table contains only measures and keys (to link to the dimensions). The dimension tables contain only descriptive attributes and keys. Fact tables are always at the ''n' end of each relationship, and dimension tables are at the '1' end. Cognos 10 uses the cardinality of the relationships to decide what type of "table" each query subject represents within a query. The rules it uses are:

it is treated as a FACT table if it is at the 'n' end of all relationships used in the query
it is treated as a DIMENSION table if it is at the '1' end of any relationship used in the query

Hopefully you can see that your model does not conform to this - AR_ACCT_BAL (where your measures exist) is being treated as a dimension and ACCT_CTCT_REL is being treated as a fact. Have you taken any training in Framework Manager? These points along with all the best-practice modelling techniques are covered in detail in the course. I strongly recommend you take the training before you develop the model any further.

Cheers!

MF.

[cognos_sourabh]

Hi,

The development of the Framework is from an OLTP structure with relational tables. It turns out to be a snowflake schema. I am aware of the modelling structure and the way should be created but here I am looking for a solution based on only these three tables. The table structure cannot be changed however I can apply determinants.

My intention is to understand the reason for double counting although the flow of data is correct.

Thanks

[MFGF]

Hi,

The development of the Framework is from an OLTP structure with relational tables. It turns out to be a snowflake schema. I am aware of the modelling structure and the way should be created but here I am looking for a solution based on only these three tables. The table structure cannot be changed however I can apply determinants.

My intention is to understand the reason for double counting although the flow of data is correct.

Thanks

Hi,

The schema you included in your screenshot isn't a snowflake - in a snowflake schema you have your measures and keys in a fact table and "legs" of dimension tables that link to this. Your measures are in a table that is being recognised by the query engine as a dimension, so you're going to have to do some remodelling to take into account the assumptions the query engine makes when generating queries.

I'm not suggesting you change the physical table structure. I'm suggesting you add another modelling level above your existing level, utilising model query subjects that are arranged and linked to look like a star schema. Then base your reporting off this. Determinants are there to allow multi-fact/multi-grain queries to operate appropriately where you are querying across different fact query subjects where the measures are held at different granularities. They are used to separate the different dimensional key levels in a conformed dimension. You're not easily going to be able to "fudge" your query generation simply by applying them to your current model structure.

As I mentioned before, this is covered in the FM training. You don't seem to be aware of the basics here in modelling successfully in FM, so I really would recommend you take the training.

Cheers!

MF.