Data Query in Power BI - Part 1

DataQuery - We are using this to connect data in Power BI. For those areas, where we have limitations to import the entire data in our Power BI file i.e. .pbix, we are using DataQuery there. On the runtime, DataQuery connect the source data, run the query model on the source data, and it display those in our Power BI dashboard in visual format.

Let's see what are the data sources supported by DataQuery. Following are the data sources supported by Power BI. While using DataQuery and publish, Power BI Service still creates the dataset (like import), however no data will be save there. Only query parameter with other information will be store which will run on source data.
  • Amazon Redshift
  • Azure HDInsight Spark (Beta)
  • Azure SQL Database
  • Azure SQL Data Warehouse
  • Google BigQuery (Beta)
  • IBM Netezza (Beta)
  • Impala (version 2.x)
  • Oracle Database (version 12 and above)
  • SAP Business Warehouse Application Server
  • SAP Business Warehouse Message Server (Beta)
  • SAP HANA
  • Snowflake
  • Spark (Beta) (version 0.9 and above)
  • SQL Server
  • Teradata Databas


Why we are using DataQuery? For what circumstances we need to use this feature in Power BI? Following are some areas where we have to sue this.

  • Import Data size is too BIG
  • Limitation of placing the data on outside organization as for security and other policy
  • Data is changing very frequently - like SharePrice, News Report, or your daily support maintain tickets
  • Data security - not everyone allows to view company's data - current year year to date balance sheet or sales vole, or bid value
  • Source data is under OLAP - SAP HANA or SAP BW - Specific to these cases, additional modeling restrictions are here
                        No support for calculated columns
                        Additional limitations for measures
                        No support for defining relationships:
                        No Data View
                        Column and measure details are fixed
                        Additional limitations in DAX


Here are some impacts we can see when using DataQuery.
  • Performance on Loading Data - Normal loading data from other data sources is within <=5s
  • Single Data Source - Unable to connect multiple source at the same time
  • Limited Data Transformations - like parsing data from other sources using JSON 
  • Modling limitation - Defining relationship between table; no calculated column; no full flavor of measure etc
  • No built in data hierarchy
  • No clustering
  • Calculated tables not supported
  • Quick insights is not supported
  • Q&A is not supported

More to know about DataQuery - I'll continue this in my next article.

Thank you for visiting this page!

Comments

Post a Comment

Popular posts from this blog

How to fix Azure DevOps error MSB4126

Image
Setting up build and release pipeline always tedious and we face many build related issues. Here is one common error I face couple of times while setting app Web or windows apps in Azure DevOps After placing the code in repos, and creating the build pipeline and run it, you can face the following error ##[error]D:\a\8\s\<your solution file>.sln.metaproj(0,0): Error MSB4126: The specified solution configuration "release|$(BuildPlatform)" is invalid. Please specify a valid solution configuration using the Configuration and Platform properties (e.g. MSBuild.exe Solution.sln /p:Configuration=Debug /p:Platform="Any CPU") or leave those properties blank to use the default solution configuration. To understand what is target platform defined in your project you need to visit or open the solution(.sln) file and review the following sections. In my case, this has been defined as x86 as target platform.                   GlobalSecti
Read more

How to create Custom Visuals in Power BI – Initial few Steps

Image
This post will help you to know the steps to create custom visuals in PowerBI. Microsoft defined them as well at https://docs.microsoft.com/en-us/power-bi/service-custom-visuals-getting-started-with-developer-tools . First things first, and you need to download and install NodeJS , in case if you do not have npm setup in your machine.  Second step is to run couple of npm commands to install Power BI Visual Tools and create & publish the cert. Finally, create your own package and upload with the help of npm. 1.       npm install -g powerbi-visuals-tools 2.       pbiviz --create-cert pbiviz --install-cert 3.       pbiviz new My Visual name – This will create the file under the name of your visual file. 4.      pbiviz start – You need to go to the folder where your custom visual  resides, and run this command. You can change a few things in your pbiviz.json file, like author, display name, icon, style etc. Finally, package your
Read more

How to fix Azure DevOps Error CS0579

Image
During build process of one ASP.NET web application in Azure DevOps environment, I face the error  CS0579 multiple times. Everything looks great to me, but continuous crappy error keep saying issues in AssemblyInfo.cs file. Below are few red error I have face during setup this project. "SharePointServicesWebApp\Properties\AssemblyInfo.cs(8,12): Error CS0579: Duplicate 'AssemblyTitle' attribute" ##[error]SharePointServicesWebApp\Properties\AssemblyInfo.cs(9,12): Error CS0579: Duplicate 'AssemblyDescription' attribute ##[error]SharePointServicesWebApp\Properties\AssemblyInfo.cs(10,12): Error CS0579: Duplicate 'AssemblyConfiguration' attribute ##[error]SharePointServicesWebApp\Properties\AssemblyInfo.cs(11,12): Error CS0579: Duplicate 'AssemblyCompany' attribute I was quite sure there is something coming up during build process and make this as duplicate. Because this build process also provides assembly information other way rou
Read more