CODEWITHSUNDEEP
sqlpowerbicreate-view
DoanhDoanh
DoanhDoanh

Reputation: 1

How "CREATE VIEW" works?

I'm a newbie in here. I had a problem that need your help.

Here is my context: I have 1 table that contains 100 mil data rows. I need to build reports from this table. I use Power BI, using direct import, and write SQL statements in Power BI. When the data loaded to Power BI, it reduced to 20 mil rows (cause I used GROUP BY in SQL statement). But the performance of Power BI is really terrible. In my opinion, Power BI had to run the query statement, and then visualize data, so its performance is bad.

Here is my solution: I'm going to CREATE VIEW (using GROUP BY statement) in my database. So that, the run query workload is no longer belongs to Power BI. My database will take responsibility for the executive SQL statement. And Power BI just only need to visualize data, so the performance of Power BI will be better.

Here are my questions:

1 - Does my solution work? :)))

2 - IF my solution work, my database just needs 1 time in running CREATE VIEW statement, and no need to run it anymore in the future, right?

3- If my solution work, the SQL running workload will move from Power BI to my database, right?

Thank you in advance.

Upvotes: 0

Views: 254

Answers (2)

msta42a
msta42a

Reputation: 3741

When you use Import mode, then your Database is query once (at refresh time), but the view still need to make aggregation and there is no difference between select from view vs select with the group by (the view is only a nice packed query, better to materialize view or populate standard Table with the daily job);

It's a good idea to remove unused columns and rows, older than X (also aggregate if possible).

Consider using Incremental refresh to shorted your load.

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

Incremental refresh is supported for Power BI Premium, Premium per user, Power BI Pro, and Power BI Embedded datasets.

Upvotes: 1

developer
developer

Reputation: 159

I have no idea about powerBI but if you use view it executes the query in view every call. You can try materialized view instead of view. But be aware materialized view has the snapshot of data when it created or refresed time.

as an example the view MY_MATERIALIZED_VIEW is refreshing every day.

create materialized view MY_MATERIALIZED_VIEW
build immediate
refresh force
on demand
start with sysdate next sysdate + 1
as
....query

Upvotes: 0

Related Questions