Is there a way to modify a PostgrSQL or a BigQuery dataset in such a way that the fields country and state automatically belong to a geo hierarchy (like country_state) without having to edit the Dataset in the QuickSight backend in order to add such hierarchy?
Hello @DanielH
I imagine you could create a comma separated list for the hierarchy but I don’t think QuickSight would recognize it as a geospatial but instead as a string.
What are you trying to accomplish/what’s the use case?
Hello @DanielH
Can you share more about your use case? Again, you could use a calculated field like concat to create the hierarchy but QuickSight won’t recognize it as geospatial.
We have a Dataset based on a PostgreSQL data source (or now BigQuery) which contains for places the field state (in our case called “Bundesland” or in other datasets “state”) and “country”. I want to show an area map of Germany or in some cases several European countries where each state is filled with a color which changes with the number of places in the respective state or something similar.
After we have created the dataset, usually we have to edit the data type of state/Bundesland to “state” and we have to add a geo hierarchy for country and state/Bundesland, otherwise area maps don’t work.
As we have to change this in QuickSight by hand, we have to ingest the whole huge dataset again, which takes time (and money).
I guess this might work automatically with US states, but not so with European countries and states.
Hey @DanielH
I would not expect you to have to recreate your hierarchy. If you are using PostgresSQL you should be able to use SPICE refreshes for new data ingestion which would keep the dataset hierarchy of country and state in tact as well as maintain it’s data type. Are you using SPICE and it’s still resetting your hierarchy/data types?
Also, this is what I am referring to in the data prep layer:
Hi @duncan,
Thanks for your feedback.
That’s how I change the datatype in the prep layer. But when we mage little changes to the data fields in the input, we usually have to do it again.
Do you happen to know if there is a way to name the field ‘state’ in a way that QuickSight always ingests it as the correct data type (geo state)? It seems to do this with ‘country’, but maybe ‘state’ is too ambiguous or the problem is that the states in the data in my case are German, not US.
Hey @DanielH
It would not surprise me if QuickSight didn’t recognize German states like it does US states. There have been a few issue with global geospatial data types.
Also, to doublecheck, have you tried the steps in the video in this topic (Kind of hard to see but you can open the video in another tab in your browser to make it bigger):
If you’ve tried this/it doesn’t work with a refresh then I can mark this topic as a feature request for the QuickSight team.
Thanks. The thing is, I would prefer if the field containing the states - which has now the name ‘state’ - would always automatically get assigned the data type State - without anybody having to edit it in QuickSight manually for the dataset.
That’s because typically different people in our organisation create the dataset and later create the visuals and notice when the data type is incorrect. It’s annoying if the second person has to refresh the whole dataset when correcting the data type.
However, the hierarchy might also be needed (country - state) for area maps, Im not sure. There is probably no way to add the hierarchy ‘automatically’.
Hey @DanielH
My main point here is that if you follow those steps above then every time you refresh this as a SPICE dataset it should maintain the data type.
If you are using this as a SPICE dataset and after updating the state hierarchy/data type and completing a scheduled refresh and the data type does not stay as geospatial data type then you should create an AWS support ticket. Here are the steps to open a support case.
1 Like