Hasbytes as Primary Key

[soldstatic]

Hello,

We're creating a data model in sql server that combines data from multiple sources in our data lake. We're trying to build it with the future in mind as we will likely turn it into an ETL type process as well. For the moment, it is basically a series of views that we've defined. 

In doing this, we have multiple columns within each view that when considered together act as a 'primary key' for that view. Each of the three main views have their own key, and are tied to each by this key (group of columns).

I defined this key in the view by using hashbytes, as in the snippet below. My problem is that PowerBI loads the hashbyte column as binary within query editor, but outside of query editor these columns don't seem to exist. My goal was to be able to create a simple relationship within powerBI between the three main views utilizing this column for the 'joins' between them, but I can't seem to define that relationship from the modelling tab within PBI desktop because it doesn't show that column at all. If I change the type in query editor to change it to text instead of binary, I receive an error when applying the changes that this type of transformation cannot be used with direct query. 

Please help!

Andy

ALTER view [CustomerView] as
	select HASHBYTES('SHA2_256',
			CONCAT(SourceSystemID,'␟'
				,SourceSystemVersion,'␟'
				,ETLVersion,'␟'
				,ETLDatetime,'␟'
				,RecordVersion,'␟'
				,CustID,'␟'
				)) as CustomerKey
				,* from Customer

[Anonymous]

Hashbytes apparently works in PowerQuery now.  I'm using this: 

convert(nvarchar(50),hashbytes('MD4' ,BR.projecttitle),2)

[v-piga-msft]

Hi @soldstatic，

Yes, I'm afraid that we cannot convert the data type when in direct query mode.

We may try another way, you could have a reference of this similar thread which has been solved.

Best Regards，

Cherry

[soldstatic · Accepted solution]

I was able to switch to the CHECKSUM() function within sql server. I think this will work, but I'm waiting on some indices from our DBA group before I can actually run my verifications. However, this did come into power BI as a number so I was able to use this as the key linking the tables together. My performance is terrible w/o the indices, but I think the CHECKSUM has done what I needed it to do.