Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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# ?

 

 

4 REPLIES 4
kentyler
Solution Sage
Solution Sage

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





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

Sure , i will have a check on it

Greg_Deckler
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

thanks greg i will look into it

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.