2 of 2 people found this helpful
This is a difficult question to answer since there are a lot of variables to consider. It really depends on the use-case for this database. It can be difficult balancing the needs of quickly and efficiently storing the data in comparison to then quickly and clearly querying it for reporting. Fortunately, you have the rare opportunity to design a database so it provides clean data for the viewer.
I can, in no way, provide effective recommendations for database architecture... but I can try to give some general advice.
- If performance is the focus, consider creating a healthy database which can perform the hard work for us. This means having a normalized database which would also perform common calculations so Tableau doesn't have to do it in addition to more complex calculations and queries.
- There is no preferred method for creating efficient extracts or accessing the data source. You are 100% correct that creating an extract which filters out unnecessary columns is the best approach. Tableau will only read and query the data, so indices won't much of a concern unless the database itself is writing a table for these records.
- Know when to leverage an extract and when to use the live connection. Each type of connection has its advantages which are outlined in the following Whitepaper:
Seriously, the above is a good read.
Overall, as long as you have a well-built, normalized database which has quick connection then you will be fine. Each subsequent report written in Tableau should be considered by each use case in order to find the best balance of performance and data freshness.
Apologies for my very delayed reply, Adam. This was very helpful information to have.
No worries, my friend. I hope the information was useful.