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 Established Member
Established 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 Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)