Count of group in Transformer

[Nunina]

Hi Everyone!

As many of you know, I work for a telecommunications company, in which we handle millions of customers anually. These customers have each a customer_id assigned, and in many cases (specially corporate customers), one customer_id can have several tel_number assigned to it.

I have a cube that reports all my active/inactive customers per TEL_NUMBER. I need to modify this cube so that it reports not only the amount of active/inactive tel_numbers, but also the amount of active/inactive customer_id.

For example:

---------------------------------------------------------
Customer_id Tel_number
---------------------------------------------------------
4992525 123-456-7890
5001169 123-111-2222
5001169 123-111-2223
5001169 123-111-2224
5001169 123-111-2225
5022468 123-222-3333
7158972 123-444-5555
7158972 123-444-5555

I have an Impromptu report that acts as a "feeder" from which I create my iqd. I group the data within the feeder and created a calculation that counts the Customer_id. This is a sample of what Impromptu returns:

Customer               Telephone             Customer_Amount
----------------------------------------------------------
4992525               123-456-7890                  1
5001169               123-111-2222                  1
                      123-111-2223
                      123-111-2224
                      123-111-2225
5022468               123-222-3333                  1
7158972               123-444-5555                  1
                      123-444-5555

Which is exactly what I want.

Now I assign my iqd to transformer, and create the CustomerAmount measure. I have used both sum and count rollup. When I read the cube, it shows 8 records for the CustomerAmount measure, instead of 4.

What am I doing wrong? I even created a footer for the customer_id in my Impromptu report and counted it (to test). It returned the correct amount (4) instead of 8. But when I use it to create the cube, it shows 8.

I know it has to do with my grouping, but I can't seem to see what I'm doing wrong.... Also, I have two datasources in Transformer, which are exactly the same, except that one of them has the grouping by Customer_id and the calculation Customer_Amount.

Any help will be greatly appreciated.

Thanks a lot guys!




Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico

 

[drlex]

Issys,
In situations with more than one source of measures, it's usually better to use a category count rather than a sum of a binary field (in your case, Customer_Amount)

Given that the Customer_id is unique, does the structure of your model allow for you to create a measure that is a category count of customer ID? That is to say, is there a level in a dimension (like 'Customer') where the Customer_id appears only once? If so, you can set it as unique and then insert a measure 'Customer Count' which is a category count for the customer_id level.

It does slow build time, but can save data read time in the grouping to provide the Customer count.

lex

soi la, soi carre

 

[Nunina]

Hi lex!

Actually, the Customer_id does appear more than once, since one customer can have several tel_numbers attached to it.

On the other hand, the model does not include a dimension for customer_id, since it's built to consider only the amount of active/inactive telephone lines (I'm just editing the cube. It was created by someone else.). Maybe I should create a dimension for the customer_id, and then do the category count you suggest....

I'll let you know.

Thanks for your suggestion.





Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico

 

[drlex]

Issys,
so long as the telephone number is the level below Customer_id, that shouldn't be a problem. It's more if a customer_id can be found in more than one higher level - eg Customer_id 5001169 is in San Paulo and San Juan in a district level above.
Post back as required.
lex

soi la, soi carre

 

[DoubleD]

My assumption is that you want your cube to show data like this:

STATE CUST TYPE Customers Phones
AL ACTIVE 20 36
INACTIVE 6 8
WI ACTIVE 15 30
INACTIVE 3 9

Your IQD shouldn't include Phone #'s or Customer ID's. It should only contain Distinct Counts.

If my assumption is not correct, please display what you'd like your cube to display, including what dimensions are included in your cube, and I will help you build your IQD.


Pain is stress leaving the body.

DoubleD

 

[Nunina]

Thanks, guys!

This is what my cube should look like when I filter by the measure of ALL TELEPHONES.

--------------------------------------
           Active | Inactive | Total |
--------------------------------------
Fixed     |  4    |    1     |   5   |
--------------------------------------
Wireless  |  1    |    2     |   3   |
--------------------------------------
Customers |  5    |    3     |   8   |
--------------------------------------

If I filter by the measure ALL CUSTOMERS, my cube should look like this:

--------------------------------------
           Active | Inactive | Total |
--------------------------------------
Fixed     |  1    |    1     |   2   |
--------------------------------------
Wireless  |  1    |    1     |   2   |
--------------------------------------
Customers |  2    |    2     |   4   |
--------------------------------------

Now, the model has 2 datasources. Both have the same information, but one of them has a distinct count field, which I use to count the CUSTOMERS_ID only. Bear in mind that I did not create this cube nor the datawarehouse. The user needs to see the Customer_Id count also, since the only measure the cube shows is the Telephone_number count (the cube only had one measure...)

I'm going to try both lex's suggestion and d's suggestion and I'll post back in about an hour.

Thanks again guys!





Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico

 

[Nunina]

Guys!

Sorry to inform you, none of the things I did worked....

I went to Cognos Support and found some information regarding this issue:

http://support.cognos.com/kb-app/kn...ow_document=1&document_id=112763&version_id=4

I'm working on it right now. I'll keep you posted.

Thanks again!





Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico

 

[drlex]

Issys,
Seeing what you want to display - just a summary position - I'm with DoubleD on this; as long as it is possible to make a distinct count of inactive numbers and customers, a single IQD ought to suffice. (How activity or inactivity is calculated will itself be determined by your DB schema).
lex

soi la, soi carre

 

[Nunina]

Hi Guys!

I finally solved it! I posted the solution in a FAQ (faq401-5291)

I did as DoubleD suggested using the instructions found in this Cognos document. Now, as you know I have 2 datasources in the model. For some reason, it's duplicating the amount of telephone_numbers, although the amount of customer_id's is the correct one.

When I go to the diagram, I see duplicated categories for the dimension that handles the tel_numbers. I tried deleting the categories, so that transformer has to recreate them. It still comes duplicated!

Any comments?

Thanks a lot you guys.



Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico