Cleaning up Drillthrough DB Tables

Every time you run a plan which contains a Profiling Step with enabled Drill-through functionality, a table will be created in the specified database. These tables will have a PROF_ prefix in their names unless it has been specified in the Profiling Step Properties. These tables will also get a _0_x suffix, where x stands for the number of inputs used in the Profiling Step, beginning with 0. For example. you might see a table with the following name: TEST.PROF_34C76810_0_1, which means that this table is used for the second input of the corresponding Profile. You will also see the same table with the _0_0 suffix, which corresponds to the first input used in the profile. One more table that is always created is called PROF_META. This table stores the connections between profiles and drill-through tables in the database.

Drill-through tables accumulate and are not automatically cleaned up when profiles are deleted, which, for some users, might lead to a lack of space.

To clean up unused tables, right-click the used database and select Drop Obsolete Profiles.... You will see the following screen:

Dropping Obsolete Profiles

You will see the profiles which no longer exist, the user that created them, the date of creation, and the corresponding table in the database, which will be dropped.

If you share the database schema with others, the dialog window above will treat the tables created by others as obsolete since you will not have the corresponding profiles in your DQ project. Make sure to drop only your own obsolete profiles.

As you can see in the figure above, the profile eeg1.profile has four tables associated with it (_0_(0...3) suffix), which will all be deleted. The other two profiles have one table each in the database (_0_0 suffix). Select the tables you want to drop (click Check All to select all) a click OK.