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.
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?
Greetings prisoners of gravity
Quoteyou 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.
1Getting back to the original problem, given what you've been told in this thread and what (presumably) you've learnt
2 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