به منظور انجام دادن تمرینات بیان شده در این مقاله از منابع رایگان ارائه شده در آزمایشگاه استفاده کنید. این مورد هیچ هزینهای برای شما نخواهد داشت.
با کلیک روی این قسمت یک آزمایشگاه ایجاد کنید.
دسترسی به محیط خودتان
قبل از شروع این آزمایشگاه (بغیر از حالتی که بخواهید آزمایشگاه قبلی را ادامه دهید)، بر روی لینک بالا کلیک کنید. شما بعنوان data-ai/student وارد محیط آزمایشگاهی خودتان خواهید شد. در این مرحله میتوانید کار خودتان را در این آزمایشگاه شروع کنید.
توجه: به منظور اینکه محیط آزمایشگاه با ویندوز متناسب باشد، آیکون PC موجود در بالای صفحه و بعد از آن Fit Window to Machine را انتخاب کنید.
برای دیدن تمام آموزش های پاور بی آی به لینک زیر مراجعه کنید:
معرفی کلی
زمان تقریبی مورد نیاز برای این آزمایشگاه 45 دقیقه میباشد.
در این آزمایشگاه یک سری تغییراتی را در کوئریها ایجاد خواهید کرد. بعد از آن هر یک از کوئریها را اجرا خواهید کرد تا به صورت جدول وارد مدل داده ها شود.
مواردی که در این آزمایشگاه یاد میگیرید عبارتند از:
- اعمال تغییرات مختلف
- اجرای کوئریها به منظور بارگزاری آنها در مدل داده ها
قبل از شروع این آزمایشگاه، باید با استفاده از لینک بالا محیط آزمایشگاه را باز کنید و وارد محیط آن شوید. هیچ نیازی به ایجاد محیط مختص خودتان ندارید، چرا که از قبل یک محیطی برای این آزمایشگاه آماده شده است.
بارگزاری داده ها
در این بخش تغییرات را بر روی هر یک از کوئریها اعمال خواهید کرد.
فایل راه حل را باز کنید
- بر روی آیکون Power BI Desktop دبل کلیک کنید (باز شدن آن ممکن است یک یا دو دقیقه زمان ببرد).
- با انتخاب X برای بستن، صفحه معرفی را ببندید.
3. از تب File، Open report و بعد از آن Browse reports را انتخاب کنید.
4. فایل Sales Analysis.pbix را باز خواهید کرد که در پوشه D:\DA100\Lab03A\Starter قرار دارد.
ایجاد کوئری Salesperson
در این بخش کوئری Salesperson را ایجاد خواهید کرد.
- به منوی باز کردن Power Query Editor در Power BI Desktop بروید و در ریبون Home بر روی Transform Data کلیک کنید.
- در پنجره Power Query Editor در صفحه Queries، DimEmployee را انتخاب کنید.
- به منظور تغییر نام کوئری در صفحه Query Setting (که در قسمت راست قرار دارد)، در باکس Name، Salesperson را جایگزین متن کنید و بعد از آن Enter بزنید.
اسم جدول مدل برمبنای اسم کوئری تعیین خواهد شد. توصیه میشود اسم برادر، خواهر یا دوستانتان را استفاده کنید.
- صفحه Queries صحت و سقم به روزرسانی اسم کوئری را تعیین میکند.
به منظور اینکه فقط افراد فروشنده را مشخص کنید، سطرهای کوئری را فیلتر کنید.
5. برای مشخص کردن یک ستون خاص، در تب ریبون Home ، در داخل گروه Manage Columns، بر روی فلش کشوی Choose Columns کلیک کنید و بعد از آن Go to Column را انتخاب کنید.
نکته: این روش در زمانیکه یک کوئری ستونهای زیادی داشته باشد، کارایی مناسبی دارد. شما بمنظور مشخص کردن یک ستون، میتوانید به صورت افقی در صفحه حرکت کنید.
6. در پنجره Go to Column به منظور مرتب کردن لیست براساس نام ستون، بر روی دکمه مرتب کردن براساس AZ کلیک کنید و بعد از آن Name را انتخاب نمایید.
7. ستون SalesPersonFlag را انتخاب و بعد از آن بر روی OK کلیک کنید.
8. به منظور فیلتر کردن کوئری در سرتیتر ستون SalesPersonFlag، بر روی دکمه کشویی کلیک کنید و تیک مربوط به FALSE بردارید.
9. بر روی OK کلیک کنید.
10. در صفحه Query Settings در لیست Applied Steps، به مرحله Filtered Rows توجه کنید.
هر تغییری که انجام میدهید در منطق مربوط به مرحله اضافی نمود پیدا میکند. امکان ویرایش یا حذف مراحل وجود دارد. همچنین در مرحله ایجاد تغییرات بمنظور بررسی نتایج کوئری میتوان یک مرحله را انتخاب کرد.
11. به منظور حذف کردن ستونها، در تب ریبون Home، از داخل گروه Manage Columns، بر روی آیکون Choose Columns کلیک کنید.
12. در پنجره Choose Columns، بمنظور برداشتن کلیک تمامی ستونها، کلیک مربوط به آیتم (Select All Columns) را بردارید.
13. به منظور شامل کردن ستونها، شش ستون زیر را انتخاب کنید:
- EmployeeKey
- EmplyeeNationallDAIternateKey
- FisrtNme
- LastName
- Title
- EmialAddress
14. بر روی OK کلیک کنید.
15. در لیست Applied Steps، اضافه شدن یک مرحله دیگری از کوئری را مشاهده میکنید.
16. به منظور ایجاد یک ستون دارای اسم منحصر بفرد، در مرحله اول سرتیتر ستون FirstName را انتخاب کنید.
17. همراه با نگهداشتن کلید Ctrl، ستون LastName را انتخاب کنید.
18. بمنظور انتخاب سرتیتر ستونها راست کلیک کنید و بعد از آن در منوی متنی ظاهر شده، Merge Columns را انتخاب نمایید.
از طریق کلیک راست کردن بر روی سرتیتر ستونی و انتخاب کردن تغییرات مختلف از منوی ظاهر شده میتوان تغییرات مختلفی را انجام داد. با این وجود، تمامی تغییرات و موارد بیشتر در ریبون قابل دسترس هستند.
19. در پنجره Merge Columns و در لیست کشویی Separator، Space را انتخاب کنید.
20. در باکس New Column Name، عبارت Salesperson را جایگزین متن کنید.
21. بر روی OK کلیک کنید.
22. به منظور تغییر نام ستون EmployeeNationalIDAlternatieKey بر روی سرتیتر ستون EmployeeNatinalLDAlternateKey دبل کلیک کنید.
23. EmployeeID را جایگزین متن کنید و Enter را بزنید.
در زمانیکه از شما خواسته میشود تا نام ستونها را تغییر دهید، تغییر نام آنها به صورت دقیق و برمنبای موارد بیان شده اهمیت زیادی دارد.
24. برای تغییر نام ستون EmailAddres به UPN از مراحل قبلی استفاده کنید.
UPN مخفف User Principal Name (به معنای اسم اصلی کاربر) میباشد. مقادیر موجود در این ستون در حین ایجاد امنیت سطح سطری در Lab 05A استفاده خواهد شد.
25. در قسمت پایین و چپ و در نوار وضعیت بررسی کنید که این کوئری 5 ستون و 18 سطر داشته باشد.
نکته مهمی که وجود دارد عبارت از این است که در صورت بدست نیامدن نتایج درست، جلوتر نخواهید رفت و تکمیل آزمایشگاه های بعدی امکانپذیر نخواهد بود. در صورتیکه این مشکل برای شما پیش بیاید، به مراحل قبلی بیان شده در این بخش برگردید و مشکلات موجود را رفع نمایید.
ایجاد کوئری SalespersonRegion
در این مرحله شما کوئری SalespersonRegion را ایجاد خواهید کرد.
- در صفحه Queries، کوئری DimEmplyeeSalesTerritory را انتخاب کنید.
- در صفحه Query Settings، اسم کوئری را به SalespersonRegion تغییر دهید.
- به منظور حذف کردن دو ستون آخر، ابتدا سرتیتر ستون DimEmployee را انتخاب کنید.
- همراه با نگهداشتن کلید Ctrl، سرتیتر ستونی DimSalesTerritory را انتخاب کنید.
- بر روی سرتیتر ستونها راست کلیک کنید و بعد از آن از منوی متنی نمایش داده شده، Remove Columns را انتخاب کنید.
- در نوار وضعیت مشاهده میکنید که 2 ستون و 39 سطر وجود دارد.
ایجاد کوئری Product
در این مرحله کوئری Product را ایجاد خواهید کرد.
در صورتیکه راهنمایی های دقیق از قبل در آزمایشگاه وجود داشته باشد، مراحل آزمایشگاهی دستورالعمل های مختصری خواهد داشت. در صورتیکه به دستورالعمل دقیق نیاز داشته باشید، میتوانید به مراحل و تکالیف قبلی مراجعه کنید.
- کوئری DimProduct را انتخاب کنید.
2. اسم کوئری را به Product تغییر دهید.
3. محل ستون FinishedGoodsFlag را مشخص کنید و بعد از آن ستون را فیلتر کنید تا محصولات نهایی نمایش داده شوند (که عبارت از TRUE میباشند).
4. بغیر از ستونهای زیر بقیه ستونها را حذف کنید:
- ProductKey
- EnglishProductName
- StandardCost
- Color
- DimProductSubcategory
5. توجه داشته باشید که ستون DimProductSubcategory یک جدول رابطه ای میباشد (شامل یک سری لینکهای Value است).
6. در سرتیتر ستون DimProductSubcategory، در سمت راست اسم ستون، بر روی دکمه باز کننده کلیک کنید.
7. به منظور برداشتن تیک تمامی ستونها، تیک مربوط به آیتم (Select All Columns) را بردارید.
8. تیک مربوط به ستونهای EnglishProdctSubcategoryName و DiProductCategory را بزنید.
با انتخاب کردن این دو ستون، یک تغییری برای متصل شدن به جدول DimProductSubcategory اعمال میشود و سپس این ستونها را شامل میشود. در واقع ستون DimProductCate در یک جدول مرتبط دیگر قرار دارد.
9. برداشتن تیک مربوط به چک باکس Use Original Column Name as Prefix
اسمهای ستون کوئری همواره باید منحصر بفرد باشند. در حین علامتگذاری این چک باکس پیشوند اسم ستون گسترش یافته خواهد بود (در این مورد DimProductSubcategory)، بدلیل اینکه عدم برخورد ستونهای انتخاب شده با ستونهای موجود در کوئری Product، این گزینه از حالت انتخاب خارج میشود.
10. بر روی OK کلیک کنید.
11. DimProductCategory را باز کنید و بعد از آن فقط ستون EnglishProductCategoryName را معرفی کنید.
12. عناوین چهار ستون زیر را به صورت بیان شده تغییر دهید:
- EnglishProductName به Product
- StandardCost بهStandard Cost (فاصله دارد)
- EnglishProductSubcategoryName بهSubcategory
- EnglishProductCategoryName به Category
13. در نوار وظیفه مشاهده میکنید که کوئری شش ستون و 397 سطر دارد.
ایجاد کوئری Reseller
در این مرحله کوئری Reseller را ایجاد خواهید کرد.
- کوئری DimReseller را انتخاب کنید.
- اسم کوئری را به Reseller تغییر دهید.
- تمامی ستونها بغیر از موارد پایین را حذف کنید:
- ResellerKey
- BusinessType
- ResellerName
- DimGeography
- ستون DimGeography را باز کنید تا فقط شامل سه ستون زیر باشد:
- City
- StateProvinceName
- EnglishCountryRegionName
5. در سرتیتر ستون Business Type بر روی فلش رو به پایین کلیک کنید و آیتم های موجود را چک کنید و غلط املایی warehouse را اصلاح کنید.
6. بر روی سرتیتر ستون Business Type راست کلیک کنید و Replace Values را انتخاب نمایید.
7. در پنجره Replace Values مقادیر زیر را بنویسید:
- در باکس Value to Find، عبارت Ware House را وارد کنید.
- در باکس Replace With، عبارت Warehouse را وارد کنید.
8. بر روی OK کلیک کنید.
9. اسم ستونهای زیر را تغییر دهید:
- BusinessType به Business Type (فاصله دارد)
- ResellerName به Reseller
- StateProvinceName به State-Province
- EnglishCountryRegionName به Country-Region
10. در نوار وضعیت مشاهده میکنید که کوئری 6 ستون و 701 سطر دارد.
ایجاد کوئری Region
در این بخش کوئری Region را ایجاد خواهید کرد.
- کوئری DimSalesTerritory را انتخاب کنید.
- اسم کوئری را به Region تغییر دهید.
- به منظور حذف مقادیر صفر، ستون SalesTerritoryAlternateKey را فیلتر کنید.
به غیر از ستونهای زیر، بقیه ستونها را حذف کنید:
- SalesTerritoryKey
- SalesTerritoryRegion
- SalesTerritoryCountry
- SalesTerritoryGroup
5. نامهای ستونهای زیر را به ترتیب بیان شده تغییر دهید:
- SalesTerritoryRegion به Region
- SalesTerritoryCountry به Country
- SalesTerritoryGroup به Group
6. همانطور که مشاهده میکنید در نوار وضعیت 4 ستون و 10 سطر وجود دارد.
ایجاد کوئری Sales
در این بخش کوئری Sales را ایجاد خواهید کرد.
- کوئری FaceResellerSales را انتخاب کنید.
- اسم کوئری را به Sales تغییر دهید.
- به غیر از ستونهای زیر، بقیه ستونها را حذف کنید:
- SalesOrderNumber
- OrderDate
- ProductKey
- ResellerKey
- EmployeeKey
- SalesTerritoryKey
- OrderQuantity
- UnitPrice
- TotalProductCost
- SalesAmount
- DimProduct
همانطور که به یاد دارید در Lab 02A درصد محدودی از سطرهای FactResellerSales فاقد مقادیر برای TotoalProductCost بودند. ستون Dimproduct آورده شده است تا هزینه استاندارد محصول بازبینی شود و مقادیر از دست رفته تصحیح شوند.
- ستون DimProduct را باز کنید و بعد از آن ستون StandardCost را وارد کنید.
- به منظور ایجاد یک ستون متناسب، در تب ریبون Add Column از داخل گروه General، بر روی Custom Column کلیک کنید.
6. در پنجره Custom Column، در باکس New Column Name، عبارت Cost را جایگزین متن کنید.
7. در باکس Custom Column Formula، جمله زیر را وارد کنید (بعد از گذاشتن علامت مساوی):
8. برای راحتی خودتان میتوانید عبارت را از فایل D:\DA100\Lab03A\Assets\Snippets.txt کپی کنید.
if [TotalProductCost] = null then [OrderQuantity] * [StandardCost] else [TotalProductCost]
این عبارت وجود نداشتن مقدار TotalProductCost را بررسی میکند. در صورت درست بودن شرط if این مقدار با ضرب شدن مقدار OrderQuantity در مقدار StandardCost بدست میآید؛ در غیر اینصورت، از مقدار TotalProductCost موجود استفاده میکند.
9. بر روی OK کلیک کنید.
10. دو ستون زیر را حذف کنید:
- TotalProductCost
- StandardCost
11. اسمهای ستونهای زیر را به ترتیب بیان شده تغییر دهید:
- OrderQuantity به Quantity
- UnitPrice به Unit Price (یک فاصله دارد)
- SalesAmount به Sales
12. به منظور اصلاح نوع داده ستونی، در سرتیتر ستون Quantity، در سمت چپ اسم ستون، بر روی آیکون 2 کلیک و بعد از آن Whole Number را انتخاب کنید.
ایجاد نوع داده صحیح اهمیت زیادی دارد. در مواقعی که ستون دارای مقادیر عددی باشد و بخواهید محاسبات عددی انجام دهید، انتخاب نوع صحیح اهمیت زیادی پیدا میکند.
13.نوع داده های موجود در سه ستون زیر را به Fixed Decimal Number تغییر دهید:
- Unit Price
- Sales
- Cost
نوع داده عددی اعشاری ثابت شده مقادیر را با دقت کامل جمع میکند و به همین دلیل نسبت به اعداد اعشاری به فضای انباشت بیشتری نیاز دارد. استفاده از نوع عددی اعشاری ثابت شده برای مقادیر مالی یا نرخ ها (همانند نرخ تبدیل) اهمیت فوقالعادهای دارد.
14. در نوار وضعیت مشاهده میکنید که 10ستون و بیش از 999 سطر وجود دارد.
در حین بررسی داده ها برای هر کوئری، حداکثر 1000 سطر بارگزاری خواهد شد.
ایجاد کوئری Targets
در این بخش کوئری Targets را ایجاد خواهید کرد.
- کوئری ResellerSalesTarget را انتخاب کنید.
- اسم کوئری را به Targets تغییر دهید.
- بمنظور خارج کردن ستون های 12 ماهه (M01-M12) از حالت pivot، ابتدا سرتیتر ستونهای Year و EmployeeID را چند انتخابی کنید.
4. بر روی سرتیتر ستونهای انتخاب شده راست کلیک کنید و از منوی متنی Unpivot Other Columns را انتخاب نمایید.
5. توجه داشته باشید که در این مرحله اسامی ستون ها در ستون Attribute ظاهر میشوند و مقادیر در ستون Value نشان داده میشوند.
6. به منظور حذف مقادیر خط فاصله (-) ستون Value را فیلتر کنید.
7. عنوان دو ستون زیر را به ترتیب بیان شده تغییر دهید:
- Attribute به MonthNumber (بین دو کلمه فاصله وجود ندارد که بعدا حذف میشود)
- Value به Target
در این مرحله یک سری تغییراتی را برای ایجاد ستون تاریخ اعمال کردید. تاریخ از ستونهای Year و MonthNumber بدست خواهند آمد. شما با استفاده از ویژگی Column From Examples این ستون را ایجاد خواهید کرد.
8. به منظور آماده کردن مقادیر ستون MonthNumber، بر روی سرتیتر ستون MonthNumber راست کلیک کنید و بعد از آن Replace Values را انتخاب نمایید.
9. در پنجره Replace Values، در باکس Value To Find، حرف M را وارد کنید.
10. بر روی OK کلیک کنید.
11. نوع داده های ستون MonthNumber را به Whole Number تغییر دهید.
12. در تب ریبون Add Column، از داخل گروه General بر روی آیکون Column From Examples کلیک کنید.
13. توجه داشته باشید که سطر اول برای سال 2017 و ماه 7 میباشد.
14. در ستون Column1، در اولین سلول شبکه، 7/1/2017 را وارد کنید و Enter بزنید.
ماشین مجازی از تنظیمات منطقه ایالات متحده استفاده میکند، بنابراین این تاریخ مربوط به 1 جولای 2017 میباشد.
15. توجه داشته باشید که سلولهای شبکهای همراه با مقادیر پیشبینی شده به روز رسانی میشوند.
این ویژگی به صورت دقیق پیشبینی کرده است که شما مقادیر مربوط به دو ستون را ترکیب میکنید.
16. فرمول نشان داده شده در شبکه کوئری بالا را هم مد نظر داشته باشید.
17. به منظور تغییر نام ستون جدید، بر روی سرتیتر ستون Merged دبل کلیک کنید.
18. اسم ستون را به TargetMonth تغییر دهید.
19. بر روی OK کلیک کنید.
20. ستونهای زیر را حذف کنید:
- Year
- MonthNumber
21. نوع داده های ستونهای زیر را اصلاح کنید:
- Target بصورت عدد اعشاری ثابت شده
- TargetMonth به صورت تاریخ
22. بمنظور ضرب مقادیر Target در 1000، سرتیتر ستون Target را انتخاب کنید و بعد از آن در تب ریبون Transform، از داخل گروه Number Column بر روی Standard کلیک کنید و Multiply را انتخاب نمایید.
23. در پنجره Multiply، در باکس Value، 1000 را وارد کنید.
24. بر روی OK کلیک کنید.
25. در نوار وضعیت مشاهده میکنید که 3 ستون و 809 سطر وجود دارند.
به روز رسانی کوئری Product
در این بخش شما از طریق ادغام کردن کوئری ColorFormats، کوئری Product را به روز رسانی خواهید کرد.
- کوئری Product را انتخاب نمائید.
- به منظور ادغام کوئری ColorFormats، در تب ریبون Home از داخل گروه Combine، بر روی Merge Queries کلیک کنید.
در این مورد ادغام کردن کوئری ها باعث میشود که داده های موجود در منابع داده های متفاوت (SQL Server و یک فایل CSV) را با هم ادغام کنید.
3. در پنجره Merge، در شبکه کوئری Product، سرتیتر ستون Color را انتخاب نمایید.
4. در زیر شبکه کوئری Product، در لیست کشویی، کوئری ColorFormats را انتخاب کنید.
5. در شبکه کوئری ColorFormats، سرتیتر ستون Color را انتخاب کنید.
6. بعد از باز شدن پنجره Privacy Levels، برای هر دو مورد از منابع دادهای مرتبط در لیست کشویی مربوطه، Organizational را انتخاب کنید.
به منظور مشخص کردن قابلیت به اشتراک گذاری داده ها مابین منابع میتوان یک سری سطوح امنیتی را برای منابع داده ها مشخص کرد. تنظیم هر منبع دادهای به صورت Organizational باعث میشود که این داده ها در صورت نیاز قابلیت اشتراک گذاری داشته باشند. منابع داده ای خصوصی به هیچ عنوان قابل اشتراک گذاری با سایر منابع دادهای را ندارند. این به معنای عدم قابلیت اشتراک گذاری داده های Private نمیباشد، بلکه به این معنا است که موتور Power Query نمیتواند داده ها را مابین منابع به اشتراک بگذارد.
7. بر روی Save کلیک کنید.
8. در پنجره Merge، بر روی OK کلیک کنید.
9. ستون ColorFormats را انتخاب کنید تا دو ستون زیر را شامل شود:
- Background Color Format
- Font Color Format
10. در نوار وظیفه مشاهده میکنید که 8 ستون و 397 سطر وجود دارند.
به روز رسانی کوئری ColorFormats
در این بخش شما ColorFormats را به روز رسانی خواهید کرد تا بارگزاری آن را غیرفعال کنید.
- کوئری ColorFormats را انتخاب کنید.
- در صفحه Query Settings، بر روی لینک All Properties کلیک کنید.
3. در پنجره Query Properties، تیک مربوط به چک باکس Enable Load To Report را بردارید.
غیرفعال کردن بارگزاری به معنای این است که به عنوان جدول در مدل دادهای بارگزاری نخواهد شد. دلیل انجام این کار عبارت از این است که این کوئری با کوئری Product ادغام شده است و قابلیت بارگزاری بر روی مدل داده ها را داشته است.
4. بر روی OK کلیک کنید.
تکمیل
در این بخش آزمایشگاه را کامل خواهید کرد.
- بررسی کنید که هشت کوئری به صورت صحیح و مطابق موارد زیر نامگذاری شده باشند:
- Salesperson
- SalespersonRegion
- Product
- Reseller
- Region
- Sales
- Targets
- ColorFormats (که در مدل دیتا بارگزاری نخواهد شد)
- به منظور بارگزاری مدل داده ای، در نمای پسزمینه File، Close & Apply را انتخاب کنید.
تمامی کوئریهای دارای قابلیت بارگزاری به صورت مدل داده ای بارگزاری شده اند.
- در صفحه Fields (که در قسمت راست قرار دارد)، بررسی کنید که هفت جدول در مدل داده ای بارگزاری شده باشند.
4. فایل Power BI Desktop را ذخیره کنید.
5. Power BI Desktop را در حالت باز بگذارید.
در آزمایشگاه بعدی، جداول و رابطه های مدل داده ای را ایجاد خواهید کرد.
خاتمه دادن به آزمایشگاه خودتان
به منظور خاتمه دادن به آزمایشگاه، در سمت راست پنجره راهنما Done را انتخاب نمایید.
ممنون که با ما همراه بودید. سوالاتتان را زیر همین پست کامنت کنید.
در قسمت بعد آزمون بخش چهارم را خواهیم داشت 🙂