Forum Discussion

jirineoral's avatar
jirineoral
Post Patron
10 years ago

Server name parameterization causes refresh issues

Hi,
last Power BI desktop update introduced parameters. I changed hard coded Server name in pbix and replaced it with parameter SERVER_NAME. After this change refreshes through Enterprise Gateway took unusually long time and finally resulted in error for unknown reason. Took me 2 days to resolve this (did other changes as well)
Server name as a parameter is a must for moving from dev to production.  

Did anyone encounter the same issues? I already posted an idea to fix so please vote if you find this useful

https://ideas.powerbi.com/forums/360879-issues/suggestions/14151696-server-name-parameterization-causes-refresh-issues


Thanks

Jiri

General Comment

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    As a work-a-round, you might try something like this:

     

    let
    DevSource = Csv.Document(File.Contents("C:\temp\powerbi\test_dev.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    ProdSource = Csv.Document(File.Contents("C:\temp\powerbi\test_prod.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    
    #"Promoted Headers Dev" = Table.PromoteHeaders(DevSource),
    #"DevResult" = Table.TransformColumnTypes(#"Promoted Headers Dev",{{"Column1", type text}, {"Column2", Int64.Type}}),

    #"Promoted Headers Prod" = Table.PromoteHeaders(ProdSource),
    #"ProdResult" = Table.TransformColumnTypes(#"Promoted Headers Prod",{{"Column1", type text}, {"Column2", Int64.Type}}),

    result = 
        if Environment = "Dev"
        then #"DevResult"
        else #"ProdResult"
in
    #"result"

    This uses a parameter called "Environment" that can either be "Dev" or "Prod"

    • jirineoral's avatar
      jirineoral
      Post Patron
      10 years ago

      Thanks Greg_Deckler,

      but I don't need workarounds. I already contacted support with this problem, I was just curious, if anyone has the same problem as I do. Your solution requires rewriting M code and if I need to to this, I can rewrite the server name directly.

       

      Jiri

  • dimazaid's avatar
    dimazaid
    Microsoft Employee
    10 years ago

    hi jirineoral,
    Could you elaborate more about the problem/suggestion? Was it a Gateway issue that was causing this refresh to fail?

    • jirineoral's avatar
      jirineoral
      Post Patron
      10 years ago

      Hi dimazaid,

      I'm importing data from SSAS Multidimensional. In my .pbix file I had server name as constant for all my MDX queries. Refresh worked ok. After introduction of parameters. I replaced server name with parameter passing the same value of registered data source in Power BI Enterprise Gateway. Refreshes started to fail. Because I did multiple steps lately (updated Gateway as well) I didn't know what the problem is.

      Yesterday I simulated same steps on demo data before changing server name to parameter value. Refresh works, after change fails witch unknown error in EG.

      So I suspect that this is problem of EG that it somehow can't read properly parameter value? Not sure.

       

      Jiri

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        10 years ago

        jirineoral - While I realize it is a workaround, it would be interesting if you tested the techique I posted to see if the EG's problem with parameters is a general problem or if it is specific to the use of the parameter as the server name/connection string.