DataSet Migration

Question & Answer
1

Hi Team ,

I am migration my Obiee rpd based model in Quicksight Dataset and i am getting the following the problems . Here is the explanation about the tables and it relationship .

Table 1; Revenue (Fact )
Table 2 : Customer (Dimension)
Table 3 : Address (Dimension)

Table explanation : Revenue is fact table and is joined with Customer table and for every 1 customer row in Customer table has multiple records in Revenue table . Address is joined with Customer table and every 1 customer there are mutilple address in address table.

ISSUE : when I create this design in OBIEE rpd and then create a report and in a report if i select 1 dimension from customer table and 1 measure from revenue table it gives me correct result as what is there in database . On the contrary when i do the same in QuickSight as it is getting joined with all 3 tables it gives me duplicate rows .
I can attach sample data for all 3 tables if it is required.
Thanks & Regards
Shiv

1 Like
2

Hi @vshivku1 - Yes please, please share sample data . This is an interesting observation.

@David_Wong - Any advise.

Regards - Sanjeeb

3

Attached is the data for the above tables .
Revenue & Customer joining condition : Cust_key = Cust_Key
Customer & Address Joining condition : Address_Key = Address_key
Thanks & Regards
Shiv

4

@Sanjeeb2022 How should I attach the files. Please suggest me .

Thanks & Regards
Shiv

5

There is an option to insert data as a table. Its a new feature in the settings.

image
image976×286 13.5 KB

Can you use that and upload sample data ( ensure you should not share PII info).

Regards - Sanjeeb

6

Hi @Sanjeeb2022 Apologies for the delay . Is there any other options available . Inserting table and inserting values is a tedius process . it has its own limitations. I can share on drive . There is PI information in this dataset.

7

Hi @vshivku1 - In that case, I will advise you to raise a ticket to AWS Customer support and show the use case so that they can analyze it and provide the solution w.r.t QuickSight.

To raise a ticket, please follow the link - Creating support cases and case management - AWS Support

In parallel, I am also trying to create some dummy data and replicate the usecase. We may not exactly build the back end query like OBIEE but the output of both reporting tool should be same.

Regards - Sanjeeb

8

Hi Team , @eperts @Naveed

I am migration my Obiee rpd based model in Quicksight Dataset and i am getting the following the problems . Here is the explanation about the tables and it relationship .

Table 1; Revenue (Fact )
Table 2 : Customer (Dimension)
Table 3 : Address (Dimension)

Table explanation : Revenue is fact table and is joined with Customer table and for every 1 customer row in Customer table has multiple records in Revenue table . Address is joined with Customer table and every 1 customer there are mutilple address in address table.

ISSUE : when I create this design in OBIEE rpd and then create a report and in a report if i select 1 dimension from customer table and 1 measure from revenue table it gives me correct result as what is there in database . On the contrary when i do the same in QuickSight as it is getting joined with all 3 tables it gives me duplicate rows .
I can attach sample data for all 3 tables if it is required.
Thanks & Regards
Shiv

9

Hi @vshivku1 - Couple of questions:

  1. With OBIEE are you querying the database live, ie direct query?
  2. With Quicksight are you querying the database live, or using SPICE?

Can you also confirm the relationship between Customer and Address? Is it many-to-many?

10

Hi @eperts ,

Yes I am querying the database live for OBIEE.
In QuickSight i have created a spice layer.

Relationship is One to many . 1 customer can have multiple address in address table.
I am happy to share the sample dataset .but i am not able to upload it here.
Thanks & Regards
Shiv