Power BI Premium and Azure Analysis Services

[nirvana_moksh]

Hello All,

I am sure this question has been posted here and as well as on other forums, but I wanted to get inputs from other users potentially facing the same situation or having knowledge of how to tackle this area. The problem statement that I am facing is deciding between Power BI Premium (for its Paginated Reports, incremental refreshes, dedicated capacity etc.) and Azure Analysis Service. The reason for this decision is to define the future state and moving more to Azure and addressing the problem of true date parameter queries and stored procedures which can be handled in Paginated Reports. Below are the keys points and open to be proven wrong or certified to be true:

• 90% of our reports have a SQL Query or Stored Procedure within which there are parameter’s defined like @Date , so there might be a sub-query within where it will be ‘WHERE [Processing Date] <= @Date’ and another sub-query within where it states ‘WHERE [Closing Date] >=@Date’ and all of these are done based on business rules and logic. Finally this query is used in our other reporting tools where a Date Parameter filter option is given to the users and that selection by the user in return populates ALL instances of @Date and then goes to our SQL Server and returns the results. From what I have done so far there is no direct way of doing this in Power BI, I have tried this by ‘invoke function’ in Power Query but what it did was execute all possibilities for all dates and then loaded data for ALL dates. Opposed to grabbing the user selection of @Date and then going to SQL Server to retrieve the results.
• I have developed a lot of reports in SSRS and I know doing the above is super easy by defining the input parameters and it will do what is intended and the same can be done via Paginated Reports
• The other decision is to move to Azure Analysis Services which can hosts large amounts of data and can process data at fast rates, but my question is how can we circumvent the parameter part of our procedures like @Date etc., in Azure Analysis Services? Is this not like another repository of data? 

What I am unsure of is that is Paginated Reports the only way to do true parameter type queries in Power BI and is this available via Azure Analysis Services or is it ONLY offered via PBI Premium.

Thank You

[GilbertQ · Accepted solution]

Hi there If your data is highly aggregated you can most certainly use Power BI or Azure AS to get this working for you. It would also depend on how the report is going to be consumed. If you want pixel perfect formatting and a lot of table based data then SSRS would be the best option, because this is how the product is designed. You could still use SSRS and connect to an Azure AS instance and get the data you require via DAX. Which could mean that both your Power BI reports and SSRS reports all use a single source of data!

[GilbertQ]

Hi there What you could possibly do is to use DirectQuery which would then fire the query all the way to your SQL Server Source. With regards to using Azure Analysis Services, you must remember that it is an analytical engine and is designed to do highly aggregated queries and super fast computations. If your SSRS reports are more granular and transnational then potentially Azure AS might be slower. With regards to your question when using Azure AS it too has all the data loaded into memory and when a user for instance uses a Date filter the query will use this to only return the required data. The same thing can be done by customizing the SSRS report using a DAX query to get the data back you require.

[nirvana_moksh]

Hey @GilbertQ thanks for your response, the problem is we do not use SSRS yet, we use Power BI (PBI Pro license) and Tibco products for other reports which handle all reports that contain stored procedures or direct queries containing these parameters from which we want to move away from and which is why we are trying to determine if we should upgrade to Power BI Premium of use Azure Analysis Services. In response to your suggestion of using Direct Query, I do not know how exactly that would work. For instance for a stored procedure like:

EXECUTE SP_CUSTOMER @DATE 

We have a report that feeds all potential options for the defined @DATE parameter via a date query (which is unique list of all dates) and this is shown as a drop down and every time a user changes the drop down value to another date it goes back to SQL and returs the result. I have not been able to replicate this same feature in Power BI yet, but the reason I said SSRS was because Paginated Reports is SSRS and in SSRS I know we can do this.

Thank you for help and resposne in advance!

[GilbertQ · Accepted solution]

Hi there If your data is highly aggregated you can most certainly use Power BI or Azure AS to get this working for you. It would also depend on how the report is going to be consumed. If you want pixel perfect formatting and a lot of table based data then SSRS would be the best option, because this is how the product is designed. You could still use SSRS and connect to an Azure AS instance and get the data you require via DAX. Which could mean that both your Power BI reports and SSRS reports all use a single source of data!

[nirvana_moksh]

So the source of data in question is not aggregated at all. The source is our Global Repository SQL System which has multiple tables from our different source systems which gets refreshed nightly via SSIS and SQL Server Agent jobs. So our SQL Data repository is normalized and via stored procedures and queries we define rules, logic and aggregate date and then finally the reports are created.

From the sound of it (correct me if I am wrong) Azure AS does not look like the final solution or ONLY solution since we have a normalized data repository that is the primary source and to aggregate it all for a Azure AS model we will have to recreate all views and stored procedures in a way? And even then the missing front piece will be SSRS which we cannot get via a Azure AS instance like that of PBI Premium Paginated Reports. But on the contrary with minimal overhaul and code rewriting we can upgrade to Power BI premium and get Paginated Reports through that and reuse our defined repository as is?

Thanks a lot @GilbertQ for clalryfying the above in advance

[GilbertQ · Accepted solution]

Hi there Yes you are correct in that Azure AS needs to have a Star Schema where the data is denormalized and put into the Star Schema From the sounds of it, you could most certainly use SSRS with Power BI Premium with your existing Stored Procs and views. Which would allow for minimal rework.

[nirvana_moksh]

Thanks for all the info @GilbertQ!

[GilbertQ]

Happy to assist you in your Power BI journey