List report returning too many rows

[anthus]

Hi all.  I've created a simple list report in Report Studio 8.4 from my DMR.  It has one fact and 2 dimensions (no filters or prompts).  When I add both dimensions to my list report (along with some measures from the fact) I get what seems to be an outer join between the two dimensions even though in my FM model there are inner joins between the fact and the 2 dimensions.  If I only use one dimension the report is correct.  When I use both dimensions I get a record for every location / store combo whether or not it exists in the database.  What am I doing wrong?

fact_inventory
dim_location
dim_store

Thanks....Stacy

[anthus]

I noticed that I only see these extra rows when I group and sort on a column.  If I just group the rows are correct.  If I do a combination of group and sort then I get all the extra rows.  Strange.

[blom0344]

Did you check the cardinality of the joins? If set incorrectly Cognos may perform unwanted splitting and restitching of (partial) queries..

[Sreeni P]

Did you check the cardinality of the joins? If set incorrectly Cognos may perform unwanted splitting and restitching of (partial) queries..

Hi ,

as Blom said it is perfectly fits in your scenario. and one more thing...how r u joining that DIM's and Fact's in Framework? hope Ur joining directly DIM-DIM, instead of joining  DIM-Fact-DIM

It's just my thought....let me know if at all works....

[tupac_rd]

I noticed that I only see these extra rows when I group and sort on a column.  If I just group the rows are correct.  If I do a combination of group and sort then I get all the extra rows.  Strange.

Not sure if you already tried this, but did you try to sort the dimension in the expression instead as in
http://www-01.ibm.com/support/docview.wss?rs=3442&context=SS9RTN&q1=%2bDMR&uid=swg21341719&loc=en_US&cs=utf-8&cc=us&lang=all

and then group..

[anthus]

In FM, I have 4 layers
     Presentation layer
     Dimensional layer
     Business layer
     Data layer

In the data layer, I keep it just as the source db is (note that this particular source db does not have any joins in it)

In the business layer, I add the joins and any business logic.  The cardinality is correct and is an inner joins are as follows:

                      inventory
                       /         \
                      /            \
                 stores       locations

     Each store has one or more inventory
     Each inventory has one and only one store

     Each location has one or more inventory
     Each inventory has one and only one location


In the dimensional layer, I create the DMR (source is a relational db) off the business layer.  Here I create all the virtual DIMs and FACTs (measures, hierarchies, etc).  There are no joins in this layer as it appears to be using the joins from the Business Layer.  There are, however, scope relationships.

In the presentation layer, I create the star schema groupings from the dimensional layer.  The presentation layer also contains a namespace for a relational structure from the business layer.

[anthus]

Not sure if you already tried this, but did you try to sort the dimension in the expression instead as in
http://www-01.ibm.com/support/docview.wss?rs=3442&context=SS9RTN&q1=%2bDMR&uid=swg21341719&loc=en_US&cs=utf-8&cc=us&lang=all

and then group..

I just tried the order() function from the IBM article and I still get all rows.  I did notice that when I convert my list to a crosstab and sort there that I do not get these extra rows.  But, I have a couple columns that are text and not number so I cannot use a crosstab for this report.  =(

[blom0344]

Anthus,

The quickest road to solution is to simply check the SQL generated when you use dataitems from all query subjects from the business layer.

If all joins are of the inner type than getting too many rows returned would indicate splitting and restitching.

I assume all facts originate from the inventory object?

[anthus]

I did check the sql from the both the business layer and the dimensional layer.  The counts are correct.  I even took the sql and pulled into SQL Navigator and checked counts there and they are fine.  I'm wondering if I'm doing something wrong in Report Studio with the dimensional reporting that is causing this.  I don't have any filters.  What is strange is that if I onlly group the items then the row count is correct.  But if I try to sort the groups then I get the extra rows.

Thanks...