SSAS tabular model -Relationship- which datatype is better and faster ? - Int or big int or binary?

[Anonymous]

Hi Guys,

I have a tabular model with 2 fact tables and 10 to 15 Dim tables . I am trying to optimise the performance of SSAS model.

I got some suggestion from a developers and few other websites that having int data type is faster than integer .

What i would like to know is is binary more faster than int ? ie binary >int>str ?

Things i am planning to do 

1. Change the relationship key columns created from string to (integer or binary )

2.In all dim tables and fact tables,change the datatype to int except for those columns who are really strings .For example , i have a column called version number where the values are actually numbers(1,2,3,....etc) but the datatype I have used is nvarchar .   

3. Change the date and time datatype to date datatype 

4.If i convert the key columns to binary , will it be more faster than int ?

Pls let me know your thoughts on this.

Can you provide me your answers based on my question no# ?

[kentyler]

These strategies seem more like the kind of thing you would think of in SQL Server. 

The VertiPaq engine does not work the same way at all. This video will give you some hints about how to start thinking about optimising your data model, it's for power bi, but the same frame of mind applies https://www.youtube.com/watch?v=B-h3Pohtn1Y

[Anonymous]

Sure , i will have a check on it

[Greg_Deckler]

1. Yes, I would go with int

2. Yes, far more efficient in terms of storage, etc.

3. Less of an impact but if you only need the date, yes

4. I would stay away from binary for that

https://www.mssqltips.com/sqlservertip/5115/compare-sql-server-table-join-performance-for-int-vs-big...

https://stackoverflow.com/questions/18708829/binary-datatype-for-primary-keys-vs-int

[Anonymous]

thanks greg i will look into it