Parameters Missing

[lcasey]

I really dont understand this at all. Logically, there is no sense what so ever to only allow parameters be used in Power BI desktop.

NOBODY and I mean NOBODY in the executive level is going to install and use Power BI Desktop. They only consume reports to make informative decisions. 

So what possible use is there in Parameters?  Seriously?  When I publish any report with Parameters in them , the end user has no way to input parameters in the Power BI service.

This has to be the most un-usefull feature ever developed.  What is the Purpose of a parameter if it cant be changed? 

[Greg_Deckler]

There are a number of very useful purposes for parameters. One, for example is the use of parameters to move between development, qa and production environments. So, for example, you parameterize the SQL Server name and then it is very easy to move from one SQL Server to another. 

That is just one example. It sounds like parameters are not useful in your case as you are looking for user-based ETL control, which, I would point out, isn't available in things like SSRS either really.

[lcasey]

Smoupre,

I have to disagree,

We have hundreds of reports that require user input before the report even loads.  Users must choose dates, departments etc... before the data even loads in SSRS.  Then, Only the required data is queried and returned.  It Blows circles around Power BI as far as speed and flexibility.   SSRS reports are 10 x faster , much more user friendly and everyone is in control of the data they load.

In Power BI,  I must load millions of useless rows for every user, then only after millions of rows are imported can the user begin to filter data.  This is completely backwards and makes no sense to people that have been developing reports for years.

With SSRS, the user sets the parameters they want, then the data loads in lightnening speed as it doesnt require a bunch of useless rows be included in the dataset.

Here is a screen shot of SSRS parameters being used to query SQL data. Users enter parameters and then the data loads according to those parameters.

[Greg_Deckler · Accepted solution]

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.

[lcasey]

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...:(

[Greg_Deckler]

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. 

[lcasey]

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.