DMR - Grouping Dimension values

[sunosoft]

Hi All,

Need one help.

I want to group values under one dimension.

Ex. I have handset dimension consisting of values like iphone1,iphone2....samsung1,samsung2 etc.

So i need to modify this handset dimension in such a way that Handset->Apple->iphone1, iphone2 etc
                                                                                                                  Samsung->samsung1,samsung2

Any idea please ?

[MFGF]

Hi All,

Need one help.

I want to group values under one dimension.

Ex. I have handset dimension consisting of values like iphone1,iphone2....samsung1,samsung2 etc.

So i need to modify this handset dimension in such a way that Handset->Apple->iphone1, iphone2 etc
                                                                                                                  Samsung->samsung1,samsung2

Any idea please ?

Sounds to me like you need an extra level in your DMR hierarchy (above the Handset level)? You could call this level Manufacturer, perhaps. You could either create a calculated attribute directly in the level, and use this as the ID and Caption, or else create a calculated query item in the relational layer your DMR is based on, and use this as the ID and caption of the level.

I think the main concern you have is in figuring out the groupings. You could use an if-then-else or a case statement based on the current Handset values, but this obviously won't cater for new handsets when they appear in the data. You'd perhaps need to have an ELSE clause setting these to 'Other', then periodically amend the calculation to add these into the appropriate buckets.

Cheers!

MF.

[sunosoft]

Hi MF.

Thanks for your reply.

Yeah.

I figured out the option of multiple hierarchy to achieve what I wanted.

Now some small issue, not sure if its issue or it will behave like that only.

When I open the DMR model in workspace advanced, and click on "view members tree" option then dimension with multiple hierarchy showing folder ICON. All remaining dimensions are having regular default icon.

Is this is expected behavior or I can change this ICON by any means ?

[MFGF]

Hi MF.

Thanks for your reply.

Yeah.

I figured out the option of multiple hierarchy to achieve what I wanted.

Now some small issue, not sure if its issue or it will behave like that only.

When I open the DMR model in workspace advanced, and click on "view members tree" option then dimension with multiple hierarchy showing folder ICON. All remaining dimensions are having regular default icon.

Is this is expected behavior or I can change this ICON by any means ?

It's expected behaviour - the folder is used to contain the multiple hierarchies for the dimension. You see it whenever you define multiple hierarchies.

MF.

[sunosoft]

Thanks for the confirmation MF.

I got another question here.

Now my Handset dimension is something like below

Handset
   Apple
       (blank)
        iphone1
        iphone2
    samsung
         (blank)
         samsung1
         samsung2

I want to get rid of those blank values under each handset/hierarchy now.

Is that possible by any means ?

[MFGF]

Thanks for the confirmation MF.

I got another question here.

Now my Handset dimension is something like below

Handset
   Apple
       (blank)
        iphone1
        iphone2
    samsung
         (blank)
         samsung1
         samsung2

I want to get rid of those blank values under each handset/hierarchy now.

Is that possible by any means ?

Why do you have blank members? What BusinessKey (MUN) do they have? Sounds like you have some missing data somewhere?

MF.

[sunosoft]

Yeah there are some null values in database itself.

In order to create multiple hierarchy I followed below approach.

Created multiple data items like Apple, Samsung , Blackberry etc using case statement for "Handset"(all brands are here) column.

And i dragged this data items under each hierarchy to achieve what I wanted. Then to I replaced null values by 'blank' keyword. However user does not look much happy to have this blank under Apple or Samsung etc. So I want to get rid of this, if its possible.

Along with individual groups like Apple, Samsung I have one hierarchy called Handset(direct column from DB), here also null values are there. For users here null does not mean nothing, they have some importance(some business logic) so they are happy to use blank field from this All handset hierarchy, but they dont want blank under each group. 

[MFGF]

Yeah there are some null values in database itself.

In order to create multiple hierarchy I followed below approach.

Created multiple data items like Apple, Samsung , Blackberry etc using case statement for "Handset"(all brands are here) column.

And i dragged this data items under each hierarchy to achieve what I wanted. Then to I replaced null values by 'blank' keyword. However user does not look much happy to have this blank under Apple or Samsung etc. So I want to get rid of this, if its possible.

Wouldn't you just filter the underlying query subject to exclude those rows?

MF.

[sunosoft]

This is what I thought initially.

Now if I put  multiple filter like [Apple]<>NULL, [Samsung]<>NULL, then its not returning any data. Sorry might be bit basic question because recently I jumped into development. Can we have OR operator here somewhere in filter ?

Can I put some filter like [Apple]<>NULL OR [Samsung]<>NULL ?

[bus_pass_man]

Based on your answers to the questions I might have more questions.

1.  You should be aware that if you filter your dimension you'll be filtering out of the query any records in your fact table whose keys matches the keys of the records that you've filtered in your dimension.  Since this will affect the values returned in your queries, you might not want to do that.

2.  Before you added the second level, did you have members whose captions were blank? 

2a.  Just to confirm, the only change you did was to add the second hierarchy and add the second level and your original level into that new hierarchy.

3. What information is captured in those records where the nulls exist for the column that you are using for the member caption of your handset level?  Evidently they are not handsets.  Are they stuff like service contracts and accessories?  Is there any reason why you don't your model to include whatever these things represent, especially considering the filtering-out-of-data question of point 1?  You might want to consider some way of developing a caption to indicate what these things represent.

4.  It's unclear what you mean by ' [Apple]<>NULL, [Samsung]<>NULL'  Are Samsung and apple the names of query items?  A proper filter expression would be more like [namespace].[query subject].[query item which is being used as your member caption] <> null.   You might need the expression to be ... is not null, using that operator rather than the <> operator.   

The comma isn't valid for filter expressions.  It would be valid to have an expression such as [object reference] <> null or [object reference1] is pink which would return all the records for where the value in the object reference column is not null and all the records where the values for the object reference1 column are pink.  This would mean that some records where the value for object reference was null will be returned because their value for the object reference1 column is pink.   It's just like if you were manually creating a filter in the where clause of your sql statement.  You would say where {column} is not null or something similar.

5.  You should also be aware that multiple hierarchies are fun except when you try to use them in the same report in certain cases.  I don't remember the list but I think one of them is when they are in opposite axes in the report.

6.  If you're new to the field then I strongly recommend that you take the Cognos education and read stuff by people like Kimball so you can have a better grasp on the material.   One advantage of that is to be able to better define the problems that you are encountering.

Hope that helps.

[sunosoft]

Thanks a lot for your suggestions and response.

I ended up with below steps but I got some other thing to resolve.

1.I created query subject for each handset model(Apple,samsung), with only one data item "Handset"(containing all models)
2.I put filter in each query subject in order to filter data related to models like Apple, Samsung etch. Normal filter like Handset '%APPLE%' which gave me all apple handsets. Similarly for other models also.

3.Then I created a regular dimension. First I pulled Apple query item from query subject created in step2. Then added new hierarchy for my another model Samsung. I added level to that new hierarchy and pulled my samsung query item from samsung query subject into this second hierarchy. I renamed it to as samsung.

4.So now I have a dimension called Handset which contains hirarchies like Apple, samsung, Asus etc.

5.When I go to crosstab and pull one of my hirarchies(e.g. Apple) and pull related measure then I am getting correct total for "Apple All" level which comes by default in dimension.

6. This point is related to my concern now. Now I already have Apple in my cross tab, now if I pull samsung in the same cross tab just below the apple then something wrong is happening. I am not getting correct total for Apple All and samsung ALL.
Sometimes its showing me the same value as Apple All for Samsung All as well. And sometimes vice-versa.

I am not sure how to deal with this now.

   

[MFGF]

Thanks a lot for your suggestions and response.

I ended up with below steps but I got some other thing to resolve.

1.I created query subject for each handset model(Apple,samsung), with only one data item "Handset"(containing all models)
2.I put filter in each query subject in order to filter data related to models like Apple, Samsung etch. Normal filter like Handset '%APPLE%' which gave me all apple handsets. Similarly for other models also.

3.Then I created a regular dimension. First I pulled Apple query item from query subject created in step2. Then added new hierarchy for my another model Samsung. I added level to that new hierarchy and pulled my samsung query item from samsung query subject into this second hierarchy. I renamed it to as samsung.

4.So now I have a dimension called Handset which contains hirarchies like Apple, samsung, Asus etc.

5.When I go to crosstab and pull one of my hirarchies(e.g. Apple) and pull related measure then I am getting correct total for "Apple All" level which comes by default in dimension.

6. This point is related to my concern now. Now I already have Apple in my cross tab, now if I pull samsung in the same cross tab just below the apple then something wrong is happening. I am not getting correct total for Apple All and samsung ALL.
Sometimes its showing me the same value as Apple All for Samsung All as well. And sometimes vice-versa.

I am not sure how to deal with this now.

Hi,

Personally I think this is entirely the wrong approach. If I was in your position I would create a single hierarchy with two levels - one level for manufacturer, and one below that for handset. The regular dimension would be based on a single query subject with a derived item for manufacturer.

Cheers!

MF.

[sunosoft]

:) thanks. I think, I should stop working on development stuff :)

Anyways..so you mean I should create a separate regular dimension for each manufacturer, if Yes I tried this but still I faced the aggregation issue which I mentioned in above point 6.

[sunosoft]

Yes. Thanks a lot for your suggestions. I managed to do it. One level as manufacturer and other level as handset and my aggregation problem solved. Now my dimension is having only hierarchy and one query subject :)

Now some other strange behavior I am observing.

When I pull Apple(in rows of crosstab) first and measure, then total fine for Apple handsets. After this when i pull Samsung just below Apple samsung sum is also fine, no issues..this was my previous problem.

However now if I select Apple, Samsung simultaneously and pull it into corsstab. Then after putting measure value I am not getting exact total which I got when i pull them individually. Numbers are close but not exact.

Multi selecting is making issue, when one by one pulled correct total.

[MFGF]

:) thanks. I think, I should stop working on development stuff :)

Anyways..so you mean I should create a separate regular dimension for each manufacturer, if Yes I tried this but still I faced the aggregation issue which I mentioned in above point 6.

No. Absolutely not. Have one regular dimension with one hierarchy having two levels - Manufacturer and Handset.

MF.

[MFGF]

Yes. Thanks a lot for your suggestions. I managed to do it. One level as manufacturer and other level as handset and my aggregation problem solved. Now my dimension is having only hierarchy and one query subject :)

Now some other strange behavior I am observing.

When I pull Apple(in rows of crosstab) first and measure, then total fine for Apple handsets. After this when i pull Samsung just below Apple samsung sum is also fine, no issues..this was my previous problem.

However now if I select Apple, Samsung simultaneously and pull it into corsstab. Then after putting measure value I am not getting exact total which I got when i pull them individually. Numbers are close but not exact.

Multi selecting is making issue, when one by one pulled correct total.

What happens if you build a report off the underlying query subject? If you bring in the Manufacturer item and the Handset item then your measure, do you get correct or incorrect measure values displayed?

How is the Manufacturer item defined? Do the values look correct when you test?

You're not using any detail filters in your report, are you? These are fabulous at messing up dimensional reports...

MF.

[sunosoft]

First of all thanks a lot for making me confident.

Regarding my issue, Yeah manufacturer and handset mapping is correct.

First test I did here is i checked with UAT data, no issue no discrepancy for UAT database no matter how i pull members(individually,simultaneously). Looks like its some data issue related to our production database. I did not get a chance yet to test this issue directly with underlying query subject. Yeah and I observed manufacturer to handset mapping is working correctly no discrepancy there.

any guess if this can happen if there are any duplicate records at database level ?

When I pull just one of items(samsung) in workspace advance(crosstab) then it will give me full listing of members(handsets) and last row with total. And when i pull multiple items like samsung, apple, zte then it will just show me three items in my cross tab ..samsung, apple, zte...and those will give me total for respective handsets..like total for samsung, total for apple,......in this case if my total is 50 for samsung. And now if i click on samsung to expand it or to see in detail then my total is changing to 68.And even it will be 68 when I pull single item Samsung. I am trying to find the difference between way of summation in these two cases.

[sunosoft]

Hi MF,

Further to this ....

I found what exactly is happening. Let me clear one thing,I am modifying the existing model developed by some other. Measure value is like count(distinct (some value)).

Now if the data is like below.

handset grp   handset            measurevalue(some service number )
Apple             Apple1               4562
Apple             Apple2               4562
Apple             Apple3               4589
Apple             Apple1               1234

In this case if we take count distinct of service number for Apple(handset grp/manufacturer/derived item), it will be 3.

Now if I click on Apple and expand it to detail level, here count distinct of service number for each handset. it will be like below.

Handset                Count Distinct
Apple1                   2
Apple2                   1
Apple3                   1
------------------------
Apple All                4   

This is where the data level issue it seems to be. But do we have any way to tackle with this at cognos level. I am not sure if its like we need to make changes at manufacturer level to make it count with the combination of handset and service number or something else ?  I need to handle this through FM only as its kind of self service environment here and users do not want to do any calculations in report. Can you please help me on this ?

[sunosoft]

any thoughts please ?