We get FHIR bundles from vendor, mostly patient, encounter, observation, flag and a few other resources (10 total). We have an option to store resources as json values or we can come up with a process to normalize all the nested structures into separate tables. We are going to use traditional BI tools to do some analytics and build some dashboards and these tools do not support json natively. Should we do former or latter and what is the best/easiest way to build/generate these normalized tables programmatically?
Ultimately how you decide to store these is not part of the scope of FHIR, and any answer you get on here is going to be one person's opinion. You need to figure out what method makes the most sense for the product/business you're building.
Here are some first principles that may help you:
Different vendors will send different FHIR at you. Fields may be missing, different code systems may be used.
FHIR extensions contain a lot of valuable information and the JSON representation is an Entity Attribute Value. EAV is anti-pattern for relational databases.
FHIR versions will change overtime - fields will be added and have their names changed, and new extensions will be relevant.
As far as your second question about generating the tables - I think that you will be best served by designing the data model you need, and mapping the FHIR data to it. That said there are a number of open source FHIR implementations you can study for inspiration.