cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kibosh Frequent Visitor
Frequent Visitor

query in query (Dynamic database name)

Hey all,

 

I need to read out some values from SQL-tables generated bij the SCADA program Siemens WINCC.

But that SCADA has a very anoying thing: Each time the project is "compiled and loaded" it changes it's runtime database name with a time-stamp. And it's giving me issues to get it working with Power BI to make reports. I already asked Siemens if there was a way it would not do that, but no luck.

The runtime database will always start with "CC_HMI_xxxx" and ends on "xxxxx_xxxR".

For example: "CC_HMI_7HI6_19_10_21_14_29_48R"

 

I have query's working but still running into issues.

 

Query 1 ("ArchiveTags"):

let
   
    Source = Sql.Databases("SERVER"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "CC_HMI_") and Text.EndsWith([Name], "R")),
    Name = #"Filtered Rows"[Name],
    dbName = Text.Trim(Lines.ToText(List.FirstN(Name,1))),
    
    Source2 = Sql.Database("SERVER\WINCC", dbName),
    dbo_ArchiveTags = Source2{[Schema="dbo",Item="Archive"]}[Data]
in
    dbo_ArchiveTags

The above query runs ok but when powerbi is started, it asks for the credentials. It adds the old database name which it last accessed. So it is trying to connect to a db that no longer exist. Is there a way to add the credentials inside the query?

 

Query 2 ("ArchiveValues"):

let
   
    Source3 = Sql.Databases("SERVER\WINCC"),
    #"Filtered Rows" = Table.SelectRows(Source3, each Text.StartsWith([Name], "CC_HMI_") and Text.EndsWith([Name], "R")),
    Name = #"Filtered Rows"[Name],
    dbName = Text.Trim(Lines.ToText(List.FirstN(Name,1))),

    Source4 = OleDb.DataSource("provider=WinCCOLEDBProvider.1;data source=SERVER\WINCC;catalog=" & dbName & ";mode=Read", [Query="TAG:R,(1;2;3;4),'0000-00-00 00:30:00.000','0000-00-00 00:00:00.000'"])
in
    Source4

Second query uses a different provider from Siemens itself to read out the actual tag values. The archived values are segmented and compressed so I have to use the "WinCCOLEDBProvider". 

But when I do "apply changes" I get the error "Sequence contains more than one element"?

 

Someone has a thought how to proceed?

 

2 REPLIES 2
artemus Member
Member

Re: query in query (Dynamic database name)

Unfortunatly the only way to retrieve credentials (and reuse them) for a conection is to write your own Power Bi connector. These are not supported in the online version.

 

You could also set up a web server which forwards the requests.

Highlighted
Kibosh Frequent Visitor
Frequent Visitor

Re: query in query (Dynamic database name)

Thx for the reply,

 

Sounds to complex for me...

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)