I am seeing the multiple rows in report (believe it is a cross product) which the query is running for long time and giving time out error. There isno issue when the sql is run in database. Please refer the snapshot attached (Issue1.jpg).We are taking all the
objects from Tasks class which are coming from 3 tables highlighted in a box and the cardinality is defined as follows.
There are no measure objects created in this class. Please let me know if any information is required on the same. I would like to know how i can resolve this issue and any response is highly appreciated.
While trying to figure out the root cause of issue happening in report this is the issue found in universe when the joins between tables were checked. I checked the automatic detection of cardinality and it showed me many-many. Please suggest me how i can resolve this issue at the universe level or if there is anything that can be done at database level.
Its recommended not to use the Automatic Cardinality Detect feature and you should set the cardinality according to what is defined in your data model. You should consult your data modeler to find out the correct cardinality.
In the universe when i use the automatic detection of cardinality it is showing as N:N between odf_ca_task_variance & CMN_LOOKUP_FLAT
I’ve tried to change the cardinality manually to 1:N but no luck
Since the relation ship by default is showing as N:N i would like to know the impact because of this i believe which ever tables connect to this in universe schema there will be multiple rows. Could any one please advice how to rectify this issue.
Unfortunately there is no data modeler available to contact except the people managing the database schema
Due to some Firewall related issues, I cannot access your document (As I am on a Client network).
You have got to see the relationship between tables, the real issue is not Cardinality (but is indeed important from a design perspective), it is in fact the way you join the tables and the data stored in them.
If you join two tables which are not having a proper Primary-Forgein key relationship (say, they are related by M:M relationship in actual database), then, you will get multiple rows since you don’t really have a clear 1:N or 1:1 mapping between them (As it will form a Cartesian Product).
Can you check whether you are really joining the correct columns? In case you are following Star Schema (which you ideally should), your Dimensions and Facts should be related by 1:N relationship.
Did you copy the (same) SQL generated by your reporting application (for the report you are getting multiple rows) and run it on database? Do you find duplicate records as well?
I can see there is no primary key and foreign key defined for tables at the database level.
However the issue i am facing is error appearing as time out for the objects of one class
I came to know that the issue is with the join condition which i have changed and the issue resolved with no errors appearing at the report.
Also i would like to know one more thing in XI R3.1 there is an option in object properties which is keys tab allows to define the keys for object. Is this only for improving the performance or if already there is no keys defined at database this will server the purpose
And if we define the keys is thing going to change any output at report level.
This is actually not good. Although you can still use the relationship (provided it is correct by your business logic) in your Universe, your database, for good performance, should have the relationship defined.
I suspected it would be a join issue, as long as your tables are not involved in M:M relationship, your reports (actually, the query) won’t produce Cartesian Product.
This is used to define Index Awareness. Simply put, it is the ability to use the Indexes, which are defined on database columns. This is typically used to improve data retrieval operations.
For more details, you could refer to SAP Designer Product Guide or Dave’s blog:
No, there will be no change in your data output. The only change will be in the SQL Query. For ex, instead of Region_Table.Region = “XYZ”, it will produce Region_Table.Region = 1. This is will work if the column is actually indexed.
Changing the cardinallity on a join in a universe does not change the generated query when reporting.
What I am missing in the whole post is the word ‘context’.
Cardinality-setting is only used for ‘information’ and ‘detecting contexts’.
So, are the joins between the 3 concerning tables in 1 context?
(BOB member since 2010-11-05)
(BOB member since 2008-08-28)
(BOB member since 2008-08-28)
(BOB member since 2006-02-06)