Day 23 - Connect to Local SQL SERVER with a Power BI gateway

I have received few queries about Standard Mode POWER BI gateway (i.e. Enterprise Mode gateway) from my LinkedIn contacts . So I have come up with this blog . Hope it helps !!!!

I have installed SQL Server on my system and also installed On-prem gateway.

Please follow step-by-step instructions to link your SQL Server to On-prem gateway. It would help in refreshing data at POWER BI Service without opening your POWER BI DESKTOP tool.
Now you can schedule data refresh or refresh it on adhoc basis.



Step 1: Install On-prem data gateway > Configure it and restart it



Step 2 : Select your dataset and click on Schedule Refresh . Below web-page would pop up.



Click on Actions drop-down and select the data-source and later click on 'Add to gateway' to add this data-source to On-prem gateway.

Step 3: Enter data as shown below to configure data-source at POWER BI Service


Authentication method should be Windows in our case.

Step 4: Now it's time to map it to On-prem gateway. So go back to refresh page and select the data-source name to link the data-source to On-prem gateway.


Yippee !!!  Now you could see  Scheduled refresh section is enabled.


#RR #Day23 #SQLServer  #POWERBI #gateway #enterprisemode #HappyLearning #WeLearnEveryday

Comments

Post a Comment

Popular posts from this blog

Day 32 - Python Script to track Available COVID-19 Vaccine Slots for 18+ in India

Image
 Booking a COVID-19 vaccination slot reminds me of good old days of booking a TATKAL ticket in   Indian train. If you are lucky and very fast with Keyboard then we would be able to book the train   ticket  under TATKAL quota. Otherwise Surprise !!!  Same rule applies here too but we don't have idea about the next available slot timings for vaccination. When is the next slot going to open ? Still it's a suspense for us. By the time we come to know that there are few slots available, CoWIN portal ( https://www.cowin.gov.in/home ) shows that it's already booked. I have observed that people are struggling to find an available slots in their locality. So I thought of writing a python script using public API to track the available COVID-19 vaccine slots in the entire city. Task for interested folks who want to send an alert to a group/channel: Please trigger an email or message on WhatsApp/Telegram group whenever you get any data in the output file - "18plus...
Read more

Day 26 - Call Power BI REST APIs to get POWER BI REPORT Details

Image
Step 1:  Launch the website - " https://docs.microsoft.com/en-us/rest/api/power-bi/reports/getreport " Step 2: Click on Try It and Select one of accounts to Sign In Step 3: A new page will appear on left hand side of the screen where you have to enter 'reportId'. Step 4: How to get 'reportId' Go to POWER BI SERVICE ( https://app.powerbi.com/ )   and browse your POWER BI Report. You would get the URL in below format. Please follow below mentioned instruction to extract the reportId Step 5: Enter the reportId on this page and click on Run button. Step 6 : Yipee . We are able to get the details of POWER BI Report using Rest API but there is a limitation here. Token key gets expired after sometime.  So we should do R&D from our end to develop a CUSTOM Rest API to get the POWER BI details in case we want to access it from external system. Any suggestions or inputs would be greatly appreciated !!!  TABLE 3 Name T...
Read more

Day 24 - Use ODBC driver to connect to Oracle/SQL Server DB from POWER BI Desktop

Image
Open Database Connectivity (ODBC)  The ODBC Driver for Oracle enables an application to access data in an Oracle database through the ODBC interface. Step 1 : Install ODBC Driver  Step 2 : Open ODBC App and Navigate to 'User DSN' > 'Add' to add a new connection Step 3:  Select option - 'Oracle in OraClient.......'  to add a new oracle DB connection. Please enter details as shown below. Click on Test Connection and enter DB password to test DB connection. You would get below message if connection is successful . Step 4: Launch Power BI Desktop and Navigate to Get Data > ODBC > Connect  Select DSN  which you have just configured in above step. To avoid extra delay, it's a good practice to enter SQL statement to fetch relevant data from DB. Click on OK Step 5 : Enter user name and password to fetch data from Oracle DB Step 6 : Tada    🎉   🎉 Able to access Oracle DB successfully from POWER BI Desk...
Read more