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
Highlighted
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.

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 Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,070)