Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mixednuts
Helper II
Helper II

Gateway advises Sql.Database() function is unsupported

In one of the systems on which we have been asked to report, a new copy of the database (SQLServer) is initiated each year (Not my design). This means to report over several years, we have to merge data from several databases into a single reporting table.

 

Traditionally, this would have been achieved by creating a separate query for each database and merging the resulting query results. Unfortunately this means the PowerQuery scripts would have to be edited each year to account for the new database.

 

I tried to make this more dynamic by obtaining a list of the required databases via a Sql.Dtaabases(Server) call and then adding a new column whose definition included .... each Sql.Database(Server,[Database Name],Query="....."). As the databases have the same structure, the same SQL queries could be used.

 

This worked extremely well in the desktop, however when I published the report and attempted to configure the dataset refresh, it reported that the query "contains an unsupported function. That function is Sql.Database"

 

Any idea why? It is supported in the 'Source =' context.

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

Hi @Mixednuts ,

 

Adding data source in Gateway need address and database name, we can expand the result of Sql.Databases() instead of connect them again.

 

1. Expand the data column of Sql.Databases()

10.5.jpg

 

2. Filter the tables you want.

11.jpg

 

3. expand the data of tables after remove unuseful columns

12.jpg

 

But currently the On-Premises Data Gateway (Standard Mode) does not support blank database name.

14.jpg

13.jpg

 

But we can use On-Premises Data Gateway (Personal Mode) as a workaround.

 

15.jpg16.jpg

 

 

All the queries are here for reference:

 

let
    Source = Sql.Databases("localhost"),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Name", "Data", "Schema", "Item", "Kind"}, {"Data.Name", "Data.Data", "Data.Schema", "Data.Item", "Data.Kind"}),
    #"Filtered Rows2" = Table.SelectRows(#"Expanded Data", each ([Data.Name] = "845087" or [Data.Name] = "863829")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"Data.Schema", "Data.Item", "Data.Kind", "Kind"}),
    #"Expanded Data.Data" = Table.ExpandTableColumn(#"Removed Columns", "Data.Data", {"AccountNumber", "CurrencyCode", "id", "DateTime"}, {"Data.Data.AccountNumber", "Data.Data.CurrencyCode", "Data.Data.id", "Data.Data.DateTime"})
in
    #"Expanded Data.Data"


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-lid-msft
Community Support
Community Support

Hi @Mixednuts ,

 

Adding data source in Gateway need address and database name, we can expand the result of Sql.Databases() instead of connect them again.

 

1. Expand the data column of Sql.Databases()

10.5.jpg

 

2. Filter the tables you want.

11.jpg

 

3. expand the data of tables after remove unuseful columns

12.jpg

 

But currently the On-Premises Data Gateway (Standard Mode) does not support blank database name.

14.jpg

13.jpg

 

But we can use On-Premises Data Gateway (Personal Mode) as a workaround.

 

15.jpg16.jpg

 

 

All the queries are here for reference:

 

let
    Source = Sql.Databases("localhost"),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Name", "Data", "Schema", "Item", "Kind"}, {"Data.Name", "Data.Data", "Data.Schema", "Data.Item", "Data.Kind"}),
    #"Filtered Rows2" = Table.SelectRows(#"Expanded Data", each ([Data.Name] = "845087" or [Data.Name] = "863829")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"Data.Schema", "Data.Item", "Data.Kind", "Kind"}),
    #"Expanded Data.Data" = Table.ExpandTableColumn(#"Removed Columns", "Data.Data", {"AccountNumber", "CurrencyCode", "id", "DateTime"}, {"Data.Data.AccountNumber", "Data.Data.CurrencyCode", "Data.Data.id", "Data.Data.DateTime"})
in
    #"Expanded Data.Data"


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you Dong Li.

 

Appreciate the effort you put into this!

 

In summary then, the work around is to use a personal gateway rather than a Standard Gateway? 

 

Is this considered something to be fixed from a Standard Gateway perspective? We don't want to have to permanently operate via personal gateway if at all possible....

Hi @Mixednuts ,

 

We get a solution to create a database without database name by using RESTful API.

 

1. we encode the credential with C# refer to this document:https://docs.microsoft.com/en-us/power-bi/developer/encrypt-credentials

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp2
{
    class Program
    {




        static void Main(string[] args)
        {
            var credentials = "{\"credentialData\":[{\"name\":\"username\", \"value\":\"UserName\"},{\"name\":\"password\", \"value\":\"Password\"}]}";
            var result =    AsymmetricKeyEncryptionHelper.EncodeCredentials(credentials, "Place-Yuor-Exponent-Here", "Place-Yuor-Modulus-Here");

            Console.WriteLine(result);
            Console.ReadLine();
        }


    }

    public static class AsymmetricKeyEncryptionHelper
    {

        private const int SegmentLength = 85;
        private const int EncryptedLength = 128;

        public static string EncodeCredentials(string credentials, string publicKeyExponent, string publicKeyModulus)
        {
            using (RSACryptoServiceProvider rsa = new RSACryptoServiceProvider(EncryptedLength * 8))
            {
                var parameters = rsa.ExportParameters(false);
                parameters.Exponent = Convert.FromBase64String(publicKeyExponent);
                parameters.Modulus = Convert.FromBase64String(publicKeyModulus);
                rsa.ImportParameters(parameters);
                return Encrypt(credentials, rsa);
            }
        }

        private static string Encrypt(string plainText, RSACryptoServiceProvider rsa)
        {
            byte[] plainTextArray = Encoding.UTF8.GetBytes(plainText);

            // Split the message into different segments, each segment's length is 85. So the result may be 85,85,85,20.
            bool hasIncompleteSegment = plainTextArray.Length % SegmentLength != 0;

            int segmentNumber = (!hasIncompleteSegment) ? (plainTextArray.Length / SegmentLength) : ((plainTextArray.Length / SegmentLength) + 1);

            byte[] encryptedData = new byte[segmentNumber * EncryptedLength];
            int encryptedDataPosition = 0;

            for (var i = 0; i < segmentNumber; i++)
            {
                int lengthToCopy;

                if (i == segmentNumber - 1 && hasIncompleteSegment)
                    lengthToCopy = plainTextArray.Length % SegmentLength;
                else
                    lengthToCopy = SegmentLength;

                var segment = new byte[lengthToCopy];

                Array.Copy(plainTextArray, i * SegmentLength, segment, 0, lengthToCopy);

                var segmentEncryptedResult = rsa.Encrypt(segment, true);

                Array.Copy(segmentEncryptedResult, 0, encryptedData, encryptedDataPosition, segmentEncryptedResult.Length);

                encryptedDataPosition += segmentEncryptedResult.Length;
            }

            return Convert.ToBase64String(encryptedData);
        }
    }
}

 

The Exponent and Modulus can be find with the Get Database API

 

2. then we can use the Create Data Source API to create it, the body is like following:

 

{
  "dataSourceType": "SQL",
  "connectionDetails": "{\"server\":\"localhost\",\"database\":\"\"}",
  "datasourceName": "Test100",
  "credentialDetails": {
    "credentialType": "Basic",
    "credentials": "Generated-Credentials",
    "encryptedConnection": "Encrypted",
    "encryptionAlgorithm": "RSA-OAEP",
    "privacyLevel": "None"
  }
}

 

19.jpg20.jpg21.jpg22.jpg

 

But the main point is expanding the result of Sql.Databases() instead of create another column using Sql.Database().


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you again, Dong Li.

 

I understand that I should not use the function in an Add Custom Column scenario.

 

So, to get support through a Standard gateway, I will need to create a Custom Connector as per your instructions. 

 

regards,

John

 

Hi @Mixednuts ,

 

Sorry for did not explained clearly, we did not create a custom connector, we are using RESTful API to add data source as same as adding data source in "Manage Gateway" because we found we cannot add a data source without name of database in "Manage Gateway".  The C# application is to generate the credential that used to add the data source, you can create it in local using Visual Studio or another IDE, then you can just use the "Try it " in API document to call the API.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors