CODEWITHSUNDEEP
sqloracle-databaseplsqlviewtableau-desktop
LearnerCode
LearnerCode

Reputation: 179

Tableau - How to replicated calculated dimension in Oracle Database

I have a very large Tableau workbook, which takes a very long time to load. I have determined that this is due to some very large calculated fields which I want to replace to improve the load time.

My plan is to have this calculated field replicated on the database level however, I am unsure what the best approach is and was hoping someone can help me.

The calculated field is essentially a large mapping table, which roles up an area (say area 2) to a higher level named area 1. This is several lines of code and unfortunately I am not able share this due to work.

e.g.

IF [area_2] = "abc" THEN [area_2]
ELIF [area_2"] = "def" THEN "xyz"
ELIF [area_2"] = "ghi" THEN "mno"
....
....
END

My initial idea was to create a view on top of the database table these attributes come from with a IF Statement. However I have come to understand the views cannot be created with PL/SQL If statements. I have tried to begin learning PL/SQL elements (i.e. Procedures, Packages, Functions), but finding it hard to determine what is the correct option I should go for.

Any guidance will be greatly appreciated. Thanks

Upvotes: 0

Views: 62

Answers (1)

Kauan Polydoro
Kauan Polydoro

Reputation: 108

I think you have three ways to solve your problem

If a record always have the same value after the calculation

  1. You can store the calculation value: add to your table columns to store the results of the calculation and create a TRIGGER in your table that calculate the values after every insert.

  2. Create an Materialized View: If your table don't have a high frequency of inserts, you can create a MATERIALIZED VIEW, that store the view result after every insert.

Materialized View Docs: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6002.htm

If a record can change the value after the calculation

  1. Create a view using CASE WHEN or DECODE if the calculations are simple as your example, if they're not, use a FUNCTION that return the value passing your value and params to calculate the value.

Upvotes: 0

Related Questions