CODEWITHSUNDEEP
powerbi
Peter Sun
Peter Sun

Reputation: 1813

PowerBi - Connection Type (DIRECT QUERY or IMPORT DATA) Question

I am working on a PowerBi project and I need some advice/questions on the best way to approach this project. I am tasked to create a dashboard for employee metrics pulled from an onsite SQL Server database. The managers here are going to have access to the PowerBi cloud, so I will end up uploading this to the cloud. There are 10 or so metrics that need to be shown on the dashboard. We have 5000+ employees. My first thought was to create a table and dump all the metrics into a table and set the PowerBi report to import the data, but that seems excessive and a waste of space to upload all that data to the CLOUD because all of the managers don't need access to every employee. They may want to see 1 or 2 employees' metrics on the dashboard.

My second thought is to (and if this is possible) create a stored procedure that will take the employee id and output a dataset for PowerBi to create a visual for. On the dashboard, have a list of employees and when a manager selects one, PowerBi will call the stored procedure with the employee id and the dataset will be returned for PowerBi to decipher into a visual based on my measurements. I guess I would set the PowerBi report connection type as DIRECT QUERY?

Here are my questions:

  1. Is this possible? Is it possible to what I am thinking for my second plan? Is this how DIRECT QUERY works?
  2. If so, how does DIRECT QUERY work with the PowerBi cloud?
  3. What is setup like? Do I just install the PowerBi Data Gateway/configure it like IMPORT DATA and PowerBi does the rest?

Upvotes: 0

Views: 252

Answers (1)

Nandan
Nandan

Reputation: 4935

A couple of queries:

  1. What is the frequency of data update ? In case if it is a batch job, it is ideally preferable to import that data from source into powerbi model and do reporting on the imported data as a) The performance would be quicker b) There would be no to and for of data across on prem database and cloud c) the source would not be impacted constantly

  2. So is the ask to have RLS wherein the managers should see only the employees under them? Then it is pretty easy to implement RLS in imported version rather than in case of direct query.

Also you won't be able to pass parameters to stored procedures, and you can't execute them in direct query mode. You can however, create table valued functions which give you the ability to use table variables and perform other functions that are more complex in nature in Direct Query mode

you can refer this for additional details : https://community.powerbi.com/t5/Desktop/Can-i-call-Stored-Procedure-with-Direct-Query/m-p/267141#:~:text=%40Pallavi%20you%20won't%20be,nature%20in%20Direct%20Query%20mode.

Upvotes: 0

Related Questions