How to find unused rows in a dimension table

Question

I have a dimension table in my database that has grown too large. With that I mean that is has too many records - over a million - because it grew at the same pace as the linked facts. This is mostly due to a bad design, and I'm trying to clean it up.

One of the things I try to do is to remove dimension records which are no longer used. The fact tables are regularly maintained and old snapshots are removed. Because the dimensions were not maintained like that, there are many rows in the table whose primary key value no longer appears in any of the linked fact tables anymore. All the fact tables have foreign key constraints.

Is there a way to locate table rows whose primary key value no longer appears in any of the tables which are linked with a foreign key constraint?

I tried writing a script to track this. Basically this:

select key from dimension 
where not exists (select 1 from fact1 where fk = pk) 
and not exists (select 1 from fact2 where fk = pk) 
and not exists (select 1 from fact3 where fk = pk)

But with a lot of linked tables this query dies after some time - at least, my management studio crashed. So I'm not sure if there are any other options.

Please provide sample data and desired outputs as _formatted text_ not images. — Ilyes, Sep 04 '20 at 13:23
This is never going to be fast. The most convenient way is to generate queries that trundle over all fact tables to select `DISTINCT` values for each dimension column into a temp table, then delete dimension rows that don't occur in that table. A (non-clustered) columnstore index on these columns would speed this up considerably (and might be a nice thing to have in general in a warehouse). — Jeroen Mostert, Sep 04 '20 at 13:33
Yeah I was kinda afraid that it was going to be this way. I actually already had made code to generate the statements to do this. But at least now I know I haven't missed out on a better way to do this. — Hanneke Debie, Sep 04 '20 at 14:13

MLeblanc · Accepted Answer · 2020-09-05T15:38:19.737

we had to do something similar to this at one of my clients. The query, like yours with "not exists.... and not exists.... and not exists...." was taking ~22 hours to run before we change our strategy to handle this in ~20 minutes.

As Nsousa suggest, you have to split the query so SQL Server doesn't have to handle all data in one shot, having to unnecessarily use tempdb and all other things.

First, create new table with all keys in it. The reason to create this table is to not have to read the full table scan for every query, having more keys on a 8k page and to deal with a smaller and smaller set of keys after each delete.

create table DimensionkeysToDelete (Dimkey char(32) primary key nonclustered);
insert into DimensionkeysToDelete 
select key from dimension order by key;

Then, instead of deleting unused key, delete the keys that exists in facts table, beginning with the fact table that has the least numbers of rows. Make sure facts table have proper indexing for performance.

delete from DimensionkeysToDelete 
from DimensionkeysToDelete d 
inner join fact1 on f.fk = d.Dimkey;

delete from DimensionkeysToDelete 
from DimensionkeysToDelete d 
inner join fact2 on f.fk = d.Dimkey;

delete from DimensionkeysToDelete 
from DimensionkeysToDelete d 
inner join fact3 on f.fk = d.Dimkey;

Once all facts tables done, only unused keys remains in DimensionkeysToDelete. To answers your question, just perform a select on this table to get all unused key for that particular dimension, or join it with the dimension to get data.

But, from what I understand of your needs for cleaning up you warehouse, use this table to delete from the orignal dimension table. At this step, you might also want take some action for auditing purposes (ie: insert in an audit table 'Key ' + key + ' deleted on + convert(datetime, getdate(),121) + ' by script X'.... )

I think this can be optimize, take a look at the execution plan, but my client was happy with it so we didn't have to put much effort in it.

At the risk of stating the obvious - I assume none of the dimensions where you want to delete records are SCD2 (or similar)? If they are, you obviously have a more complex problem on your hands as you will need to fix the start/end dates of the remaining records in order to remove any gaps — NickW, Sep 07 '20 at 14:20

score 0 · Answer 2 · answered Sep 04 '20 at 16:00

You may want to split that into different queries. Check unused rows in fact1, then on fact2, etc, individually. Then intersect all those results to get to the rows that are unused in all fact tables.

I would also suggest a left outer join instead of nested queries, counting rows in the fact table for each pk, and filter out from the resultset those that have a non zero count.

Your query will struggle as it’ll scan every fact table at the same time.

How to find unused rows in a dimension table

2 Answers2