Data Validation/Compare

#1

Hello,


I'm looking for some advice on how to implement this.

I have an Excel sheet where I want to compare all rows (based on certain key columns) against the values in a SQL database. Purpose of this is just to make sure the data from our Excel is matching up to the database.


I'm guessing I would create the Excel sheet as a metric set and create the data in SQL into a data cube?


Afterwards I'm a little confused as how to do the compare/validation piece. Would I have to merge the two datasets together and setup sorts and states to compare the two?


I also want to highlight any cells that are not equal to each other (Excel vs SQL).


Any help would be appreciated.


Thanks!

#2

You could compare the two on the data cube level. Join the SQL and Tabular selects and then use a calculated element for the comparison. A result set of 1s and 0s is all you need.


Or you could compare the two on the metric set level. Use the Advanced option on a formula measure to generate the same 1,0 result set.


In either case, you will need to set up states to highlight the cells based on the 1 or 0 value.