Thank you very much! I will try! Just a question. Is the Azure SQL Service one of the free Azure Services? In my company all have fear to use Azure... even I had to use my personal credit card for testing Azure Cognitive Services & show to my boss what I can get!... now when you finish your free time if you wish to go on with a free account you have to give your card. It's free for 750 transactions/month for cognitive services... This is why I ask you this question... I suppose some Gb/month will be free....
Thanks Ruth, but after watching several videos of the BI Convention in Seattle, now I think I will try to use Common Data Services to save my data and model in the cloud. Then I've seen that I can recover them in Power BI with DataFlows, and it's much cheaper! Another new upgrade I'm astonished is the possibility to use Power Query Online, which is now a part of Power BI service! I'm realizing of a new world of posiblities in the world of computing and modelling. Now you can work "on the fly" with the data when a transaction is being triggered and then to "recompose" the transactional data according the data model you are using! If the triggered data come to Power BI already "joined" you could simplify your Power BI model a lot! A join of just one record in the cloud is carried out only once (very good if you are not a PRO user without incremental refresh) and very efficient in time as the records are composed according the model schema on the fly...
Hi Ruth, thank you very much for your very helpful videos! I tried to do like you suggested - however there is alwas a failure-message: DataSource.Error: ADO.NET: R script error. Error in odbcDriverConnect(connectionString) : could not find function "odbcDriverConnect" Is it a driver-problem? A problem regarding windows 32-bit or 64-bit-version? Best regards, Bernd
@@CurbalEN Hi Ruth, yes I have installed the RODBC - here the respond: > install.packages("RODBC") Installing package into ‘C:/Users/bernd.hartmann/Documents/R/win-library/3.5’ (as ‘lib’ is unspecified) trying URL 'cran.rstudio.com/bin/windows/contrib/3.5/RODBC_1.3-15.zip' Content type 'application/zip' length 879575 bytes (858 KB) downloaded 858 KB package ‘RODBC’ successfully unpacked and MD5 sums checked The downloaded binary packages are in C:\Users\bernd.hartmann\AppData\Local\Temp\RtmpyilUjs\downloaded_packages Best regards, Bernd
Hi, thanks for the video. I am not able to view ODBC string Azure for MySQL Database. Can we achieve this using other driver in R script or Python script?
Thanks! I will try this at the office. For someone who hasn't used Microsoft SQL Server Management studio, how do you do the part at 9:24 where you connect to the Azure server? Is it easy to find the Azure server connection details?
From the little I understand, SQL server is optimized for normalized data models, whereas PBI likes one big fact table with many small dimension tables. Does the model get normalized when you export it to Azure SQL?
Hi Ruth, very good demo. Did you test it after publishing to Power BI Service? I wonder if this solution works only from Power BI Desktop. Once published the pbix file to Power BI Service, how do we know the IP address of the server in Microsoft data center? How do you set the firewall in that case? I would like to create a PBI solution using Google Maps Geocoding API (yes I saw your video), schedule a refresh and export the geocoded addresses back to a table in Azure SQL Database to complete a round-trip data flow back into the DW.
To make google API’s work in the service you need to create a custom connector so you can specify how the service should authenticate. Hope this helps! /Ruth
Hi Ruth, do you know where to get from Azure the information about existing VMs in my tenant(several subscriptions)? information like hostname, primary ip, serial number. uuid, created by, created on, last updated by and so on? i would like to get this information to power bi. thx
Hi you could create a trigger that when you update the table in DB, they only will do it with non duplicated values... In Ruth's example, if you dont want to keep duplicated values in Date and CityID you will create a trigger like this CREATE TRIGGER removeduplicates ON "Sessions" FOR INSERT AS BEGIN SET NOCOUNT ON; DELETE FROM "Session" WHERE Date IN(SELECT inserted.[Date] FROM inserted) and CityID IN(SELECT inserted.[CityID] from inserted) INSERT INTO "Sessions" SELECT * FROM inserted END
Hi I get an error. What to do? Formula.Firewall: Query 'Professionals' (step 'Run R script') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
@@CurbalEN Thank you very much. Now i get this error: DataSource.Error: ADO.NET: R script error. Error in library(RODBC) : there is no package called 'RODBC' Execution halted I already installed > install.packages(RODBC) in R studio...
Hello, May I consult why I follow the steps and the the error message below? can I know how to fix it? error message: DataSource.Error: ADO.NET: R script error. Warning messages: 1: In odbcDriverConnect(connectionString) : [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 2: In odbcDriverConnect(connectionString) : ODBC connection failed Error in sqlSave(myconn, dataset, tablename = "PowerBISQL", rownames = FALSE, : first argument is not an open RODBC channel Execution halted Details: DataSourceKind=R DataSourcePath=R Message=R script error. Warning messages: 1: In odbcDriverConnect(connectionString) : [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 2: In odbcDriverConnect(connectionString) : ODBC connection failed Error in sqlSave(myconn, dataset, tablename = "PowerBISQL", rownames = FALSE, : first argument is not an open RODBC channel Execution halted ErrorCode=-2147467259 ExceptionType=Microsoft.PowerBI.Scripting.R.Exceptions.RScriptRuntimeException
If this the best way to save the snapshot of the data set every time we refresh in Power BI ? Also if this feature already cover the feature which was requested here - ideas.powerbi.com/ideas/idea/?ideaid=69fc3925-b9f3-4d7d-b4fa-e4687d69e0c5 Secondly, is there an automated way to export data from dashboard/reports. Manually it gives us an option but can we automate the export using R script as well from Dashboard/Report - Thanks!!!
