Power bi amazon

Power bi amazon DEFAULT

Review

“The goal of this book is to expose readers to the various tools in Microsoft’s self-service BI stack, built over Excel 2016, comprising Power Pivot, Power BI Desktop, Power Query, and DAX to enable them to analyze and gain insight into their data. … The book is easy to read and use because of its lucid style. It should interest Excel users looking to enhance their data analysis skills.” (Computing Reviews, August, 2017) 

From the Back Cover

Analyze your company’s data quickly and easily using Microsoft’s latest tools. Build scalable and robust data models to work from. Learn to clean and combine different data sources effectively. Create compelling visualizations and share them with your colleagues.

Author Dan Clark takes you through each topic using step-by-step activities and plenty of screen shots to help familiarize you with the tools. This second edition includes new material on advanced uses of Power Query, along with the latest user guidance on the evolving Power BI platform. Beginning Power BI is your hands-on guide to quick, reliable, and valuable data insight.

What You Will Learn:

  • Simplify data discovery, association, and cleansing
  • Create solid analytical data models
  • Create robust interactive data presentations
  • Combine analytical and geographic data in map-based visualizations
  • Publish and share dashboards and reports

About the Author

Dan Clark is a senior business intelligence (BI)/programming consultant specializing in Microsoft technologies. He is focused on learning new BI/data technologies and on training others how to best implement the technology. Dan has published several books and numerous articles on .NET programming and BI development. He is a regular speaker at various developer/database conferences and user group meetings and enjoys interacting with the Microsoft developer and database communities. In a previous life, he was a physics teacher; he is still inspired by the wonder and awe of studying the universe and figuring out why things behave the way they do.

Read more

Sours: https://www.amazon.com/Beginning-Power-Practical-Self-Service-Analytics/dp/1484225767

Integrate Power BI with Amazon Redshift for insights and analytics

by Vu Le and Po Hong | on | in Amazon Redshift, Amazon Redshift, Analytics, AWS Big Data, Database, Top Posts | Permalink | Comments |  Share

Amazon Redshift is a fast, fully managed, cloud-native data warehouse that makes it simple and cost-effective to analyze all your data using standard SQL and your existing business intelligence (BI) tools. Microsoft Power BI is a business analytics service that delivers insights to enable fast, informed decisions. With Power BI, you can perform ad-hoc query analysis, visualize data, and create-user friendly dashboards.

This post demonstrates how to integrate Power BI with Amazon Redshift to deliver powerful visualization and insights. For a more integrated experience, AWS offers Amazon QuickSight – a fully managed BI service with secure private VPC connectivity, native ML-insights and pay-per-session pricing to deliver insights to everyone in the organization.

Solution architecture

This post provides code artifacts to help you create a big data environment on AWS from scratch. You can automatically provision a new Amazon Redshift data warehouse in under an hour without much technical depth required by using the AWS CloudFormation template and code examples provided.

The post also demonstrates how to configure integration for the most common deployment scenarios. For example, how do you connect Power BI to AWS services using ODBC/JDBC drivers? How do you connect to AWS services that are deployed behind a private network? What credentials do you use to connect to AWS services? This post addresses and answers these questions in the subsequent sections.

The following diagram shows the solution architecture deployed to AWS. All components inside the AWS Cloud boundary are deployed automatically using an AWS CloudFormation template to allow you to reproduce this solution quickly using your AWS account.

When deployed, the solution contains the following components:

  • Networking infrastructure that includes VPC, public and private subnets, security groups, internet gateway, NAT Gateway, and route tables
  • Linux EC2 instance provisioned in a public subnet to generate sample data
  • Windows Server EC2 instance to host Power BI Desktop
  • Windows Server EC2 instance to act as an on-premises data gateway that handles the communication between Power BI and Amazon Redshift
  • An Amazon Redshift cluster deployed in a private subnet
  • IAM user and roles with permissions to access Amazon S3 and Amazon Redshift

Prerequisites

To complete the steps in this post, you need the following prerequisites:

  • AWS account – You need an account to follow the instructions and test it with minimal cost.
    • If you are creating your account for the first time, choose the region.
    • Create a key pair for the selected Region. For more information, see Amazon EC2 Key Pairs.
  • Power BI tenant – You can test all the described Power BI functionalities with minimum to no cost with the following:
    • Power BI Pro license
    • Access to your Power BI admin portal

Creating and configuring your development environment

Before you can create Power BI visualizations in AWS, you need to load a fully working development environment with sample data. This section contains instructions to create and configure that environment from scratch. After completing all the deployment steps in this section, you have an AWS infrastructure with all the integration hooks between AWS and Power BI fully configured.

To create this environment, execute the following high-level tasks:

  1. Run an AWS CloudFormation template to provision the initial development environment.
  2. SSH into an Amazon EC2 Linux instance to generate a sample dataset.
  3. Configure the data warehouse by creating and loading data into Amazon Redshift tables.
  4. Install and configure the Power BI Desktop.
  5. Configure a data gateway in Power BI.
  6. Install the Power BI mobile app so you can consume the visuals from your phone.

To make the deployment quick and easy, this post automates much of the deployment steps through the use of an AWS CloudFormation template. For the tasks that could not be automated, the post provides detailed instructions along with actual code examples. You can find the template and relevant code in the GitHub repo. Remember to clone this GitHub repo to a local working folder because you need to reference these artifacts as you walk through the deployment steps in this section.

Provisioning a development environment

In this step, you run an AWS CloudFormation template to provision the initial infrastructure. Complete the following steps:

  1. On the AWS CloudFormation console, under Region, choose US East (N. Virginia).You can choose other Regions, but you need to change the EC2 AMI IDs in the AWS CloudFormation template parameters to match the chosen Region.
  2. Choose Create stack.
  3. Under Specify template, select Upload a template file.
  4. Click on Choose file.
  5. Select ProvisionDevEnv.yaml file from your working folder.
  6. Choose Next.
  7. Under Specify stack details, provide the following information:
    • Stack name – Enter a name for your CloudFormation stack.
    • AmazonLinuxImageID – Use the default value if you are running in US East (N. Virginia). Otherwise, provide the Amazon Linux AMI ID for your chosen Region. An easy way to find the AMI ID is to go to the Amazon EC2 console and launch a new instance. The Amazon Linux AMI ID should be listed on the first page. Use Amazon Linux, not Amazon Linux 2.
    • EC2KeyPair – Enter the name of your key pair for your Region.
    • MyIPAddress – Enter your public IP address in CIDR format, for example, . To find your public IP address, search online for .
    • RedshiftMasterUserPassword – Enter a password for your Amazon Redshift cluster.
    • RedshiftMasterUsername – Use the default username or provide your own.
    • WindowsServerImageID – Use the default value if you are running in US East (N. Virginia). Otherwise, provide the Microsoft Windows Server 2019 Base AMI ID for your Region.
    • For all other options, keep the default values.
  1. Choose Next.
  2. On the Configure stack options page, choose Next.
  3. On the Review page, select the “I acknowledge…” check box.
  4. Choose Create stack.

Wait for the CloudFormation stack creation to complete, which can take approximately 10 minutes. When the CloudFormation stack is complete, choose Stack Outputs. In this section, you can find all the keys and values of the resources that you need to reference later in the deployment process.

Generating an sample dataset

Now that you have provisioned the initial infrastructure, let us generate the sample data to be use in the Power BI visualizations. For more information about how to generate the sample dataset, see GenerateSampleDataset.txt in your working folder.

Replace the placeholders in this file with the following corresponding values from the AWS CloudFormation Stack Outputs section:

  • [KeyPairFileName] – Replace with the value of the template input parameter
  • [EC2IPAddress] – Replace with the public IP address of this EC2 instance with the key
  • [S3BucketName] – Replace with the actual S3 bucket name

After you generate the dataset and copy it to your S3 bucket, terminate the EC2 instance with the name . You don’t need it anymore.

Configuring an Amazon Redshift cluster

In this step, you run a series of SQL commands to create tables in your Amazon Redshift cluster. After that, you load the data you generated earlier from Amazon S3 into the Amazon Redshift tables. Complete the following steps:

  1. On the Amazon Redshift console, choose Query Editor.
  2. Connect to the cluster by providing the database name, database user, and password that were provided as input parameters to the CloudFormation stack.
  3. From the schema drop-down menu, choose public.
  4. Open the file CreateRedshiftTables.txt in your working folder and run each SQL statement, one at a time, in the Query Editor.
  5. Run the commands in the file CopyFromS3ToRedshift.txt, one at a time, to load sample data into the tables you just created. Replace the following placeholders with the actual values from the AWS CloudFormation Stack Outputs section
    • [S3BucketName] – Replace with actual S3 bucket name.
    • [RedshiftAccessRoleName] – Replace with the IAM role name.
  6. Confirm that you see the following tables listed under the public schema. Do a select on each table to see how many rows you have for the following tables:
    • : 1, 500,000
    • : 59,986,052
    • : 25
    • : 15,000,000
    • : 2,000,000
    • : 8,000,000
    • : 5
    • : 100,000

Installing and configuring Power BI Desktop

In this step, you connect using Remote Desktop Protocol (RDP) into the Windows Server jump box and install Power BI Desktop, which you use later to create visualizations from the data that you pulled from Amazon Redshift. For more information, see Connecting to Your Windows Instance. Complete the following steps:

  • From your local machine, RDP to the Windows Server Jump Box. You can get the public IP address of the jump box from the CloudFormation Stack Outputs tab or from your EC2 Console.
  • Remember to use the EC2KeyPair you specified in the CloudFormation template to decrypt the administrator password by using the EC2 console:
    • Open the Amazon EC2 console, and then choose Instances.
    • Select the instance of your choice, choose Actions, and then choose Get Windows Password.
      Note: It can take a few minutes for this option to be available after first launching a new instance.
    • Click on Choose File, select your key pair file, and then choose Open. Alternatively, you can paste the contents of your key pair into the text box.
    • Choose Decrypt Password.
  • The first thing you’ll want to do after you’ve logged into the jump box is to turn off IE Enhanced Security Configuration. Otherwise, you will have problems logging into Power BI Service later.
    • Inside the Jump Box, go to Start > Server Manager > Local Server
    • Click the On link to the right of IE Enhanced Security Configuration
    • Click Off under the Administrators section and Click OK
  • Download and install Power BI Desktop 64-bit on the Jump Box. Remember to just download PBIDesktopSetup_x64.exe, which is the 64-bit version of Power BI Desktop.  After you download the executable, run it to install Power BI Desktop.  Accept all the default settings.

Installing and configuring a Microsoft on-premises data gateway

In this step, you install a Microsoft on-premises data gateway to enable Power BI to communicate with data stores that are not accessible from the public internet, such as your Amazon Redshift cluster, which you deployed inside a private subnet. Install the Microsoft on-premises data gateway on the data gateway instance. You can perform the following tests at minimum or no cost. Complete the following steps:

  • From the Windows Server Jump Box, you will RDP to the data gateway using its private IP address, which you can get from the EC2 Console.
  • Use the EC2KeyPair to decrypt the administrator password for the data gateway following the same instructions as outlined in the previous steps.
  • Turn off IE Enhanced Security Configuration.
    • Inside the data gateway, go to Start > Server Manager > Local Server
    • Click the On link to the right of IE Enhanced Security Configuration
    • Click Off under the Administrators section
    • Click OK
  • Download and install Microsoft On-premises Data Gateway
    • On the screen that asks you to choose the type of gateway you need, choose On-premises data gateway (recommended)
    • Accept the default values and click Install
    • When the installer ask you to sign-in, type the email address associated with the admin account for the Power BI Pro tenant.
    • Click Sign in
    • After sign-in, you will be asked to register the gateway. If you are asked to register a new gateway or migrate, restore, or take over an existing gateway, choose Register a new gateway.
    • Give your gateway a name and provide a recovery key
    • Click Configure
    • You should see a green checkmark indicating the gateway is online and ready to be used.

Configuring the data gateway in Power BI

In this step, you log in to your Power BI tenant as an administrator to configure how Power BI connects to the AWS data sources. Complete the following steps:

  1. On your browser (from any machine), launch https://powerbi.com.
  2. Log in with an account that has admin privileges. Use the same account that you for the data gateway.
  3. Choose the gear icon.
  4. Choose Manage gateways.
  5. Choose the gateway you installed earlier.
  6. Select the check box Allow user’s custom data connectors to refresh through this gateway cluster.
  7. Choose Apply.
  8. Choose ADD DATA SOURCE.
  9. Create a data source for Amazon Redshift with the following parameters:
    • Data Source Name – Amazon Redshift Private VPC.
    • Data Source Type – Amazon Redshift.
    • Server – Replace with the value of the [] key from the AWS CloudFormation Stack Outputs
    • Database – Enter the name of the Amazon Redshift database, which you provided earlier as part of the CloudFormation stack parameters. The default is .
    • Username – Enter your database username, which you provided earlier as part of the CloudFormation stack parameters.
    • Password – Enter your database password, which you provided earlier as part of the CloudFormation stack parameters.
  10. Choose Add.

Installing the Power BI mobile app

In this step, you install the Power BI app on your mobile phone so you can interact with Power BI reports and dashboards later from your phone. Complete the following steps:

  1. Go to either the Apple App Store or Google Play Store and search for .
  2. Install the app.
  3. Sign in to Power BI with the same account you’ve been using.

Visualizing Redshift Data using Power BI Desktop

In this section, you will connect Power BI Desktop to Redshift; create a report; publish the report to Power BI service; and finally consume the report from your phone.

  1. From Jump Box, launch Power BI Desktop
  2. If you haven’t already, sign in to the Service with your Power BI credentials
  3. Select Home > Get Data > More > Database > Amazon Redshift
  4. Click Connect
  5. On the next screen, provide the following values:
    • Server – copy the value of the key [RedshiftClusterEndpoint], which is found in the CloudFormation Stack Outputs tab
    • Databasedev (or whatever name you gave for the database)
    • Data Connectivity ModeDirectQuery
  6. If this is the first time you’re connecting to this cluster, then you’ll need to type the Redshift credentials you provided to the CloudFormation Stack earlier. Type in your Redshift username and password in the popup window and click on Connect.
  7. Select the orders, lineitem, and part tables from the Navigator window and then click Load.
  8. Once the data has finished loading, you will need to define table relationships in the in-memory model.
    • In Power BI Desktop, change to the Model view by clicking on the “table relationship” icon on the left.
    • Create relationships between the tables by dragging and dropping the following columns on each other.
      • o_orderkey = lineitem.l_orderkey
      • p_partkey = lineitem.l_partkey
  1. Now, you are ready to create some charts. Change to Report view and add the following visualizations to the report.
    • Date Slicer
      • Visualization type – Slicer
      • Field – orders.o_orderdate
    • Sales by Date by Manufacturer
      • Visualization type – Line Chart
      • Axis – orders.o_orderdate
      • Legend – part.p_mfgr
      • Values – lineitem.l_extendedprice
    • Order Count
      • Visualization type – Card
      • Fields – Count of orders.o_orderkey
    • Line Item Count
      • Visualization type – Card
      • Fields – Count of lineitem.l_linenumber
  1. With a little formatting, your report shows Sales by Date and Manufacturer and should look something similar to this:

  1. Save the report and give it a name.

Publishing the report to Power BI

After you create a report in Power BI Desktop, you need to publish the dataset and report to Power BI to share it with others or consume it from a mobile device. Complete the following steps:

  1. In Power BI Desktop, choose Publish
  2. Choose My workspace.
  3. Choose Select.

It should not take long for the publishing to complete because no data was imported.

Configuring the gateway connection

You now need to tell Power BI to map the data source inside this report to the Amazon Redshift data source that you registered with the data gateway. Complete the following steps:

  1. From any machine, open a browser and launch https://powerbi.com.
  2. Log in to Power BI.
  3. Choose My workspace.
  4. Choose Datasets.
  5. Choose the … icon next to dataset you just published.
  6. Choose Settings.
  7. Expand the Gateway connection section.
  8. For Use a data gateway, choose On.
  9. Under Maps to, choose the Amazon Redshift data source you created earlier.
  10. Choose Apply.

Consuming the Power BI reports and dashboard

Now that you have published the report to Power BI successfully, you can interact with it on https://powerbi.com.

  1. Launch https://powerbi.com.
  2. Log in to Power BI.
  3. Choose My workspace.
  4. Choose Reports.
  5. Choose the report you just published.

Your report should look similar to the following screenshot.  It shows the same Sales by Date and Manufacturer graph as before.

You can interact with the reports and dashboards on the Power BI mobile app the same way you can on the Power BI website. The following screenshot shows how the Sales by Date and Manufacturer report looks on the Power BI app for iPhone.

 

When you view and interact with the report, whether from the website or your mobile app, you’re always viewing the latest data because you connect directly to Amazon Redshift.

Conclusion

This post showed how to use Power BI to query data in Amazon Redshift to generate reports, visualization, and dashboards, and described a solution architecture that you can deploy in the AWS Cloud. The post also demonstrated how easy it is to set up and connect Power BI to an Amazon Redshift cluster in a VPC without public internet access, and how to push Power BI dashboards to mobile devices. Be sure to also check out Amazon QuickSight, AWS’ cloud-native BI service, which requires no desktop clients or servers, automatically detects Redshift instances in your VPC and connects securely to your Redshift instances via private VPC connectivity.

Special acknowledgement goes to AWS colleagues Juan Yu, Sophia Jung, and Joe Harris for their valuable comments and suggestions.

If you have any questions or suggestions, leave your feedback in the comment section. If you need any further assistance to optimize your Amazon Redshift implementation, contact your AWS account team or a trusted AWS partner.

 


About the authors

Vu Le is a Senior Data Architect, Strategic Accounts Team, AWS Professional Services.

 

 

 

 

Po Hong, PhD, is a Senior Data Architect, Data & Analytics Global Specialty Practice, AWS Professional Services.

 

 

TAGS: Amazon Redshift

Sours: https://aws.amazon.com/blogs/big-data/integrate-power-bi-with-amazon-redshift-for-insights-and-analytics/
  1. Ladies retro bikes
  2. Wellstar remote access
  3. Menards impact sockets

Creating dashboards quickly on Microsoft Power BI using Amazon Athena

Amazon Athena is an interactive query service that makes it easy to analyze data in a data lake using standard SQL. One of the key elements of Athena is that you only pay for the queries you run. This is an attractive feature because there is no hardware to set up, manage, or maintain.

You can query Athena with SQL or by using data visualization tools such as Amazon QuickSight, Microsoft Power BI, Tableau, or other third-party options. QuickSight is a cloud-native business intelligence (BI) service that you can use to visually analyze data and share interactive dashboards with all users in your organization. QuickSight is fully managed and serverless, requires no client downloads for dashboard creation, and has a pay-per-session pricing model that allows you to pay for dashboard consumption with a maximum charge of $5.00 per reader per month. The combination of QuickSight and Athena allows you to rapidly deploy dashboards and BI to tens of thousands of users, while only paying for actual usage, and not worrying about server deployment or management.

Microsoft Power BI allows you similarly to analyze your data. Previously, creating dashboards with Microsoft Power BI and Athena required you to download all data locally on your computer. This takes time and can fail due to memory or network bandwidth constraints.

You can now create Microsoft Power BI dashboards and leverage the power of Athena through our out-of-the-box connector for Power BI. The connector is more scalable as it supports Power BI’s DirectQuery mode in which complete, raw data sets are not downloaded to your workstation. While you create or interact with a visualization, Microsoft Power BI works with Athena to dynamically query the underlying data source so you’re always viewing current data.

This post provides step-by-step guidance on how to use the Athena connector for Power BI to query, visualize, and share data with Power BI.

Solution overview

To create Microsoft Power BI dashboards using Athena as a data source, you start by designing a dashboard in Microsoft Power BI Desktop with the help of the Athena data source connector for Power BI and the Athena ODBC driver. When you finish creating your dashboard, you publish it to the Microsoft Power BI Service. To see your data on Microsoft Power BI Service, you need to install the Microsoft Power BI on-premises data gateway in your AWS account—it works like a bridge between Microsoft Power BI Service and Athena. Finally, you configure Athena as a new data source in Microsoft Power BI Service.

To authenticate yourself with Athena, you use an instance profile role because it is easier to do all the configuration, or you can use any of the different authentication options that the Athena ODBC driver provides.

The following diagram illustrates the solution architecture.

Walkthrough overview

For this post, we step through a use case using the data from the New York City Taxi Records dataset from 2015. The data is already stored in Apache Parquet format and is partitioned. For more information about optimizing your Athena queries, see Top 10 Performance Tuning Tips for Amazon Athena.

You deploy an AWS CloudFormation stack with all the infrastructure required to deploy two Amazon Elastic Compute Cloud (Amazon EC2) instances in a private subnet in an Amazon Virtual Private Cloud (Amazon VPC): one instance is used for Microsoft Power BI Desktop, and the other is used for the Microsoft Power BI on-premises data gateway. This stack uses t3.2xlarge instances because they have the minimal hardware requirements recommended. You can increase or decrease the EC2 instance type depending on the performance of the gateway.

Additionally, the CloudFormation template creates an AWS Glue table that gives you access to the dataset. It creates an AWS Lambda function as an AWS CloudFormation custom resource that updates all the partitions in the AWS Glue table.

Then, you use AWS Systems Manager Session Manager (see Starting a session (Systems Manager console) and any remote desktop client to configure the instances and to create your dashboard by following these steps:

  1. Deploy the CloudFormation stack by choosing Launch stack:
  2. On the Amazon EC2 instance that has the tag , install and configure the Simba Athena ODBC driver and Microsoft Power BI Desktop.
  3. Create your dashboard on Microsoft Power BI Desktop and publish it.
  4. On the Amazon EC2 instance that has the tag , install and configure the Simba Athena ODBC driver and Microsoft Power BI on-premises data gateway.
  5. Open Microsoft Power BI and configure your Athena data source.

Prerequisites

For this walkthrough, you should have the following prerequisites:

Logging in to your Microsoft Power BI Desktop instance

To reduce the surface of attack of a bastion host, the operational burden to manage bastion hosts, and the additional costs incurred, Session Manager allows you to securely connect to your EC2 instances without the need to run and operate your own bastion hosts or run SSH on your EC2 instances. For more information, see New – Port Forwarding Using AWS System Manager Session Manager.

Connect to the Microsoft Power BI Desktop instance with Session Manager. You need to run the following commands depending on the OS of your local machine. It can take a few minutes after deployment for your instance to be available.

For Linux and Mac OS, enter the following code:

 For Windows, enter the following code:

Open your remote desktop application and connect to the Microsoft Power BI Desktop EC2 instance. You need the following information:

Installing and configuring Microsoft Power BI Desktop

To install and configure Microsoft Power BI Desktop, complete the following steps:

  1. Download and install the latest Athena ODBC driver for Windows 64-bit.
  2. Choose the ps1 script located in the desktop (right-click) and choose Run with Powershell.

This creates a new data source on Windows called .

  1. Download and install the Microsoft Power BI Desktop.
  2. Open the Microsoft Power BI Desktop application.

Creating an Athena connection on Microsoft Power BI Desktop

To create your Athena connection, complete the following steps:

  1. Open Microsoft Power BI Desktop.
  2. Choose Get Data and More.
  3. Search for and select Amazon Athena.
  4. For Data Source Name (DSN), enter .
  5. Choose DirectQuery.

If you choose Import mode, you can’t create the dashboard because Microsoft Power BI Desktop tries to download all data locally on your computer, and the dataset never finishes loading.

  1. Choose OK.
  2. Choose Use Data Source Configuration.
  3. Choose Connect.
  4. In the folder, navigate to the folder.
  5. Choose the records.
  6. Choose Load.

Creating your dashboard on Microsoft Power BI Desktop and publishing it

You can create a dashboard to show the number of transactions by month and type in descending order. You can then publish the structure of this report to make it available on Microsoft Power BI.

  1. In the Visualizations pane, choose the Stacked bar chart
  2. In the Fields pane, drag the month field to the Axis section in the Visualizations
  3. Drag the type field to the Legend section in the Visualizations
  4. Drag the pickup_datetime field to the Value section in the Visualizations

The following screenshot shows your visualization.

  1. Choose Publish.

Because this is a new report, you’re prompted to save it before you can publish it.

  1. Give your report a name (such as ), and choose Save.
  2. Sign in to be able to publish your report.
  3. Choose a destination (such as My workspace).
  4. In the Success window, choose Got it.

After this last step, the report structure is published on Microsoft Power BI. However, if you try to see the report, there isn’t any data on it because the data isn’t published with the report. You need to install the Microsoft Power BI on-premises data gateway to be able to pull the data.

Logging in to your Microsoft Power BI on-premises data gateway instance

As you did with the Microsoft Power BI Desktop, you log in to the Microsoft Power BI Gateway instance using the tags to get its IDs and a different local port.

For Linux and Mac OS, enter the following code:

For Windows, enter the following code:

Open your remote desktop application and connect to the Microsoft Power BI Gateway Amazon EC2 instance with the following information:

Installing and configuring Microsoft Power BI on-premises data gateway

To set up your on-premises data gateway, complete the following steps:

  1. Download and install the latest Athena ODBC driver for Windows 64-bit.
  2. Choose the script located in the desktop (right-click) and choose Run with Powershell.

This creates a new data source on Windows called .

  1. Download the Microsoft Power BI on-premises data gateway standard mode and launch the installer. You might need to update to the latest available .NET version before starting the installation.
  2. For your gateway, choose On-premises data gateway (recommended).
  3. Accept the default values and choose Install.
  4. When the installer asks you to sign in, enter the email address associated with the admin account for the Microsoft Power BI Pro tenant.
  5. Choose Sign in.
  6. If asked to register a new gateway or migrate, restore, or take over an existing gateway, choose Register a new gateway.
  7. Give your gateway a name and provide a recovery key.
  8. Choose Configure.

You should see a green checkmark indicating the gateway is online and ready to be used.

Opening Microsoft Power BI and configuring your Athena data source

To configure your data source, complete the following steps:

  1. Open Microsoft Power BI in your browser.
  2. Choose the Settings icon.
  3. Choose Manage gateways.
  4. Find the gateway cluster you just created.
  5. Hover your mouse over your gateway name and choose the …icon.
  6. Choose Open menu.
  7. Choose Add data source.
  8. For the data source name, enter .
  9. For the data source type, choose AmazonAthena.
  10. For the second data source name, enter .
  11. On the Authentication Method, choose Anonymous, then choose Add.

Seeing your report on Microsoft Power BI

To view your report, complete the following steps:

  1. Choose the workspace where you saved your report.
  2. On the Datasets + dataflows tab, locate the dataset, locate the dataset that has the same name as your report (for example,) and choose the … icon.
  3. Choose Settings.
  4. Choose Discover Data Sources.
  5. Expand the Gateway Connection
  6. Choose your gateway.
  7. For Maps to, choose .
  8. Choose Apply.
  9. Return to the workspace where you saved your report.
  10. On the Content tab, choose your report ().

You can now see your report online using the most recent data.

Cleaning up

To avoid incurring future charges, delete the CloudFormation stack and the S3 bucket that you deployed as part of this post.

Conclusion

This post presented how to connect to Athena from Microsoft Power BI using the out-of-the-box data source connector and import data using DirectQuery mode. The first part of the post described the architecture components and how to successfully create a dashboard using the NYC taxi dataset. The stack deployed uses only one EC2 instance for the Microsoft Power BI on-premises data gateway, but in production, you should consider creating a high-availability cluster of gateway installations, ideally in different Availability Zones. The second part of this post deployed a demo environment and walked you through the steps to configure Microsoft Power BI with Athena to share your insights. For native access to your data in AWS without any downloads or servers, be sure to also check out Amazon QuickSight.


About the Authors

Armando Segnini is a Data Architect with AWS Professional Services. He spends his time building scalable big data and analytics solutions for AWS Enterprise and Strategic customers. Armando also loves to travel with his family all around the world and take pictures of the places he visits.

 

 

Xavier Naunay is a Data Architect with AWS Professional Services. He is part of the AWS ProServe team, helping enterprise customers solve complex problems using AWS services. In his free time, he is either traveling or learning about technology and other cultures.

Sours: https://aws.amazon.com/blogs/big-data/creating-dashboards-quickly-on-microsoft-power-bi-using-amazon-athena/
[1/1] Visualizing Amazon Fires 🔥 in Power BI: Get data and model it.

Using Microsoft Power BI with Amazon QuickSight

  • With Amazon QuickSight, there’s no need to download and install a client application. All functionality, including authoring and reporting, can be accessed from any platform (Windows, Mac, Linux, etc.) by a web browser.

  • Amazon QuickSight is delivered as a fully managed, cloud-native SaaS application and is simple to build and deploy dashboards to production. The service is serverless, which means that you do not need to calculate how many nodes/servers you need to support your users. QuickSight also takes full advantage of high availability features provided by AWS for resiliency.

  • It’s easy to get started in small or large settings, with the ability to add users from a point-and-click interface within QuickSight. No external administrator intervention needed.

  • Amazon QuickSight is powered by Super-fast, Parallel, In-memory Calculation Engine (SPICE) for a fast response time (in the milliseconds) and interactive visualizations. Datasets can currently scale up to 200 GB.

  • Amazon QuickSight pricing is simple, inexpensive, and has two components; report authors and report readers. Report authors, who create and publish interactive dashboards, are priced per user. If users do not log in during a given month, there are no charges for those users. Report readers are charged per 30-minute session, with a maximum of $5.00 per reader per month. A free trial allows you to evaluate Amazon QuickSight without any charges. For more information, see Amazon QuickSight Pricing.

  • Sours: https://docs.aws.amazon.com/whitepapers/latest/using-power-bi-with-aws-cloud/using-microsoft-power-bi-with-amazon-quicksight.html

    Bi amazon power

    .

    Power BI Connection with Amazon Redshift -- Learn Power BI

    .

    You will also like:

    .



    903 904 905 906 907