As stated in the subject: Selecting two dimensions without fact shows "no data available". However, if I drag one of the measures of the fact table to the canvas it does show results.
What am I doing wrong?
This must be something that is wrong in Framework Manager.
Take a look at the SQL generated in both scenario's. If one scenario fetches no data and the other one does, then it would indicate different paths are choosen (for some reason, which would lead to examining the model itself)
That is right. I've had this before.
But actually it is strange. In my presentation layer I created a namespace containing a reference to the several components for this star. Why does Cognos then still search for paths outside this namespace?
I think there was a way to force Cognos to use a specific path. Do you know of a way to do that?
Found my initial post:
http://www.cognoise.com/community/index.php/topic,8692.msg28351.html#msg28351
Actually it is using the facts in alphabetical order.
I'll tell the users that reporting when only using two conformed dimensions without facts is NOT the best practice!
What is the point in reporting on dimensions only in the first place? By the way, did you check the model for redundant joins? Copying query subjects will have the side effect of also duplicating joins associated with the query subject..
I agree that reporting on dimensions only is pretty much useless. However, some of our users tend to do that...or at least start their report or query like that.
When I copy query subjects I always choose the option not to take along the joins.
Just out of curiosity, and I agree with blom about the point behind "factless" reporting, what is the join between the 2 dimensional query subjects? If no join exists, that could be a problem. Alternatively, if a join between the two exists but is only an inner join then the problem mentioned by blom again comes into the picture - values getting dropped because there isn't something common between the 2 tables and this islikely because unlike a snowflake, a proper star schema isn't really designed to have a relationship between 2 purely dimensional tables.
For example you can have a Customer dimension and a Contract dimension. Every Customer can hold one or more Contracts. You can join these dimensions in a sales star around a Sales fact.
The user may want to pull up just the Customer dimension and Contract dimension to see which Customers have which Contract. (Keeping in mind that only those records that have a fact associated will be shown.)
So, the join in a model like this should always exist through the fact table.
I do agree, that it would be better to always include a fact.
That does not make a lot of sense. Surely a lot of other information should be stored somewhere regarding the customer - contract relationship (start / enddate ; discount percentages etc) I would expect that such seperate tables exist in the source. This is not information that would be stored in the fact itself. Even if it does, it would make 'new' customer - contract relationships undetectable (as no transaction exists in the fact)
Maybe this is an opportunity for a factless fact table. Essentially a bunch of keys on the fact table with perhaps an artificial fact column containing 1 that can be aggregated representing a count of things.
If there is value in understanding the connection between customers and contracts (with or without an actual sale occurring) then this approach might be something worth reading about. Here is a Kimball article about it.
http://www.ralphkimball.com/html/articles_search/articles1996/9609d05.html
This is starting to get an interesting conceptual discussion! ;)
Quote from: blom0344 on 22 Dec 2010 07:38:33 AM
That does not make a lot of sense. Surely a lot of other information should be stored somewhere regarding the customer - contract relationship (start / enddate ; discount percentages etc) I would expect that such seperate tables exist in the source. This is not information that would be stored in the fact itself. Even if it does, it would make 'new' customer - contract relationships undetectable (as no transaction exists in the fact)
Definately this information is available in the source. Here on the project somebody suggested that it should be ok to run a query for only customers and contracts, but no reports like that should be created. Reports like that typically are being made in the source. (I agree with that statement)
In our case the user is very much aware of the fact that they will not see new customer - contract relationships.
Quote from: Lynn on 22 Dec 2010 08:14:49 AM
Maybe this is an opportunity for a factless fact table. Essentially a bunch of keys on the fact table with perhaps an artificial fact column containing 1 that can be aggregated representing a count of things.
If there is value in understanding the connection between customers and contracts (with or without an actual sale occurring) then this approach might be something worth reading about. Here is a Kimball article about it.
http://www.ralphkimball.com/html/articles_search/articles1996/9609d05.html
That is a very good suggestion! So far I think we will educate the user not to use the factless querying too often, but if they want to,...this could be the best way!
HI,
There is one point in producing a factless report, it's to show the availability, the content of a dimension. If I take the example give by Plantje, it will be for information purpose only to have a 'LIST' of contact by customer without any fact related. Supposed you want to send by mail publicity to those contacts, a list with just the information about the contact could be done.
In the project I work now we produced some report without fact,10 to be exact, like new customer in the business for the month or reseller who change address between two month. That kind of report for us it's just informational and have nothing to do with the rest of the work we do.
Thanks.