Forum Discussion
Parameters Missing
Yes, but the SSRS report is based upon a data set that has all of the data loaded into it either via the actual transactions themselves or the ETL function that loads up all of the data. This is exactly equivalent to the data model in Power BI.
When the user in SSRS selects parameters, the SSRS report is essentially building a query to query the database and return the relevant rows. This is analogous in Power BI to the use of slicers.
In review. Your database behind your SSRS report has all of those "useless rows" in it because it needs to have them in order for the SSRS report to query it to return the relevant rows for the visualizations. Power BI data model also needs all of those useless rows in its dataset in order to return relevant rows for its visualizations.
What it sounds like what you really want is Direct Query which avoids the whole import process that you do not seem to like. But, I believe in one of the multiple, multiple, multiple threads that you have posted on this topic that you don't like Direct Query either.
Yes, but the SSRS report is based upon a data set that has all of the data loaded into it either via the actual transactions themselves or the ETL function that loads up all of the data. This is exactly equivalent to the data model in Power BI.
When the user in SSRS selects parameters, the SSRS report is essentially building a query to query the database and return the relevant rows. This is analogous in Power BI to the use of slicers.
In review. Your database behind your SSRS report has all of those "useless rows" in it because it needs to have them in order for the SSRS report to query it to return the relevant rows for the visualizations. Power BI data model also needs all of those useless rows in its dataset in order to return relevant rows for its visualizations.
What it sounds like what you really want is Direct Query which avoids the whole import process that you do not seem to like. But, I believe in one of the multiple, multiple, multiple threads that you have posted on this topic that you don't like Direct Query either.
Hmmm, Maybee the speed difference then is the fact that all the SSRS reports are On-Premise. I didnt realize that all the data was loading in the back end. I just figured that the parameters were being passed to the SQL query and then the dataset would load.
We cant use Direct Query becouse it is too limited. Every time I have attempted to use direct query except for the most basic of reports , I run accross some un-supported function. Especially when working with Financial Data.
I am going to ignore Parameters for now as thats not a solution. I am going to try and re-model my data so that I can find a way to stop the contsant 5 minute wait when selecting a filter.
What I have noticed is that the lookup function in Dax is lightnening fast compared to creating relationships. If I use Lookup functions rather than relating the data I am getting at least a 120% increase in Power BI report speeds and in some cases even better.
I was hoping to use parameters to limit the amount of data being loaded, but that is also not an option.
Perhaps the best way to do this is create a single fact table, then use Lookup functions for all the other related data.
If SSRS and Power BI are doing the same exact thing, then Parameters are not needed in Power BI.
Thanks for the explenation and help! Back to the drawing board for me...:(
It would be interesting to understand your data and how you modeled it and how that might be changed to improve speed. When you are filtering it, are you using slicers or actual filters? I have seen some instances of Power BI when there are like 20 slicers on a page that it can become slow because a change to one slicer affects all of the other slicers and fact tables on the page so there are queries being generated for all of those and they essentially "cascade". You can get around that sometimes by having your slicers not update your other slicers in order to cut down on the number of queries generated.
It sounds like you might be in this boat because you mention LOOKUP being faster than relationships but I can't tell for sure without knowing you data model.
Smoupre,
You bring up very good points. Let me determine if there is a way I can spend some time making my data generic. I could then actually upload the .pbx file and you could see exactly what I am talking about.
Basically:
I have 1 SQL view I am pulling in all 25K + customers in the Great Plains 2016 Database. This is the Master Customer table containing things like region, country, and some numbers used in calculations. One of them is the Customer Balance.
I have another SQL view that I am pulling in all 1.5 million + Open and Closed customer Invoices and Payments from the Great Plains 2016 database. This table is actually the fact table and calculations are needed to determine the balance and it should match the balance in the master customer record.
When I create a relationship and view the fact table invoices and payments then add the customer balance fieeld from the Master record, Power BI will spin for a very very long time. Even when published it spins a long time.
If I create a lookup on the customer ID in the fact table to the master customer record and use the customer balance there is no spinning at all.
However, beyond even this, I have a few new tables to incorporate for scheduled Payments which GP 2016 makes a complete mess out of calculating balances etc.
The end result is a BAD Debt report based on foriegn currency and exchange rates, it must calculate the remaining balances at a given point in time and project 3 months of bad debt using the policy that all Invoices with a scheduled payment plan will be Bad Debt for any amount over 6 months. Then, any invoice amount regardless of type and is 6 months old becomes bad debt and is reserved in the Genral Ledger acount by country. There are amounts in the General Ledger reserved for each country and this report needs to provide exact details into those numbers and allow managers to understand bad debt and determine if customers are paying on-time according to the agreed upon payment plan. It is also an audit of sorts to ensure that the numbers match up.
The Pain point is in calculating real time balances becouse they want to be able to go back in time and see what balances and exchange rate calculations were used. So I need to Pull in ALL the data for each customer so that no matter what date they choose, the report will calculate the correct balances etc... as of that report date.
What I could do is duplicate the existing tables and then find a way to make all the data anonymous. Then build from there. Maybe that would be a good way to explain issues or get help with the reoport.