Issue with DMR model based cross tab

[mpervez]

Hi All,

I need help to resolve issue with DMR Model based cross tab
================================================================================
Measure   Null           2010     2011         2012           2013       2014             2015                      GDate
================================================================================
Entity1   84968   1062    14566       13005          5593                832               25                       120050
----------------------------------------------------------------------------------------------------------------------------------------------
Entity2   303                             10          111              24                  22                                                 470
----------------------------------------------------------------------------------------------------------------------------------------------
Entity6   4049                                  12             712         283                6                                     5062
------------------------------------------------------------------------------------------------------------------------------------------------
Entity3   14402   127               3087         4632           2346         293                5                                   24892
-----------------------------------------------------------------------------------------------------------------------------------------------
Entity4   3408                                  21            525                 182                7                                     4143
----------------------------------------------------------------------------------------------------------------------------------------------------
Entity5   16662   121                4212        4156           2261         539              14                                   27965
---------------------------------------------------------------------------------------------------------------------------------------------------

The Null column in the above table is missing in cross tab and result I got as follows

================================================================================
Measure    2010     2011         2012           2013       2014             2015                      GDate
================================================================================
Entity1   1062    14566       13005          5593                832               25                       120050
----------------------------------------------------------------------------------------------------------------------------------------------
Entity2                          10          111              24                  22                                                 470
----------------------------------------------------------------------------------------------------------------------------------------------
Entity6                                  12             712         283                6                                     5062
------------------------------------------------------------------------------------------------------------------------------------------------
Entity3   127               3087         4632           2346         293                5                                   24892
-----------------------------------------------------------------------------------------------------------------------------------------------
Entity4                               21            525                 182                7                                     4143
----------------------------------------------------------------------------------------------------------------------------------------------------
Entity5   121                4212        4156           2261         539              14                                   27965
---------------------------------------------------------------------------------------------------------------------------------------------------

Please find the attached document

[bdbits]

It might be that the FM package data layer was modeled only using inner joins. If so and the nulls would be generated on the inside of an outer join, those rows would be dropped from the result set. I suspect this might be the case because your query (which I assume you created by hand) has an outer join to the date.

[bus_pass_man]

Yeah, looking at the model is definitely the place to start.  I'm not sure you ought to think about outer joins necessarily.

You should also look at handling for nulls in your time dimension or in its underlying query subjects.

You've shown us a relational query but it isn't clear what's its provenance.  Is that a query that you've written?  Is it something generated by testing an object (or objects) in your model?

What are the entities that you are using as the columns of your crosstab?  Are they members? Are they from an attribute?  Are you using a level?   Do you have a 'Null' member in your time dimension?  If you are using an attribute, is there a 'null' in the result set when you test it?  ( for example, if you don't have access to the model in FM, by putting the attribute into a query all by itself and running the query.)

Also, did you really get the same totals for the query as for your expected results (the 182582 and you might get a rocket from MFGF if that data is real.)

My guess is that your model isn't handing for the nulls.   There's several expressions which you can use .  The most elegant method is the coalesce function.  (here's a video about it https://www.youtube.com/watch?v=T427I-i4hXo  )



Now, the dirty data question.  It is unfortunate that that phrase doesn't necessarily return a safe-for-work search engine result; try 'data integrity' and 'ETL'.

Is it truly truly truly the case that you have facts which are not associated with any time period whatsoever?  That sounds rather dubious.  If pressed, could you explain to the business guys who would use the report which you are creating why it is the case?   

[mpervez]

Hi bdbits and bus_pass_man,



@bdbits : FM package data layer was modeled  using outer joins between Date table and Enterprise Table

@bus_pass_man :
1. I have two design models (Relational and DMR) based on same DB view and
you are right the query is based on Relational model.(please find the attached pic)


2. Entities that I am using as the columns of  crosstab are as follows
EnterpriseTypeEnglishName,
GregorianYear
and
count_Big(EnterpriseCode)

3. I don't have Null Member in my time dimension

Note : I have tested same query generated with (DMR) in report studio with List object and its working fine. I mean it display the values of the null column in cross tab

[bus_pass_man]

Greetings prisoners of gravity

you are right the query is based on Relational model.

So this is the sql which is generated and not something which you've written?

Ok I've looked at your diagram. (just as an aside it doesn't look like it came from FM or, if it originated there, has been subject to considerable redaction so there is another variable to control for; that being the possible discrepancy between your diagram and what is actually modeled. ) Just to confirm, Enterprise type is on the many end of its relationship to enterprise (i.e. you haven't accidentally indicated incorrectly)?  This is a wee problem as your sql wants Enterprise.enterpriseCode to be the fact.  The query engine isn't going to handle that very well.  I was assuming that the cardinalities of the relationships was such that the fact table was on the many end of the relationships between it and its dimensions.¹

Getting back to the original problem, given what you've been told in this thread and what (presumably) you've learnt² on your Cognos training and your in-general training, do you understand why the null member isn't generated?  Do you understand why you should not be expecting a null member to be generated? Do you understand why there's a modeling problem and do you know what you need to do to fix it?

OK, so in the relational query world you can write SQL with outer joins and get results back like what you showed in your word doc, where some rows were returned with NULL for Time.

The problem, and this gets to a fairly fundamental concept, is that nulls are nulls and don't exist because they are null. When you get back a null in a query it means that there isn't any entity for that particular element in the tuple.  There's no there there.

Usually, when I've needed to deal with situations where nulls could be encountered,  I would build into the model handing so that if a null is encountered then a substitute thing is returned.   But handing facts where there isn't an associated entity in the dimension is something else.

What you are trying to say is for whatever the fact is, there are values which are associated with a dimension but not with any entity within the dimension.  This is probably not what you want to say and probably not what is actually the case.

While the nulls could be an artifact of the messed up modeling, personally I think you have dirty data, which is a problem, and ought to be fixed. I can't think of a case where any fact could exist but not be associated with a time period.

Almost every fact eventually, to one degree of indirection or another, gets incorporated into the determination of the enterprise's financial statements and the internal managerial accounting statements.

The accountants would want to be able to assign everything to some period. The tax people definitely would like that so they can figure out how much tax you guys owe to them.

The people who view whatever reports that are created from your model would be curious about why so much in the nulls and what the bloody hell is a null anyway and this will especially be the case as the values are such a large percentage of whatever the fact is.

So, no matter what, the fact that you are getting nulls even if there's no modeling problems is a problem as it is a non-nonsensical thing.

Sorry if this sounds like too much of the ranting that it is.  I'll blame the spell checker for putting me into a bad mood.  Its always fun to blame the software.

Good luck. 


1.  When I say dimension I mean it in the broad relational sense -- things which give context to facts -- not the related but subsidiary concept of the thing with hierarchies, levels, and members.

2.  Bloody Yankee spell checker!  Learnt is a perfectly good word!  http://www.oxforddictionaries.com/words/learnt-vs-learned