Forum Discussion

jersey417's avatar
jersey417
Helper I
3 years ago

Direct Query to Security Tables - Only load once?

I'm trying to find the optimium appraoch when using direct query to pull in our security tables for RLS. Several of our security tables are 25+ million records and direct query is an attractive option but appears to come with problems. When we use direct query for the security tables we're constently requerying against the securty views during user interactivity which is causing requring and performance slowdowns when interactiving with the report. Is there an option to direct query to a source only once upon opening the report?

 

 

We're also loaded the security tables as a dataflow VS Snowflake and continued to use direct query and found better performance. However, there were still some delayed in visuasl due to the direct query.

General Comment
Need Help

2 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    3 years ago

    Hi jersey417 

     

    I would suggest rather putting the tables into import mode and bring them into your model. Even though it is 25 million rows, the table should be small and then make all your subsequent reports a lot faster.

    • jersey417's avatar
      jersey417
      Helper I
      3 years ago

      We have other examples where our security tables are 200 million records.  I just think this is inefficient as we have some models that have timed out due to the number of rows in the security table.  We have some requirements where the security must be relatively live. To full this requirement without a full refresh of the rest of the content/dataset we’re setting up XMLA endpoints to refresh the individual security tables.

       

      It would be ideal to have some option that would direct query the data once and store the content VS refreshing over and over.