Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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!
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