cancel
Showing results for 
Search instead for 
Did you mean: 

Problem with data repeated in universe

Former Member
0 Kudos

Hi everyone, i hope that all will be good. I'm here with a question about the universes on information design tool.

I did it my universe with an Excel file. I created all the relations and added the primaries keys to the tables. All the process is fine. The problem is when i want run a query. There are not any loops or problems of integrity. I selected all the fields that i want but is bringing some values repeated and in some cases filtered some values on the query.

What can you recommend me? Thank you very much for the help.

I will let you the related files and the link to see the excel data. Regards

alias.html - Google Drive

BASE.xlsx - Google Drive

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

When you say filtering, is it because of your cardinalities - are they set as inner join when they should be outer joins for example?

There are also no measures in your query, which makes it more awkward to figure out what is wrong - it may be that there is a fan trap, but we don't know as we don't know how the data should be related.

Former Member
0 Kudos

Thanks for your answer Mark. I forgot put only two measures. NETWR and WRBTR. Let me try changing that and i will tell you.

Former Member
0 Kudos

Well Mark, I did the test and  result is the same. I will explain a part of the process. There is a value that i need to transfrom it taking the following condition:

blart=kz; augbl≠0; umskz=A; and vbeln≠0 to show the value of advance payment but the related values always are the same.


The values in the query that i posted are the necessary to build the report. Do you need another explanation about the query? Must i do another changes in the Excel file, dimensions or measures? I don't know if i answer your questions.

Regards

Answers (5)

Answers (5)

Former Member
0 Kudos

Hi Juan,

Looking at the table names, I see that you are trying to query SAP data directly. I could see that you you are trying to bring FINANCIAL_DOCUMENT, SALES_DOC_HEADER and PURCH_DOCUMENT together to create a report.

Do you understand the relationship between these tables (whether they are 1 to many or many to many)? This would help in identifying the root cause of the issue that you face.

Former Member
0 Kudos

Hi Mohanraj,

Not so well, I'm only have the basic knowledge. Do you have a document or link to read about that tables?

Thank you very much.

0 Kudos

Hi Juan,

I think reorganizing of tables, joins and their cardinalities may help your. I have looked into the tables and would propose you with the joins and cardinality as below. The result of the query should come proper now. Hope this helps.

Regards,

Rajesh

Former Member
0 Kudos

Hi Rajesh,

Thanks for your suggestion. I did it but the values come more filtered without the alias table (BLART columm). And with the alias table the result is the same that i posted.

Do you have another idea?

Thank you very much.

0 Kudos

Hi Juan,

Thanks for your response, I did tried to work on it during the weekend and below are my observations:

a) I think the UNX based our of MS-Excel is giving some issue and hence restricted result, I made an MS-Access .mdb file and it gave better result. Eg. All KUNNR values I could only see when my source is .mdb with excel source it gave always null value. Please validate, if same is with you.

b) The relationship between the tables are as follows:

    between table BKPF and BSEG on field BELNR is << one-to-many >>,

    I have also highlighted the Key fields as PK in each table

    Hence, the table joins and cardinality will look as below.

Former Member
0 Kudos

Hi Rajesh,

Thank you very much for your answer. I followed your steps but the results are the same. In answer to your suggestions:

a) Did you do some configuration to the mdb file? In my case, i  did set the HZUON column in long text because gave me an error. Also, i have office 2013. I saved the file in mdb and accdb but the results in the KUNNR was null. Could be my version?

b) I did set the same relations and cardinality that you and didn't works.

Can you send me the parameters of your query please to check my failure?

Thanks again Rajesh.

Regards.

amitrathi239
Active Contributor
0 Kudos

what do you mean by repeated values? are you getting records duplicate in the  rows?

take one or two values and find out how values are in other tables..

Former Member
0 Kudos

Hi Amit. I put the files showing the example. I did the test with some values and always is the same.

Former Member
0 Kudos

Hi Juan

I think what it needs is some contexts the query may be repeated values because of the loops

Check this video or search by Aliases and contexts in IDT

Resolve a loop by manually inserting contexts: Information design tool 4.x - YouTube

Thanks,

Krishna

Former Member
0 Kudos

There aren't any loops if you look at the tables.

Former Member
0 Kudos

Hi krishna. I tried iserting context and the result is the same.

Thank you very much.

TammyPowlas
Active Contributor
0 Kudos

Hello Juan - I moved this to the Semantic Layer space for faster response

- Tammy

SCN moderator