[認證介紹] Microsoft BI 相關的認證 (Data Warehouse & Data Model & PowerBI)

[認證介紹] Microsoft BI 相關的認證 (Data Warehouse & Data Model & PowerBI)

  April 09, 2019    Blake W.

---

最近正在研究 Microsoft BI 方面的解決方案，研讀了很多 BI, Data Warehouse, 視覺化, Data Analysis 等方面相關的資料，上網查詢了一下 Microsoft 在 BI 方面相關的 Certification 有哪些，我大致整理了一些資料記錄下來，同時提供讓大家參考！

Microsoft BI 開發相關的認證主要有兩個：
1. MCSA: SQL 2016 BI Development (包含兩個測驗)
* Exam 70-767 Implementing a Data Warehouse using SQL
* Exam 70-768 Developing SQL Data Models

2. MCSA: BI Reporting (包含兩個測驗)
* Exam 70-778 Analyzing and Visualizing Data with Power BI
* Exam 70-779 Analyzing and Visualizing Data with Microsoft Excel

Microsoft BI 相關的認證，主要都包含在 SQL Server 相關的產品服務之中，最基本的當然是 SQL Server 本身，SQL Data Warehouse 就是架構在 SQL Server 主體之上，另外加上 SQL Server Enterprise Information Management (EIM) Suite 的成員，三個成員包括有：SQL Server Integration Service (SSIS), Master Data Service (MDS), Data Quality Service(DQS)，然後 Data Model (Cube) 的分析與建立就需要透過 SQL Server Analysis Service (SSAS) 加上 MDX 和 DAX 兩大工具，整個搭配架構合成，用來規劃、架構、分析與建構 BI Data Warehouse & Data Model solution，最後透過 Microsoft 的資料視覺化工具：Power BI or EXCEL 來做呈現，良好的資料權限分割，視覺化的圖示工具，不論是報表、儀表板、即時監控、警告通知等，都有很好的功能來實踐，以上的架構可以參考下圖的說明就會更清楚整個 Microsoft BI 的解決方案。

功能架構如下圖：

系統架構如下圖：

有關 Data Warehouse 的部分，是進入 BI 的第一步關鍵，如何建立 Data Warehouse 更是規劃 BI 最重要的第一步，有了這個 Data Warehouse 之後，我們才能夠往後來建立 Data Model (Cube)，然後才能做資料的視覺化分析與呈現。

以下主要針對 Exam 70-767 這個認證來介紹，這是 Microsoft 在 Data Warehouse 方面主要解決方案的一張認證，如何使用 SQL Server 相關的服務來規劃、設計、建置 BI 資料倉儲的解決方案。
對於認證相關的測驗，我的考試準備內容如下，當然，網路上還有很多線上的資訊需要研讀，但是資料項目太多，無法一一列舉。

# 測驗內容說明：

Exam 70-767 Implementing a Data Warehouse using SQL
https://www.microsoft.com/en-us/learning/exam-70-767.aspx

Skills measured: (考試內容範圍及佔分比例)
* Design, implement, and maintain a data warehouse (35–40%)
* Extract, transform, and load data (40–45%)
* Build data quality solutions (15–20%)

# 閱讀書籍：（這是 Microsoft 出版的認證考試相關課程，最好買一本來慢慢研讀，裡面的內容都要了解清楚，書本裡面的介紹只是部分，自己需要再找相關的資訊來補齊。）

Exam Ref 70-767 Implementing a SQL Data Warehouse
by Raj Uchhana, Jose Chinchilla
Publisher: Microsoft Press
Release Date: November 2017
ISBN: 9781509304486
https://www.oreilly.com/library/view/exam-ref-70-767/9781509304486/

# 線上課程：（線上免費課程，共有三大科目，每一個科目都有很多不同的服務功能介紹，非常適合漸進式的課程學習，Microsoft 安排的課程，不管是分章節、服務功能說明、實作訓練，涵蓋內容深入淺出，非常適合慢慢的課程學習內容，只是需要花很多時間在這些線上課程之上。）

1. DAT216x | edX : Delivering a Relational Data Warehouse
https://courses.edx.org/courses/course-v1:Microsoft+DAT216x+3T2016/course/

2. DAT217x | edX : Implementing ETL with SQL Server Integration Services
https://courses.edx.org/courses/course-v1:Microsoft+DAT217x+2T2016/course/

3. DAT218x | edX : Data Cleansing with Data Quality Services (DQS)
https://www.edx.org/course/data-cleansing-data-quality-services-dqs-microsoft-dat218x-2

# 各項 Topic 的線上參考資訊：（參考的資料非常多，但是每一個 Topic 都需要了解清楚，否則考試會 GG .....）

* SQL Data Warehouse 相關：
什麼是 Azure SQL 資料倉儲？
https://docs.microsoft.com/zh-tw/azure/sql-data-warehouse/sql-data-warehouse-overview-what-is

REST APIs for Azure SQL Data Warehouse
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-manage-compute-rest-api

Partitioning tables in SQL Data Warehouse
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-partition

* SSIS 相關：
Control Flow
https://docs.microsoft.com/en-us/sql/integration-services/control-flow/control-flow?view=sql-server-2017

Integration Services Transformations
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/integration-services-transformations?view=sql-server-2017

Transform Data with Transformations
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/transform-data-with-transformations?view=sql-server-2017

Data Flow
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/data-flow?view=sql-server-2017

Data in Data Flows
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/data-in-data-flows?view=sql-server-2017

Integration Services (SSIS) Logging
https://docs.microsoft.com/en-us/sql/integration-services/performance/integration-services-ssis-logging?view=sql-server-2017

Execute SQL Task
https://docs.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task?view=sql-server-2017

Precedence Constraints
https://docs.microsoft.com/en-us/sql/integration-services/control-flow/precedence-constraints?view=sql-server-2017

Lookup Transformation
https://docs.microsoft.com/en-sg/sql/integration-services/data-flow/transformations/lookup-transformation?view=sql-server-2017

Set Package Properties
https://docs.microsoft.com/en-us/sql/integration-services/set-package-properties?view=sql-server-2017

Deploy Integration Services (SSIS) Projects and Packages
https://docs.microsoft.com/en-sg/sql/integration-services/packages/deploy-integration-services-ssis-projects-and-packages?view=sql-server-2017

Debugging Data Flow
https://docs.microsoft.com/en-sg/sql/integration-services/troubleshooting/debugging-data-flow?view=sql-server-2017

Parallel Loop Task
http://www.cozyroc.com/ssis/parallel-loop-task

Union All Transformation
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/union-all-transformation?view=sql-server-2017

MERGE in Integration Services Packages
https://docs.microsoft.com/en-us/sql/integration-services/control-flow/merge-in-integration-services-packages?view=sql-server-2017

Foreach 迴圈容器
https://docs.microsoft.com/zh-tw/sql/integration-services/control-flow/foreach-loop-container?view=sql-server-2017

Debugging Control Flow
https://docs.microsoft.com/en-us/sql/integration-services/troubleshooting/debugging-control-flow?view=sql-server-2017

MERGE (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017

Columnstore indexes: Overview
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-2017

Change Data Capture Stored Procedures (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/change-data-capture-stored-procedures-transact-sql?view=sql-server-2017

Enabling AlwaysOn for SSISDB
https://chrislumnah.com/2017/05/09/enabling-alwayson-for-ssisdb/

Track Data Changes (SQL Server)
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server?view=sql-server-2017

SQL Server Temporal Tables vs Change Data Capture vs Change Tracking - part 2
https://www.mssqltips.com/sqlservertip/5212/sql-server-temporal-tables-vs-change-data-capture-vs-change-tracking--part-2/

Temporal Table Usage Scenarios
https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios?view=sql-server-2017

* DQS 相關：

Install Data Quality Services
https://docs.microsoft.com/en-us/sql/data-quality-services/install-windows/install-data-quality-services?view=sql-server-2017

Data Quality Services (DQS) Cleansing Transformation - SSIS
https://mindmajix.com/ssis/data-quality-services-dqs-cleansing-transformation

Task 7: Adding DQS Cleansing Transform to the Data Flow
https://docs.microsoft.com/en-us/sql/tutorials/task-7-adding-dqs-cleansing-transform-to-the-data-flow?view=sql-server-2014

SSIS: Using the DQS Cleansing component
https://intelligentsql.wordpress.com/2013/07/30/ssis-using-the-dqs-cleansing-component/

Perform Knowledge Discovery
https://docs.microsoft.com/en-us/sql/data-quality-services/perform-knowledge-discovery?view=sql-server-2017

Create a Matching Policy
https://docs.microsoft.com/en-us/sql/data-quality-services/create-a-matching-policy?view=sql-server-2017&viewFallbackFrom=sqlserver-

Data Quality Projects (DQS)
https://docs.microsoft.com/en-us/sql/data-quality-services/data-quality-projects-dqs?view=sql-server-2017

DQS Security
https://docs.microsoft.com/en-us/sql/data-quality-services/dqs-security?view=sql-server-2017

* MDS 相關：

Master Data Services
https://docs.microsoft.com/en-us/sql/master-data-services/master-data-services?view=sql-server-2014

SQL Server Master Data Services Tips
https://www.mssqltips.com/sql-server-tip-category/97/master-data-services/

How to version a SQL Server Master Data Services model
https://www.mssqltips.com/sqlservertip/4292/how-to-version-a-sql-server-master-data-services-model/

MDS Versioning, Permission and Security — DatabaseJournal.com
https://www.databasejournal.com/features/mssql/article.php/3908611/MDS-Versioning-Permission-and-Security.htm

Domain-Based Attributes (Master Data Services)
https://docs.microsoft.com/en-us/sql/master-data-services/domain-based-attributes-master-data-services?view=sql-server-2017

Change Tracking (Master Data Services)
https://docs.microsoft.com/en-us/sql/master-data-services/change-tracking-master-data-services?view=sql-server-2017