![\"Power](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/power-bi-logo-310x310.png\"){kind=logo}
<\/a>If you are looking around to find connectivity options to get\u00a0Amazon AWS data in Power BI (e.g. Read from Amazon S3 files (CSV, JSON, XML) or get AWS API data such as Billing Data by calling REST API) then unfortunately as of now Power BI doesn’t support it natively.<\/p>\nIn this article, you will learn\u00a0\u00a0How to read Amazon S3 data in Power BI<\/strong>\u00a0(Using S3 CSV Driver<\/a>) and in later section we will also learn\u00a0How to import data from AWS API Calls (e.g. Billing, EC2, S3, DynamoDB)<\/strong>. We will use ZappySys REST \/ JSON Driver<\/a> to access AWS data without any ETL or Programming.<\/p>\n In our previous article, we discussed how to access REST API data in Power BI (Read JSON \/ XML Web API)<\/a>. Now let’s learn how to connect to AWS in Power BI<\/strong>.<\/p>\n In this article, We will explore interesting scenarios such as below. It will help you to connect\u00a0Amazon AWS in Power BI and import data from AWS without doing any ETL or programming<\/strong>.<\/p>\n So let’s get started…<\/p>\n In this section we will look at step by step approach to load Amazon S3 data in Power BI. Your file can be compressed\u00a0 (GZip, Zip) or un-compressed. We will use ZappySys Amazon S3 driver for CSV Files<\/a> for demo but you can also use Amazon S3 JSON Driver<\/a> or Amazon S3 Driver for XML Files<\/a>\u00a0same way for reading different file formats.<\/p>\n <\/p>\n <\/p>\n In the previous section, we configured ODBC DSN for AWS API call and added the ZappySys drivers in the ODBC Driver Administrator with information to connect to AWS S3 Files. We queried S3 File using ZappySys Amazon S3 Driver for CSV Files. Now we will open Power BI and import the information from ODBC\u00a0 DSN we defined earlier.<\/p>\n Get data in power bi<\/p><\/div><\/li>\n Select odbc in Power BI<\/p><\/div><\/li>\n Select Windows Credentials to connect to zappysys driver<\/p><\/div><\/li>\n In our previous section we saw how to read Amazon S3 data using native ZappySys S3 Drivers (For CSV , JSON and XML Files), this approach is preferred way to read S3 Files for sure because it gives you UI to browse files, it gives you ability to read from multiple files and many more S3 specific features.<\/p>\n However in some cases you have to call REST API in a manual way using REST API driver rather than S3 Driver. In next section we will see more interesting use case of calling AWS API (e.g. Billing \/ Cost API).<\/p>\n Now lets look at steps – How to import Amazon S3 data Power BI by calling REST API manually. Steps listed below are almost identical for XML or JSON data format except for selecting Driver Type (i.e. JSON, XML, CSV).<\/p>\n To consume S3 data in Power BI, first\u00a0we have to create ODBC DSN using ZappySys Driver (XML or JSON). Perform the following steps.<\/p>\n Open odbc data source<\/p><\/div><\/li>\n Add new data source<\/p><\/div><\/li>\n Create DSN using ZappySys ODBC Driver for XML \/ SOAP API<\/p><\/div><\/li>\n Configure Amazon S3 Connection – Read from XML File or JSON File<\/p><\/div>\n Select Filter (Flatten the hierarchy to extract data from nested structure)<\/p><\/div>\n <\/li>\n Preview data from AWS S3 File (XML Data) – Use ZappySys XML Driver to Connect to AWS API<\/p><\/div><\/li>\n <\/p>\n Now let’s look at how to import Amazon S3 data in Power BI using ZappySys XML driver.<\/p>\n In the previous section, we configured ODBC DSN for AWS API call and added the ZappySys drivers in the ODBC Driver Administrator with information to connect to AWS REST API. We queried S3 File using ZappySys XML Driver. Now we will open Power BI and import the information from ZappySys JSON Driver via ODBC connectivity option in Power BI.<\/p>\n Get data in power bi<\/p><\/div><\/li>\n Select odbc in Power BI<\/p><\/div><\/li>\n Import JSON \/ REST API data in Power BI using SQL Query Mode<\/p><\/div>\n <\/li>\n Select Windows Credentials to connect to zappysys driver<\/p><\/div><\/li>\n <\/p>\n So far you have seen how to connect to S3 File. Now let’s look at another interesting example to call AWS API and display information in Power BI dashboard. For example purpose, we are going to call this AWS Billing and Cost Analysis API<\/a>. Perform following steps.<\/p>\n Configure AWS API Connection for Cost \/ Billing API Data – Use ZappySys JSON Driver<\/p><\/div><\/li>\n <\/li>\n Query AWS Cost \/ Billing Data using API in Power BI (Use ZappySys JSON Driver)<\/p><\/div><\/li>\n Import AWS Billing \/ Cost Data in Power BI (Call AWS REST API)<\/p><\/div><\/li>\n <\/p>\n Now let’s look at another scenario. Assume that someone from other team has written a Lambda Function which exposes certain data in JSON format which you want to display that in your Dashboard. Refer to this Lambda API documentation<\/a> to learn more about API syntax.<\/p>\n Here is an example query to Call Lambda Function (submit input data in JSON and receive data in JSON format)<\/p>\n <\/p>\n Now let’s look at AWS EC2 Service API (Virtual Machine Service). This API is in XML format so you have to use ZappySys XML Driver.\u00a0 Below query calls this EC2 API<\/a> to get list of all instances.<\/p>\n <\/p>\n <\/p>\n Get EC2 Instance List in Power BI (Use ZappySys XML Driver)<\/p><\/div>\n <\/p>\n Now let’s look at how to Query S3 File System in Power BI. For example if you have to prepare dashboard to show Total Data Size and File count in a given Bucket then you can write following Query. For more information check Bucket List API Help<\/a><\/p>\n Get File Size and Count for a given S3 Bucket<\/strong><\/p>\n To list buckets you can use following query<\/p>\n List S3 Buckets<\/strong><\/p>\n <\/p>\n Check below article to learn various techniques to debug and craft AWS API Request Body \/ Headers and URL.<\/p>\n How to call Amazon AWS API using SSIS (EC2, Lambda, API Gateway, SQS)<\/a><\/p><\/blockquote>\n\n
\n
Requirements<\/span><\/h2>\n
\n
Import Amazon S3 data in Power BI (CSV, JSON or XML Files)<\/h2>\n
Configure ODBC DSN for Amazon S3 Files ( JSON \/ XML \/ CSV )<\/span><\/h3>\n
\n
<\/li>\n
\n<\/li>\n
\n<\/li>\n
\n<\/li>\n
\n<\/li>\n
\n<\/li>\n
\n<\/li>\nLoad Amazon S3 Data in Power BI – using ODBC Driver Connection<\/h3>\n
\n
<\/a><\/a>\/*--------- Amazon S3 Driver - Read CSV File Example - Override UI credentials in WITH clause ----------*\/ \r\nSELECT * FROM $ \r\nWITH ( \r\n Src='Northwind-Bucket\/Orders*.csv' --use wildcard * for pattern search\r\n -- ,AccessKey='AKIAxxxx' --not needed if you set in DSN connection\r\n -- ,SecretKey='a1wxxxxxxxxxxxxxx' --not needed if you set in DSN connection\r\n -- ,RegionName='us-west-1'\r\n -- ,ColumnDelimiter='|'\r\n -- ,HasColumnHeaderRow='False'\r\n)<\/pre>\n
<\/a><\/li>\n<\/a>AWS API Example –\u00a0\u00a0Call Amazon S3 API in Power BI<\/span><\/h2>\n
Configure ODBC DSN for REST API ( JSON \/ XML \/ CSV )<\/span><\/h3>\n
\n
<\/a><\/a><\/a>https:\/\/zs-dump1.s3.amazonaws.com\/store_001.xml\r\n--OR--\r\nhttps:\/\/zs-dump1.s3.amazonaws.com\/mysubfolder\/store_001.xml\r\n--OR--\r\nhttps:\/\/zs-dump1.s3.us-east-1.amazonaws.com\/mysubfolder\/store_001.xml<\/pre>\n
<\/a><\/a><\/a>Import AWS S3 File or AWS API data in Power BI dataset<\/h3>\n
\n
<\/a><\/a>SELECT \"author\", \"title\", \"genre\", \"price\", \"publish_date\", \"description\", \"tag\"\r\nFROM $\r\nWITH(\r\n\t Src='https:\/\/zs-dump1.s3.amazonaws.com\/store_001.xml'\r\n\t,Filter='$.store.book[*]'\r\n\t,ElementsToTreatAsArray='book'\r\n)<\/pre>\n
<\/a><\/a>AWS API Example –\u00a0Import Amazon AWS Cost \/ Billing data in Power BI<\/h2>\n
\n
https:\/\/ce.us-east-1.amazonaws.com<\/pre>\n<\/li>\n
<\/a>{\r\n \"TimePeriod\": {\r\n \"Start\":\"2018-01-01\",\r\n \"End\": \"2018-06-15\"\r\n },\r\n \"Granularity\": \"MONTHLY\",\r\n \"Filter\": { \r\n \"Dimensions\": {\r\n \"Key\": \"SERVICE\",\r\n \"Values\": [\r\n \"Amazon Simple Storage Service\",\r\n \"Amazon DynamoDB\",\r\n \"Amazon Redshift\",\r\n \"AWS Key Management Service\"\r\n ]\r\n }\r\n },\r\n \"GroupBy\":[\r\n {\r\n \"Type\":\"DIMENSION\",\r\n \"Key\":\"SERVICE\"\r\n },\r\n {\r\n \"Type\":\"TAG\",\r\n \"Key\":\"Environment\"\r\n }\r\n ],\r\n \"Metrics\":[\"BlendedCost\", \"UnblendedCost\", \"UsageQuantity\"]\r\n}<\/pre>\n
\nSimple-Query to Extract AWS Billing \/ Cost Information<\/strong><\/p>\nSELECT \r\n \"p_ResultsByTime_TimePeriod_Start\" as BillStartDate,\r\n \"p_ResultsByTime_TimePeriod_End\" as BillEndDate,\r\n json_array_first(\"Keys\") as ServiceName,\r\n \"Metrics_BlendedCost_Amount\" as BillAmount,\r\n \"Metrics_UsageQuantity_Amount\" as Quantity\r\nFROM [ResultsByTime_x_Groups] \r\nORDER BY p_ResultsByTime_TimePeriod_Start<\/pre>\n<\/li>\n
\nCustom-Query to Extract AWS Billing \/ Cost Information (WITH clause)<\/strong><\/p>\nSELECT \r\n \"p_ResultsByTime_TimePeriod_Start\" as BillStartDate,\r\n \"p_ResultsByTime_TimePeriod_End\" as BillEndDate,\r\n json_array_first(\"Keys\") as ServiceName,\r\n \"Metrics_BlendedCost_Amount\" as BillAmount,\r\n \"Metrics_UsageQuantity_Amount\" as Quantity\r\nFROM $\r\nORDER BY p_ResultsByTime_TimePeriod_Start\r\nWITH(\r\n\t Src='https:\/\/ce.us-east-1.amazonaws.com'\r\n\t,DataConnectionType='OAuth'\r\n\t,ScopeSeparator='{space}'\r\n\t,ServiceProvider='AmazonAWS'\r\n\t--,ClientId='AKIAxxxxxxxxxxxxxxx'\r\n\t--,ClientSecret='lPi+XQxxxxxxxxxxxxxxxx'\r\n\t,UseCustomApp='True'\r\n\t,Filter='$.ResultsByTime[*].Groups[*]'\r\n\t,RequestData='{\r\n \"TimePeriod\": {\r\n \"Start\":\"2018-01-01\",\r\n \"End\": \"2018-06-15\"\r\n },\r\n \"Granularity\": \"MONTHLY\",\r\n \"Filter\": { \r\n \"Dimensions\": {\r\n \"Key\": \"SERVICE\",\r\n \"Values\": [\r\n \"Amazon Simple Storage Service\",\r\n \"Amazon DynamoDB\",\r\n \"Amazon Redshift\",\r\n \"AWS Key Management Service\"\r\n ]\r\n }\r\n },\r\n \"GroupBy\":[\r\n {\r\n \"Type\":\"DIMENSION\",\r\n \"Key\":\"SERVICE\"\r\n },\r\n {\r\n \"Type\":\"TAG\",\r\n \"Key\":\"Environment\"\r\n }\r\n ],\r\n \"Metrics\":[\"BlendedCost\", \"UnblendedCost\", \"UsageQuantity\"]\r\n}'\r\n\t,Header='X-Amz-Target: AWSInsightsIndexService.GetCostAndUsage || Content-Type: application\/x-amz-json-1.1'\r\n\t,RequestMethod='POST'\r\n)\r\n<\/pre>\n<\/li>\n<\/a><\/a>AWS API Example – Import Data From AWS Lambda in Power BI<\/h2>\n
SELECT * FROM $\r\nWITH(\r\n\t Src='https:\/\/lambda.us-east-1.amazonaws.com\/2015-03-31\/functions\/HelloWorld\/invocations'\r\n\t,RequestMethod='POST'\r\n\t,RequestData='{\"id\":1}'\r\n\t,RequestContentTypeCode='ApplicationJson'\r\n\t\r\n\t\/*** Uncomment to override DSN settings\t \r\n\t,DataConnectionType='OAuth'\r\n\t,ScopeSeparator='{space}'\r\n\t,ServiceProvider='AmazonAWS'\r\n\t--,ClientId='AKxxxxxxxxxxx'\r\n\t--,ClientSecret='lPi+Xxxxxxxxxxxxxxxxxxxxxxx'\r\n\t,UseCustomApp='True'\r\n\t***\/\r\n)<\/pre>\nAWS API Example – Get EC2 VM information in Power BI<\/h2>\n
SELECT * FROM $\r\nWITH(\r\n\t ElementsToTreatAsArray='item'\r\n\t,Src='https:\/\/ec2.us-east-1.amazonaws.com\/'\r\n\t,Filter='$.DescribeInstancesResponse.reservationSet.item[*].instancesSet.item[*]'\r\n\t,RequestData='Action=DescribeInstances&Version=2016-11-15'\r\n\t,RequestMethod='POST'\r\n\t\/*\r\n\t,DataConnectionType='OAuth'\r\n\t,ScopeSeparator='{space}'\r\n\t,ServiceProvider='AmazonAWS'\r\n\t,ClientId='AKIAxxxxxxxxxxxxxxxxxxx'\r\n\t,ClientSecret='lPi+XQvxxxxxxxxxxxxxxxxxxxxxxxxx'\r\n\t,UseCustomApp='True'\r\n\t*\/\r\n)<\/pre>\n<\/a>AWS API Example – Get AWS S3 Buckets and Files Count \/ Size in Power BI<\/h2>\n
SELECT count(*) as TotalFiles,sum(Size)\/1024\/1024 as TotalMB FROM $\r\nWITH(\r\n\t ElementsToTreatAsArray='Contents'\r\n\t,Src='https:\/\/zs-dump1.s3.amazonaws.com\/?list-type=2&prefix=&max-keys=1000'\r\n\t,Filter='$.ListBucketResult.Contents[*]'\r\n\t,NextUrlAttributeOrExpr='$.ListBucketResult.NextContinuationToken'\r\n\t,NextUrlSuffix='&continuation-token=<%nextlink_encoded%>'\r\n\t\r\n\t\/**** Uncomment below to use override DSN values\r\n\t,DataConnectionType='OAuth'\r\n\t,ScopeSeparator='{space}'\r\n\t,ServiceProvider='AmazonAWS'\r\n\t,ClientId='AKIAxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'\r\n\t,ClientSecret='lPi+Xcxxxxxxxxxxxxxxxxxxxxxxxx'\r\n\t,UseCustomApp='True'\t\r\n\t,RequestMethod='GET'\r\n\t***\/\r\n)<\/pre>\nSELECT * FROM $\r\nWITH(\t \r\n\t Src='https:\/\/s3.amazonaws.com'\r\n\t,Filter='$.ListAllMyBucketsResult.Buckets.Bucket[*]'\r\n\t,ElementsToTreatAsArray='Buckets'\r\n\r\n\t\/**** Uncomment below to use override DSN values\r\n\t,DataConnectionType='OAuth'\r\n\t,ScopeSeparator='{space}'\r\n\t,ServiceProvider='AmazonAWS'\r\n\t,ClientId='AKIAxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'\r\n\t,ClientSecret='lPi+Xcxxxxxxxxxxxxxxxxxxxxxxxx'\r\n\t,UseCustomApp='True'\t\r\n\t,RequestMethod='GET'\r\n\t***\/\r\n)<\/pre>\nDebugging \/ Crafting AWS API Call Request<\/h2>\n