October 17, 2016 at 10:27 am
Hi,
I hope somebody can help shed some light on my issue as I am struggling to find an answer.
I am using SSAS 2014 SP1 - Tabular and Power BI Desktop/Service. My issue is that of performance when running a power BI report connected to a tabular model vs a direct sql connection. My scenarios are
1. I create a proof of concept report using Power BI Desktop, connect directly to sql server using the Direct Query option, build my model in Power BI Desktop (very simple 1 fact table, 4 lookups). Everything runs quite fast - max refresh time so far of 3-4 seconds. Any changes to the report are instant. Everybody is impressed with the 'realtime BI' 🙂
2. To centralise everything, I then create a tabular model exactly the same in VS 2013, and switch on directquery mode, deploy (getting all excited at this point). Connect my report to the newly deployed model and hit refresh. About 2 minutes later I see the data. Any changes to the report yield the same waiting times.
I have other tabular models running on my SSAS server absolutely fine, I only have one measure in my model which is a simple sum('Table'[Field]).
I'm new to analysis services in general and cant really find much of an explanation as to why this would be, unless somebody knows of a software breaking bug/incompatibility. I'd really like to have everything in a tabular model for ease of documentation and training but this issue is crippling my OCD :-). To be honest, I'm not even sure where to begin troubleshooting.
Does anybody have experience with this issue to shed some light on it?
Thanks in advance
Henry
November 4, 2016 at 4:37 am
My first (very superficial) guess would be that when you query the SQL database directly, query folding happens (i.e. Power BI is able to simplify and filter the query which is executed against the source database) which ultimately could also reduce the amount of data being sent to the client.
When you use a tabular cube and direct query however, this may no longer be happening and all of the cube data may be sent to the client. I would run a trace to see what queries are executed against the tabular cube, and go from there.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply