none
How to change Firewall Settings in AAS to overcome: Formula.Firewall: Query 'xxxxxx' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this da RRS feed

  • Question

  • I am getting this error in M query:

    Formula.Firewall: Query 'FullTranslation_Final/Partition/Partition' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

    I realize this is because I am invoking a function in a query that uses (merges) different data sources. I have tried creating a secondary query off my first query (so merge the data sources in an intermediate query, then create a new query off of the initial merge query), but this is not working - the error persists.

    I read that you can change the fire wall settings to "always Ignore" at the global level for security privacy level (in Power BI):

    https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-Foo-references-other-queries-so-it-may/m-p/21373/highlight/false#M6600

    But I cannot figure out how to change this setting in AAS.

    Any suggestions on how to change this setting in VS AAS?

    Or suggestions on how to write the query so I can invoke my custom function that uses a web service (MSFT Cognitive Services Translator Text).

    Thanks!

    Monday, October 22, 2018 5:20 PM

Answers

  • Hi Julian. You said 'I have enabled the "Enable Fast Combine" option for the model'. How were you able to do this? Enabling fast combine should prevent these errors from happening.

    To avoid this error without Enabling Fast Combine, you'll need to ensure that the query where you add the custom column that calls the cognitive services API is only accessing data sources, and not referencing any other queries. So, for example, this might mean that you need to combine Translation, "Language_Code/Partition", and Final_Language into a single query. You can do this by pasting their definitions into the Advanced Editor and modifying them there. For example:

    let
      Translation_Source = (pasted from the Translation query),
      Translation_NextStep = (pasted from the Translation query),
      ...
      LangCodePartition_Source = (pasted from the lang code/partition query),
      LangCodePartition_NextStep = ...,
      ...
      Source = Table.NestedJoin(Translation_FinalStep,{"JoinHack"},LangCodePartition_FinalStep,{"JoinHack"},"Language_Code/Partition",JoinKind.LeftOuter),
      #"Expanded Language_Code/Partition" = Table.ExpandTableColumn(Source, "Language_Code/Partition", {"Language", "Code"}, {"Language", "Code"})
    in
        #"Expanded Language_Code/Partition"

    Let me know if you need help trying this out.

    EDIT: Another option would be to add the cognitive services call as early as possible. I don't know if you need data from both Translation and Language_Code/Partition in order to call it or not, but if you only need data from one of them, then you could modify your current queries to add the custom cognitive services column directly to the relevant query, before Merging it.

    Ehren


    Wednesday, November 7, 2018 12:08 AM
    Owner

All replies

  • Hi Julian. Can you share the formula text of your queries (with any sensitive info removed, of course)? I may be able to help you restructure them to avoid the firewall error.

    Ehren

    Friday, November 2, 2018 10:14 PM
    Owner
  • Ehren,

    Thanks for you help. Here are the steps I have taken:

    1) I created 2 queries/tables to make a "language look-up dictionary" I cross-joined those tables to make the table below which has the following columns: DemoID | English | JoinHack | Language | Code (language code)

    Here is the code to make the Final_Language Table:

    let
        Source = Table.NestedJoin(Translation,{"JoinHack"},#"Language_Code/Partition",{"JoinHack"},"Language_Code/Partition",JoinKind.LeftOuter),
        #"Expanded Language_Code/Partition" = Table.ExpandTableColumn(Source, "Language_Code/Partition", {"Language", "Code"}, {"Language", "Code"})
    in
        #"Expanded Language_Code/Partition"

    to this table I ADD a column that is called from a function (this new column is supposed to do the language translation), here is the code for the function that is being invoked:

    let
        Source = (Source as text, To as text, From as nullable text) as table =>
    let
        JsonRecords = Text.FromBinary(Json.FromValue(Source)),
        JsonRequest = "{""documents"": " & JsonRecords & "}",
        JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),
        Response =
    Web.Contents(
    "https://api.cognitive.microsofttranslator.com/translate?api-version=3.0",
    [
    Headers=
    [#"Ocp-Apim-Subscription-Key"="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
     #"Content-Type"="application/json", 
     #"Accept"="application/json"],
    Content=JsonContent
    ]),

       JsonResponse = Json.Document(Response,1252)
    in
        JsonResponse
    in
        Source

    When I invoke this function, I get the firewall error I mentioned on the table.

    I have enabled the "Enable Fast Combine" option for the model to reduce the level of security and privacy restrictions, which I have read can help - but I cannot find where to set the Global privacy settings (as Power BI users have mentioned).

    I realize that changing the structure of the query might help - I would take your feedback if you can assist. Thank you.

    Tuesday, November 6, 2018 9:06 PM
  • Hi Julian. You said 'I have enabled the "Enable Fast Combine" option for the model'. How were you able to do this? Enabling fast combine should prevent these errors from happening.

    To avoid this error without Enabling Fast Combine, you'll need to ensure that the query where you add the custom column that calls the cognitive services API is only accessing data sources, and not referencing any other queries. So, for example, this might mean that you need to combine Translation, "Language_Code/Partition", and Final_Language into a single query. You can do this by pasting their definitions into the Advanced Editor and modifying them there. For example:

    let
      Translation_Source = (pasted from the Translation query),
      Translation_NextStep = (pasted from the Translation query),
      ...
      LangCodePartition_Source = (pasted from the lang code/partition query),
      LangCodePartition_NextStep = ...,
      ...
      Source = Table.NestedJoin(Translation_FinalStep,{"JoinHack"},LangCodePartition_FinalStep,{"JoinHack"},"Language_Code/Partition",JoinKind.LeftOuter),
      #"Expanded Language_Code/Partition" = Table.ExpandTableColumn(Source, "Language_Code/Partition", {"Language", "Code"}, {"Language", "Code"})
    in
        #"Expanded Language_Code/Partition"

    Let me know if you need help trying this out.

    EDIT: Another option would be to add the cognitive services call as early as possible. I don't know if you need data from both Translation and Language_Code/Partition in order to call it or not, but if you only need data from one of them, then you could modify your current queries to add the custom cognitive services column directly to the relevant query, before Merging it.

    Ehren


    Wednesday, November 7, 2018 12:08 AM
    Owner