We are looking at simplifying our CT Model, as part of this we would like to identify and remove any redundant tables. Has anyone been through this process themselves and is there an easy way of identifying redundant tables?  

Hi there @Simon Bell, this is something that we addressed when we upgraded from R11 to R12... In my experience, unfortunately, I found that there is no easy way that we're aware of to be able to accomplish this.

What we did was go through each and every table transform and looked at the formulas that use functions that reference other tables (e.g. lookup, lookupex, tablematch) and took note of these. I used ctrl+f to find such formulas in my browser to highlight these as I went through our table columns. I started this investigation from Finance Layer > IT Layer > Business Layer - wherever we had master data tables.

We did the same simplification during the R11 to R12 upgrade.  Since in R12 we can perform the transformation using steps after upload the raw file, we were able to remove many transform tables in R12.  Another method we used to remove redundant table in R12 was reviewing the staging tables used in R11, and see if any of them can be removed by using data relationship and allocate directly from one master table to another master table without the staging table.  As Kyle mentioned above, we also inventoried all the tables in excel documenting formula using LookupEx and Lookup to see if we can combine some lookup tables.

We just went through an exercise to do this.  We had merged multiple models and found ourselves with quite a bit of redundancy/obsolescence.  Unfortunately it was a labor intensive process and required a thorough regression testing cycle.  In the end though, it was effort well spent as we were able to delete almost 20 tables eliminating a lot of confusion.

I'll use this space as a shameless plug for expanded cross reference/lookup capabilities to make exercises like this easier..

Table Cross Reference 

Hi Simon,

I completely agree that it is a manual process, but it's worth it at the end. In addition to identifying Redundant tables, I would also advise you to identify Redundant formulas in tables which you plan to retain, going ahead. As a TBMA it becomes much more easier to maintain the system and it's also helpful as it would reduce time taken to troubleshoot the configuration.

Varghese

Hi All

Thanks for all your replies, i had a feeling it was going to be a labour intensive process but i just thought i would ask the question

@@Simon Bell It is indeed a manual process. Two things can help a bit:

1. Extract all your raw tables using Data Quality reports and review data freshness cycles to see if any datasets are not used.

2. Also, the table bold feature to show dependent tables might help in identifying some of the redundant tables.

Hope this helps.

Poonam

Hi Poonam,

In R11, we had this feature where when we click on the table, we could see the Table usages, in the summary tab. Is that feature going to be available in R12 ?

Varghese

Hey Varghese George, it is on planned list but actual date is not confirmed yet. See below.

[Planned] Ability to see table usage