auto-update data from SQL Server

[gunicotra]

Hi to all,

I have realized an ETL service pulling data from Microsoft Enterprise SQL Server into Power BI.

Being both (Power BI and SQL Server) into the same machine, I would like power BI to update automatically without me to click Update button.

I tried with DirectQuery and setting a Page Refresh every 2 minutes. But after 10 min nothing happened!

Have I configurated the setting in a wrong way, or is there a better way to do this?

Thanks in advance!

G     

[lbendlin]

Please explain a bit more what you are trying to achieve.  Are you talking about Power BI Desktop or Power BI Report Server? 

[gunicotra]

I have my data in two semparated tables (Logger 1 and Logger2) within an SQL Server DB. This is connected with PowerBI Desktop through DirectQuery. For both tables the power BI Automatic Page Refreh (APR) works fine. However in order to use a slicer and other techniques (measures, calculation etc.), I need to have the data of the two tables in the same table. For this reason I have created a new table as "Union(logger1, logger2)". 
My problem is that, even thogh this table is directly connected to the two tables which update automatically, this table does not update automatically at all. 

[gunicotra]

PS.: In order for this composite model to work, I have also activated the "DirectQuery for Power BI dataset and Analysis Services" option from settings. 

[lbendlin · Accepted solution]

Create a view on your SQL server that does a UNION ALL on the two tables.

Creating a union in DAX will result in a calculated table which behaves like import mode.

[gunicotra]

I have doen what you suggested and it works, but in the sql sintax I have edited I have problem in identifying the new logger's data.
1. I have updated the tables adding a new column (ALTER TABLE Logger1, ADD LOGGER varchar(10);
2. I have updated the new column, giving the name of the logger (UPDATE Logger1 SET LOGGER = 'RT1')
3. I have created the new union table (

SELECT * INTO LOGGERS FROM
(SELECT * FROM Logger1
UNION
SELECT * FROM Logger2) a

SELECT * FROM LOGGERS

The old data are correctly identified as RT1 or RT2, but the new data are Blank! I wish I can have the old and the new data identified automatically as RT1 or RT2. Have you hints to fix this?

[lbendlin]

When you SELECT INTO you are materializing the view. Are you sure you want that?

Your view definition should be:

CREATE VIEW Loggers AS
SELECT *,'RT1' [Source] FROM Logger1
UNION ALL
SELECT *,'RT2' FROM Logger2

[gunicotra]

Thanks again for this hint!
May I ask you one more issue? My tables Logger1 and Logger2 are updated every 5 minutes with new data, but these data have not the RT1 or RT2 for the LOGGER column (since I have created it manually with "UPDATE Logger1 SET LOGGER = 'RT1'" at a certain date/time). Therefore every 5 minutes I receive further new data which have the column LOGGER with NULL.
Is there any solution to have the column LOGGER continually updated with RT1 or RT2 without me to pass the UPDATE sintax everytime?!

[gunicotra]

ops excuse me lbendlin! I have executed your sintax and it is exactely what I needed!! :)) Thanks very muche for your help!