How to compare 2 tables with different keys?

GeeOh

Hi,

I am trying to compare 2 data warehouse fact tables across our Pre-prod and Production environments.

The issue is that the fact table keys are generated randomly in each environment as the data isnt ordered when it hits the dimensions for key creation.

The obvious choice would be to create views containing the business keys/data by joining the facts to the dimensions, however creating views on production environment is limited.

Is there another way I can achieve this through Data Compare Pro? - I'm thinking a custom query that is ran against the 2 environments and then the results of the query are compared.

Thanks.

Alex B

Hi @GeeOh,

There isn't a facility within the product to compare the results of a script that is run I'm afraid -unless you dumped the results from source and target into new tables that is.

Regarding the keys - is it that the columns and keys are not named and ordered the same?  Are you not able to map the columns and choose a custom key to compare on between two (or more) columns that make a unique record to make the comparison?  See this page for more details on mapping columns and setting the comparison key: https://documentation.red-gate.com/sdc/setting-up-the-comparison/selecting-tables-and-views

Kind regards,
Alex

GeeOh

Alex B said:

Hi @GeeOh,

There isn't a facility within the product to compare the results of a script that is run I'm afraid -unless you dumped the results from source and target into new tables that is.

Regarding the keys - is it that the columns and keys are not named and ordered the same?  Are you not able to map the columns and choose a custom key to compare on between two (or more) columns that make a unique record to make the comparison?  See this page for more details on mapping columns and setting the comparison key: https://documentation.red-gate.com/sdc/setting-up-the-comparison/selecting-tables-and-views

Kind regards,
Alex

Hi Alex,

Thanks for the response. That's a shame about the script result comparison.

As for the key columns, the columns are named the same however they keys values are different on a row level for their corresponding facts. I don't know how familiar you are with dimensional modelling, but if you've not had much experience with it, then it might not be something for you to easy conceptualise without me giving a lengthy explination of how facts and dimensions work together.

I've attached an imagine. In the image I am trying to compare 'FactA' table across 2 environments. Fact1... Fact2... Factn are never unique so they cant be used as keys.

One way would be to use script folders and code a local virtual table then populate it with static data from a query: https://documentation.red-gate.com/sdc/working-with-other-data-sources/working-with-scripts-folders. With over 200 tables, this method is not viable for me.

I'm now thinking that the only viable solution is to raise a request to our Database Administrators and create the views from the Fact to Dimension queries.

Maybe redgate should think about implementing a simple query to query compare across 2 servers?

Thanks.

GeeOh

Hmmm - I wrote a lengthy response which looks to have been removed from the forum?

Alex B

Hi  @GeeOh,

Thank you for the clarification!  I think it may have just taken a minute for it to post or it needed to be approved, but it's there now and that makes more sense.

You would need some way to map the values of the key 45>1, 75>2, 63>3 to be able to compare the FactA tables to each other, rather than mapping the column "Key" to another column (which is what is what I thought you had meant).

The only way for this to work (as you have ascertained) is going to be via a view that gets the result as you have shown at the bottom of the image since the values need to match for the rows to be compared.

There are two suggestions on the SQL Data Compare Uservoice forum similar to your request, so it would be good for you to vote on those and comment as well to elaborate on the situation here where it would help!
https://redgate.uservoice.com/forums/147879-sql-data-compare?query=query

Kind regards,
Alex