Forum Discussion
Restrict rows retrieved by query?
The single-qoutes is needed as part of my SQL query to filter a string column in the SQL database
The 1st double-qoute stop the "free text query" and allows to insert a parameter
Ampersand is needed to merge the parameter with the SQL code around paramater
The 2nd double-qoute continue the "free text query"
I hope this explains the code.
Now back to your problem... 19 foreign keys "scary face". I assume that you users will need to be able to filter several parameters at once? This will force you to type SQL code from now until you retire. As I see it you will have to create 19*18*17*16*15*14*13*12*11*10*9*8*7*6*5*4*3*2 combinations of your code + create 19 parameters lists with a complete set of values for your user - and this will only give them the option to select a single value per parameter. I don't think this is the right path for you. You will need to many different variations of your code because consider that your user only select a parameter in a few of your lists, then the rest will be blank but if you query your SQL with WHERE a.Country = '' then you will only get the customer with no country value (blank), but what you really want is not to filter the country column in your SQL table, so you will somehow have to handle all the combinations of posible where clauses you need.
With single value per parameter I mean they will not be able to select both US and UK customers but only one of them - unless you make you parameters free text values. Then you will be able to chance the SQL to use IN instead of =, but your users will only get the wanted result if they learn to write the right text - using my example from before this would be 'US', 'UK' and your where clause should be "WHERE a.Country IN (" & CountryParameter & ")"
classical problem. Hit the table and ask, what do you want?
I think this approach is better than "here is all the information, ask yourself"
I'm sorry, I don't understand your response. What approach is "better than 'here is all the data, ask yourself'"?
What I am looking for is the capability to create custom queries in Power BI as the basis for reports and visualizations - not limiting it to generic queries or pre-defined queries built by a guru, but letting end users build their own queries, reports and visualizations without having to be an expert in SQL, DAX, M or some other specialized language or feature set in order to limit the data they look at. SQL Server should do the work of retrieving the appropriate rows (which it can do quickly and efficiently), the tool should not require the user to bring in all of the data and create complex mechanisms to filter it down to the data they want. Can you imagine refreshing a query on a regular basis, if it retrieves all rows and then filters them? Ugh!
But it seems that's just not possible with this tool. Is that correct? Too bad, the capabilities of Power BI are great for end-user analysts to define and publish useful management and staff visualizations, but the learning curve is too steep (or the tool is not appropriate) if there's no simple way to do such things.
Am I missing something? I'm new to the product, so that's very possible - and the purpose of this question. I'd like to use Power BI if it has the needed capabilities.
Thanks for your response and help.
SamTrexler Is there some reason that you haven't gone down the modeling route? Using SSAS Tabular for instance? Everything you describe as it relates to an end user filtering/slicing and dicing without the need for complex queries is the purpose of developing a BI layer to your relational data. The end result is a model that contains the pertenant business information, and gives the end users the ability to search anything they want in the model.
Anonymous, that's where we're headed. We're looking at using SSAS Tabular modeling for business analytics. But at this stage of the project, we have flattened operational data that we intend to load into SSAS, and I was wondering if we could use that data for operational analysis as well - retrieving detail rows for analysis instead of the lowest level of aggregation.
I have prototyped this in Excel using a Microsoft Query data connection and applying criteria to the query that are linked to cells in the spreadsheet. This gives the user the ability to change the value of the parameters in the spreadsheet and get the corresponding rows. It's also not too hard for someone experienced in Excel to add criteria to the query, without knowing SQL. (There are some gotchas there, which we'll need to train on.)
When I saw Power BI, I liked the power of reports, visualizations and dashboards very much. And I wondered if it could do the same thing, with greater presentation capabilities. But it appears the answer is no, so we may have to remain "old school" with Excel and Microsoft Query, unless someone has a solution or recommendation?
Thanks.
