SASS import to Desktop slow

[Anonymous]

I am importing a SASS tabular model into Desktop (2.35.4399.601 64-bit (May, 2016).  One tabular model, about 827k rows of data. Sales transactions, some date columns, nothing unusual.  Desktop writes 4,096 rows, then hangs.  Then resumes writing about 1,000/sec. Seems slow.  Connect Live from same SASS model is quite fast (like instant).  

I turned off Relationship detection and auto date in Settings to see if Import times were improved.  No improvement.  SASS info:  We upgraded our SASS server to SQL Server 2016 last week.  

Question 1:  Is about 1000 rows per sec correct?  Realizing you have no idea what we are pulling...just checking to see if we are normal.  

Question 2:  Any Desktop settings outside of what was mentioned above that may improve SASS Import speed?  

Thanks, Lee  

[Anonymous]

Power BI stores all of its data in-memory, while the live connection should be expected to be almost instantaneous.

My Suggestions:

• Import less data. Import only columns and data you need. Make sure your looking at the right granularity (do you need each days data or can it be rolled up by week or month?)  Look to avoid high cardinality columns where possible (columns with many distinct values).  Columns containing calculated values should probably not be imported if you can recreate the calculations in DAX after the import).

If possible, I'd avoid direct import and choose Live connection.  This is almost always the best option, and import is rarely actually necessary.   Is there a specific use case or reason that you need to import the data?

Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. 

Thanks,

Ryan Durkin

[Seth_C_Bauer]

@Anonymous Can I ask why you want to import the data? You already have things set up in a model, why extract all the data into the Desktop? You could  be running into memory limits on your machine, depending on how much RAM you have.

[Anonymous]

Forgot to answer your RAM.  I have 32gb.

[Anonymous]

Good question Eno1978.  We are setting up analysis on the data itself.  We will be adding a few calculated columns and measures.  We will then chart the data to see if anything is not normal.  Thinking outloud...we could be using data tools to build the analysis model there (create dax measures and stuff) then juct connect live in desktop and create our charts.  

[Seth_C_Bauer · Accepted solution]

@Anonymous Gotcha. So you want to use the Desktop to develop against the model. There is a request in currently to add features to support additional calc columns and measures on a live connection in the Desktop - but it isn't in yet.

I do this a lot, and its a pain to develop against the model, push changes, then check visuals... so.. Here is what you can do. (I wanted to write up a blog post on this before pushing this out, but oh well, now I'll have incentive to write it up quicker) 🙂

When you connect to your tabular model in Visual Studio, there is a working version of the model spun up with your name and long guid in SSAS. You can point Power BI Desktop at that model, and develop all your calculations on the local copy you are testing in VS. This allows you to validate that the calcs work as expected in the visuals prior to checking the model in and pushing the changes to your main model.

In this fashion, you get to keep your live connection performance, and can develop directly against your working model. From my experiance, as soon as I create a measure/calc column in my model, it shows up in Power BI Desktop. - It's a great way to develop 🙂

[Anonymous]

Excellent info.  You Rock Eno1978.  Will try this proceedure.  Made my day.  Thanks.  Lee_Tobii