Problem statement :- We’re working with multiple datasets in QuickSight, For Example Dataset _1, Dataset_2, and Dataset_3, and we’re trying to build a unified view that shows key metrics for a single product SKU.
Explanation :- I’m working with three datasets in QuickSight: Dataset _1, Dataset_2, and Dataset_3. Each dataset contains different metrics related to our product SKUs. I’m trying to create a new analysis tab where I can select a single SKU and view all relevant metrics from these datasets in one place
Example (this is just a made-up example) :- if a user selects the SKU “Dell Inspiron 3291-3321”, we want a dashboard tab (or sheet) to display:
Dataset_1:
Unit Share: 2.1%
Dollar Share: 2.5%
Dataset_2:
Average Net Price: $398
Average Promotion Value: $43
Dataset_3:
Inventory Available: 528 units
We’d like to do:
A new tab where selecting a single SKU will show all of its available information across the different datasets as shown in above example
Is it possible to achieve this in QuickSight, especially considering the data comes from separate datasets?
What are the best practices for modeling the data (joins, SPICE, dataset unions,parameters or any other option at visual level or at dataset level etc.) to make this work smoothly?
Note :- We need solution for - If we have common identifier across all the datasets OR if we do not have common identifier across all the datasets
Any guidance or example approaches would be much appreciated!
Hi @Nikhilburhade,
There are a few things to consider with a scenario like this. The first thing is that you cannot build a single visual off additional datasets. You would either need to break up the information for each SKU over a couple visuals (one per dataset) or utilize a joined dataset.
By using parameters though, you can easily set a SKU within a parameter that controls every visual (even separate datasets) as long as they have a like field that has the same values.
If you have a field that represents your SKU’s in a dataset but it’s values are different (no common identifier), you could potentially build an ifelse statement to handle the filtering of your dataset…it would just depend on what that scenario is and what field options you do have in those datasets.
So I would suggest setting up a parameter for your SKU’s, linking to a dataset so that your default values are set, and then you can use that parameter to filter each visual.
Hi @Nikhilburhade , you could probably use UNION ALL to join your 3 queries and put them into a single dataset with a column “DataType” that could have one of these values: ‘Dataset_1’, ‘Dataset_2’, ‘Dataset_3’. Instead of using 3 dataset you could use 3 filters like this “DataType”=‘Dataset_1’, …_2’, …_3’ . You can fill the columns that are unapplicable for a current “DataType” with NULLs so that they do not affect results. But I believe that every subquery should have the column SKU. Don’t use the filter “DataType”=… in a dataset with consolidated data. Probably you will have to use calculate fields to put metrics from different columns into a unified column or several ones.
Marking @Brett 's response as the solution.
Will also add that if the SKU field is named exactly the same in all three datasets, you can also use the implicit filters with Apply cross-datasets option checked
