Power BI 직접 쿼리 기능 사용하는 방법

튜토리얼

Power BI 보고서를 생성할 때, 뷰어들은 보고서가 주기적으로 새로 고쳐지고 데이터가 지속적으로 업데이트되기를 기대합니다. 어떻게 하면 될까요? 보고서를 수동으로 업데이트하는 것도 작동하지만, Power BI Direct Query는 예약된 새로 고침이라는 더 나은 기능을 제공합니다.

이 자습서에서는 서버에 저장된 데이터 세트에 연결하고 데이터에 대한 쿼리를 실행하여 Power BI Direct Query 기능을 활용하는 방법을 배우게 됩니다.

계속 주시하고 프로세스에서 시간을 절약하면서 생산성을 높이세요!

전제 조건

이 자습서는 실습을 통한 데모로 진행됩니다. 따라오려면 다음이 필요합니다:

  • Power BI 데스크톱 – 이 자습서는 Power BI 데스크톱 버전 2.109.1021.0을 사용합니다.
  • SQL Server.

관련 자료:Microsoft SQL Server Standard Edition 시작하기

  • A code editor – This tutorial uses VS code version 1.71.

관련 자료:학습할 가치가 있는 Visual Studio Code 자습서

관련:Power BI 데이터 게이트웨이 : 다양한 데이터 소스 연결

관련:SQL Server Management Studio (SSMS) 시작하기

Power BI Direct Query로 관리할 SQL 데이터베이스 만들기

Power Bi Direct Query를 사용하면 데이터 집합에 직접 연결하여 프로젝트에 실시간 데이터를 업로드할 수 있습니다. 그러나 SQL 데이터베이스와 연결을 만들기 전에 올바른 세부 정보를 얻으려면 서버 이름과 데이터 소스 이름을 알아야 합니다.

SQL 데이터베이스를 만들려면 다음 단계를 따르십시오:

1. 검색 막대에서 ODBC를 검색하고 ODBC 데이터 소스 관리자 (64비트)를 찾아 클릭하여 엽니다.

Launching ODBC Data Sources

2. 다음으로, ODBC 데이터 소스 관리자 창에서 시스템 DSN 탭으로 이동하고 새 데이터 소스를 추가하려면 추가를 클릭합니다.

Initiating adding a new data source

3. 아래 목록에서 SQL Server 드라이버를 선택하고 SQL 데이터 소스를 만들려면 완료를 클릭합니다.

DirectQuery는 모든 데이터 소스를 지원하지 않습니다. MySQL은 지원되지 않습니다. SQL은 지원됩니다.

Selecting SQL Server driver

4. 이제 데이터 소스(MssqlDataSource)의 이름을 지정하고, 기계에 설치된 SQL 인스턴스(POWERSERVER\POWERSERVER)를 선택하고 마침을 클릭하세요.

SQL 서버 및 데이터 소스 이름을 기록해 두세요. 이 정보는 Python 연결 문자열에 필요합니다.

Naming the new data source

5. 데이터 소스의 세부 정보를 검토하고, 데이터 소스를 테스트하기 위해 “테스트 데이터 소스”를 클릭하세요.

Testing the data source

연결이 정상이면 아래에 표시된 것처럼 “테스트가 성공적으로 완료되었습니다.” 메시지가 표시됩니다.

Verifying testing the data source completed

SQL 서버에 연결

이제 SQL 서버용 데이터 소스를 만들었으므로, 이를 사용하여 Python 코드에 대한 연결 문자열을 만들 것입니다.

1. Visual Studio를 실행하고 새 터미널을 열기 위해 CTRL+SHFT+`를 누르세요.

2. 다음으로 아래의 sqlcmd 명령을 실행하여 SQL 서버 인스턴스에 로그인하세요.

sqlcmd -S SQL_SERVER -E
Connecting to the SQL server instance

3. 로그인한 후, 새 데이터베이스(MSSQLDB)를 생성하기 위해 다음 쿼리를 실행하세요.

CREATE DATABASE MSSQLDB;
GO
Creating a new SQL database

4. Python 파일 DBconnect.py를 생성하고 아래 코드를 추가하세요. 이 코드를 사용하면 Python에서 SQLAlchemy ORM을 사용하여 SQL 데이터베이스에 연결할 수 있습니다.

엔진은 SQLAlchemy 애플리케이션의 시작점을 나타냅니다. 엔진은 Python Database API Specification (DBAPI)에 대한 연결 풀과 방언을 설명합니다. Python DBAPI는 모든 데이터베이스 연결 패키지에 대한 공통 사용 패턴을 정의하기 위한 Python 내 명세서입니다. 이 명세서는 지정된 데이터베이스와 통신합니다.

//DBconnect.py
import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
import pyodbc
# 연결 문자열을 생성하고 urllib을 사용하여 텍스트를 형식화합니다.
conn = urllib.parse.quote_plus(
# 연결 문자열의 값은 데이터 소스에서 가져옵니다.
'Data Source Name=MssqlDataSource;'

'Driver={SQL Server};'

'Server=POWERSERVER\POWERSERVER;'

'Database=MSSQLDB;'

'Trusted_connection=yes;'

)

try:
# sqlalchemy 엔진을 사용하여 연결 생성
	coxn = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))
	print("Passed")
except:
	print("failed!")

5. 메인 폴더에 CreateTable.py라는 Python 파일을 만들고 아래 코드를 추가하고 실행합니다. 아래 코드는 SQL 데이터베이스에 학생 테이블을 만듭니다.

//CreateTable.py
from DBconnect import coxn
from sqlalchemy import Table, Column, Integer, String, MetaData

# 테이블 정의를 포함하는 메타데이터 구성을 생성합니다.
  # 인덱스, 뷰, 트리거 등과 같은 관련된 객체를 포함합니다.
meta = MetaData()

# 테이블을 만들기 위한 일반 SQL 구문으로 CREATE TABLE을 나타냅니다.
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)

# 엔진 객체를 사용하여 모든 정의된 테이블 객체를 만들고 저장합니다.
  # 메타데이터에 정보를 저장합니다.
meta.create_all(coxn)

6. 다음으로 SSMS를 열고 생성된 데이터베이스, 테이블 및 열을 확인합니다.

Verifying Database and tables on SSMS.

7. WriteToTable.py라는 Python 파일을 만들고, 아래 코드를 추가하고 실행합니다.

이 코드에는 데이터베이스 테이블에 값 쓰기를 위한 논리가 포함되어 있습니다.

//WriteToTable.py
# CreateTable.py 파일에서 학생 테이블을 가져옵니다.
from CreateTable import students
# DBconnect.py 파일에서 연결 문자열을 가져옵니다.
from DBconnect import coxn
# 여러 값을 학생 테이블에 삽입합니다.
ins = students.insert().values([
   {'name':'Bob','lastname':'Marley'},
   {'name':'Bob','lastname':'Dylan'}
])
conn = coxn.connect()
conn.execute(ins)

온 프레미스 데이터 게이트웨이 준비

데이터베이스에 연결 문자열을 만들었으므로 데이터 게이트웨이를 생성해야 합니다. 이 데이터 게이트웨이는 데이터베이스를 Power BI에 연결하는 역할을 담당할 것입니다.

1. 온 프레미스 데이터 게이트웨이를 열고 로그인합니다.

Signing in to On-premises data gateway

2. 온 프레미스 데이터 게이트웨이의 상태를 확인하고 온라인인지 확인합니다. 아래에 표시된 것처럼.

Checking if the On-premises data gateway is online

3. 다음으로, Power BI를 열고 홈 립본 탭 아래에서 데이터 가져오기를 클릭하고 SQL Server를 선택하여 데이터 소스를 Power BI에 연결을 시작합니다.

Getting data from SQL server

4. 이제 다음과 같이 DirectQuery에 연결합니다:

  • 서버 이름과 데이터베이스 이름을 해당 필드에 삽입하십시오. DirectQuery 옵션을 선택했는지 확인합니다. 연결을 위해 확인을 클릭합니다.
Connecting to DirectQuery

5. 데이터베이스에서 테이블(학생)을 선택하고 데이터를 로드하려면 로드를 클릭합니다.

Loading data from the database

6. 다음으로, 데이터를 볼 수 있는 테이블 시각화를 사용하기 위해 테이블 아이콘을 클릭합니다.

Choosing the table visual

관련:파워 BI 파이썬으로 놀라운 데이터 시각화

아래 테이블은 데이터베이스 테이블에서 로드한 데이터를 보여줍니다.

Viewing data in a table visual

7. Power BI에서 수행할 작업에 액세스하려면 파일 메뉴를 클릭합니다.

Accessing the File menu

8. 이제 데이터 보고서를 게시하기 위해 게시 → Power BI에 게시를 클릭합니다.

Publishing data report

9. 클릭하기 <당신의 보고서> 후에 발행이 성공적인 경우, Power BI에서 최근 활동 목록으로 브라우저를 리디렉션합니다 (단계 10).

Opening the Power BI browser

10. 보고서를 열려면 아래와 같이 클릭하십시오.

Opening the data report

첫 설정 시, 프로젝트의 데이터 소스를 아직 Power BI의 데이터 게이트웨이에 연결하지 않았기 때문에 아래와 같은 게이트웨이 문제에 직면할 수 있습니다.

Getting a gateway configuration error

11. 작업 열 아래의 설정 아이콘을 클릭하여 새로운 게이트웨이 연결 추가를 시작하십시오. 이렇게 하면 게이트웨이 구성 오류를 수정할 수 있습니다.

Initiating adding a new gateway connection

12. 새로운 게이트웨이 연결을 구성하고, 만들기를 클릭하여 데이터 소스와 게이트웨이 간의 새로운 연결을 만듭니다.

Configuring a new data source

13. 마지막으로, 게이트웨이가 실행 중인 것을 확인하면 드롭다운 필드에서 데이터 소스 이름 (MssqlDataSource)을 선택하고, 적용을 클릭하십시오.

Applying the new gateway connection

데이터 보고서 보기 및 관리

게이트웨이와 Power BI 간의 연결을 설정한 후에는 보고서를 볼 수 있고 보고서의 새로 고침 일정을 설정할 수 있습니다.

1. Power BI 보고서 홈 페이지로 이동하십시오.

Power Bi Direct Query의 성능은 기본 데이터 집합 소스에 따라 달라집니다. 기본 데이터 집합이 요청에 응답하는 데 걸리는 시간이 사용자가 보고서를 볼 수 있는 속도 또는 첫 번째로 보고서를 성공적으로 볼 수 있는지 여부를 결정합니다.

2. 다음으로, 아래에 표시된 대로 보고서를 클릭하여 엽니다.

Accessing the data report

보고서를 열고 나면 아래에 표시된 데이터를 볼 수 있습니다.

Viewing the data report

3. 보고서 홈으로 돌아가서, 새로 고침 → 일정 새로 고침을 클릭하여 보고서의 예약된 새로 고침을 설정하십시오.

Power Bi Direct Query를 사용하면 라이브 보고서를 생성하는 것 외에도 예약된 새로 고침을 설정하여 보고서를 자동으로 업데이트할 수 있습니다.

Initiating setting a scheduled refresh

4. 이제 드롭다운 필드에서 원하는 새로 고침 간격을 선택하고 변경 사항을 적용하려면 적용을 클릭하십시오.

Setting a refresh interval

5. Python 스크립트 WriteToTable.py를 열고 다음 코드를 추가하여 데이터베이스에 더 많은 항목을 삽입한 다음 스크립트를 실행하십시오.

이 코드를 실행하면 출력이 제공되지 않지만 나중에 다음 단계에서 테이블에 삽입된 값을 확인할 수 있습니다.

SAP Business Warehouse와 같은 다차원 소스에 대한 Power Query Editor가 없기 때문에 데이터로 수행할 수 있는 작업이 제한됩니다.

//WriteToTable.py
# CreateTable.py 파일에서 학생 테이블 가져오기.
from CreateTable import students
# DBconnect.py 파일에서 coxn 연결 문자열 가져오기.
from DBconnect import coxn

# 학생 테이블에 여러 값을 삽입합니다.
ins = students.insert().values([
{'name':'Damian','lastname':'Marley'},
{'name':'Rita','lastname':'Marley'},
{'name':'Ziggy','lastname':'Marley'},
{'name':'Sam','lastname':'Dylan'},
{'name':'Jakob','lastname':'Dylan'},
{'name':'Maria','lastname':'Dylan'}
])
# 연결 커서 생성.
conn = coxn.connect()
# insert() 명령 실행.
conn.execute(ins)

6. 다음으로 Power BI 브라우저로 전환하고 데이터 세트 + 데이터 플로우 탭을 클릭하십시오.

모든 것이 잘 되면 아래 스크린샷에서와 같이 데이터 세트의 마지막 새로 고침 시간과 다음 새로 고침이 표시됩니다.

Power Bi Direct Query는 변경 사항과 형식 지정에 민감합니다. Power Query Editor에 복잡한 단계가 포함되어 있으면 오류가 발생할 수 있습니다.

Verifying the last refreshed time and next refresh of the dataset

당신이 기대했던 대로, 리로드는 15분 후에 자동으로 발생하며, 다음 새로고침은 또 다른 15분 후에 예정되어 있습니다.

Verifying the scheduled refresh works

결론

이 튜토리얼을 통해, Power Bi Direct Query 기능을 사용하여 서버에 저장된 데이터셋에 연결하는 방법을 배웠습니다. 또한 데이터에 직접 쿼리를 실행하는 방법에 대해 다뤘습니다.

리포트를 생성하는 것은 데이터를 수집, 정리, 데이터 소스에 업로드, Power BI에 데이터 로드 및 Power BI 시각화 작업을 오랜 프로세스입니다. 이 언급된 프로세스는 주어졌습니다. 그러나 이후에는 데이터셋을 수동으로 새로 고칠 수도 있고, 설정된 일정에 따라 데이터를 자동으로 새로 고칠 수도 있습니다.

관련 링크:Power BI Python으로 놀라운 데이터 시각화

Power BI Direct Query는 데이터셋 내 데이터를 처리하고 조작할 때 단점이 있습니다. 그러나 이 기능을 사용할 때 백엔드 작업을 한 번 하고 리포트를 계속해서 사용할 수 있다는 사실은 거대한 인센티브입니다.

Source:
https://adamtheautomator.com/power-bi-direct-query/