Number of Rows difference between CRM query and HANA view

[jyothirmayee_s]

Hi,

One of the user running WebI report created on Universe which is on HANA CV and during one of the testing process , we found out that number of rows between CRM query(SQL Query) (Which is source) is less when compared with HANA view.

I did checked the Staged table in HANA and it is also giving me matching results when compared with the source but not Analytical view or Calc view.

Just a filer was applied at ETL side on table but nothing at view side.

Little confused and lost why the result set has the discrepancy.

Any thoughts or advices are appreciated.

Thanks,

Jothi

[muthuram_shanmugavel2]

Hi,

As per my understanding, The Issue is,

Staging Table has less no of rows and

The Analytic / Calc view which is created on top of this staging table has more no of rows.

In Analytic / Calc view,

Have you joined this Staging Table to any other Table.

If Other Table has duplicate entries, Then your final view output would have more rows.

Example:

Staging Table has 3 rows.

Staging Table
ID	VALUE
1	100
2	200
3	300

Table_B has 3 rows and The Values (ID=1) are getting duplicated.

TABLE_B
ID	DESC
1	A
1	A1
2	B
3	C

In Calc View, Staging Table joined with TABLE_B by ID Column via Left Outer Join.

Then the Final Calc View Output will have 4 rows but Staging Table has 3 rows only.

CALC_VIEW_OP
ID	DESC	VALUE
1	A	100
1	A1	100
2	B	200
3	C	300

Regards,

Muthuram

[jyothirmayee_s]

Thank you for the reply,

Staging table is giving me right amount of records when compared with Analytical and Calculation view.

Ex: For one location:

• Analytical view is showing 80K rows
• Calculated view is giving 24K rows (Which is less)
• Staging table displays 33K rows which is correct with the CRM based SQL query result set.

There is a filter on one of the staging table and we can assume that might be the reason for the row discrepancy but not sure why the records are different in Analytical and Calculation views.

Btw, the joins are all referential.

Please share your thoughts.

Thanks,

Jothi

[muthuram_shanmugavel2]

In Joins,

Have you checked the columns which are used for Join Conditions have distinct records?

If you check my first reply, I gave one example that If your Join condition Column are having duplicate records, Then your Output will differ.

Regards,

Muthuram

[jyothirmayee_s]

Hi Muthuram,

Yes, there is referential join but dont see any distinct records condition on the join?.

I tried creating a new universe using those staging table using left outer joins and tested with real data in prod.

Do you suggest to revisit those joins on Analytical view?.

Please advice

Thanks,

Jothi

[muthuram_shanmugavel2]

Hi Jyothirmayee,

Have you checked Universe Output?

Is this results fine?

Regards,

Muthuram

[jyothirmayee_s]

Sorry for the late reply,

Yes, checked the universe result set too but as it gives only 5K rows then we wont be able to compare the result set.

Do you think only referential integrity is causing an issue at Analytical view/?.

Thanks,

Jothi

[Former Member]

Any restriction on result set in Universe?

[Former Member]

Hi Jyothi,

Any restriction on calculation views? check all the dependencies for the calculation view and check the analytical privileges if exist?

Regards

Subbarao M

[jyothirmayee_s]

Hi Subbarao,

Row limit is open and not set at universe level.

Can you guide me on finding these options (Restrictions, Dependencies) on Calc and Analytical views.

Thanks,

Jothi

[Former Member]

Hi Jyothi,

Just right click on the calculation view and select where used.. This will show list of objects i.e privileges... etc

From the list you can check the  privileges by double click on it.

Regards

Subbarao M

[jyothirmayee_s]

Thank you Subbarao,

I will check the options and post back the findings.

Thanks,

Jothi

[former_member656087]

Hi ,

Referential join acts as inner join if we take fields from both join tables which might lesser your result records.

if no field taken from right table then it will get all records from left table.

Do one thing copy view and change to left outer join and check the count. or take only left table fields in slq console and count  them.

I hope it helps you

Thanks,

Ramesh