Power BI Exchange

Expand all | Collapse all

PBI With Large Datasets

  • 1.  PBI With Large Datasets

    Silver Contributor
    Posted 05-16-2018 10:26 AM
    I looking for suggestions to best fit our requirements.

    We have ~120 tables and the size of data is ~2 TB on SQL Server 2016 
    And we have UDF with user name parameter (Ex: SELECT a, b FROM table (@UserID) )
    there are few other functions with complex logic to enable the security using Views. 

    I want to use direct query and pass logged-in user as the parameter to this UDF, so that I can implement security on my data. 

    Limitation on direct query: 
    I cannot add extra steps on Power-Query (Edit query window) Where I can get Username with other ways and pass that to a query as a parameter.

    1. I cannot do import model due to the size of the data and maintenance if there is a change to security logic. 

    2. USERNAME() function is not available on Power-Query.

    3. Tried using "whoami" from R.Execute package --> But this is available only on Desktop not on Service. 

    4. Tried passing the parameters using PBI Embedded "App owned data"
    and REST API. --> Direct query will not allow to update the parameters on service at run time. 

    5. AAS is working fine, but worried about the performance of refreshing the cube with this huge amount of data and also the performance of reports. 

    I am looking if anyone has faced the similar issues and how they did it.
    Also looking for any ideas / suggestions to handle this requirement

    Yaswanth Muthakapalle


  • 2.  RE: PBI With Large Datasets

    Posted 05-17-2018 03:08 PM
    First off, I am no IT or Security expert so I cannot provide very specific answers on what I am reading, but may be able to provide possible scenarios you could look into.

    I find Direct Query to be too limited for my current uses because you cannot connect to multiple databases within the SQL server and you cannot mix in imports like Excel documents.

    As for a high volume of data Importing from SQL Server, you can use a SQL Statement within the original Query Connection to filter and combine tables (when you first do "Get Data" and choose the SQL Server Database, this is where you enter the Server name and Database name, then you unroll the Advanced Options and can type or paste in a SQL Statement code to prepare some data for import)

    This will help to only bring in specific results into an imported table that does not have PBI running overtime to pull in millions of rows and dozens or hundreds of columns that may not even be used.

    As for security abilities, Power BI still has lots of control. Someone within your organization that does not have Read-Right abilities to a table on the SQL Server will not be able to refresh and utilize a data set that pulls from that. Also you can restrict who what users can see what data within reports they view by using Row Level Security. Also you can limit non-owner or admins within groups to not be able to edit reports, only view and use them. From there you will have Gateways setup on Power BI Service and you can allow only authorized individuals to build reports from  a dataset using each gateway connection.

    I have not yet experimented but you can connect to SQL Server Analysis Services databases in case you already have that doing some of the grunt work with your large amounts of data. I am not familiar with the SSAS and its abilities so just throwing that out there as well.

    Another potential option is to build in Parameters into tables that help filter down the data within to be more manageable. For example your Sales table could have a parameter to only filter by the full year. If you set the parameter to 2017 it would only have that data table containing 2017, which in turn does limit your results on the Report side, but it just depends what your goals are for the end result of the reports. You could do the same thing for sales within a country or state only but leave all of the years alone so you have that full sales history within that region. Possibilities with Power BI can really be endless once you learn the data set potential and how to utilize DAX for creating virtual tables and Measures feeding your reports. You can take a little data and make a lot of results with all of those abilities.

    Besides these Power BI Forums, another very useful company and training website is EnterpriseDNA.co. Sam the owner has excellent videos for learning about parameters, data model best practices, advanced/complex scenarios, and lots more.

    Andrew Foulk
    Financial Analyst
    Weaber, Inc.

  • 3.  RE: PBI With Large Datasets

    Posted 05-18-2018 03:15 AM
    I'd suggest looking into Row Level Security on the database itself.

    Row-Level Security
    Microsoft remove preview
    Row-Level Security
    Row-Level Security (RLS) simplifies the design and coding of security in your application. RLS enables you to implement restrictions on data row access. For example ensuring that workers can access only those data rows that are pertinent to their department, or restricting a customer's data access to only the data relevant to their company.
    View this on Microsoft >

    Mike Ross
    Aberdeen Standard Investments