Parameters در Power Query؛ حذف Hard-code و آماده‌سازی Dev/Test/Prod

مقدمه؛ چرا Hard-code دردسر می‌شود؟

فرض کن Query مستقیم به مسیر C:\Data\Sales وصل شده. روی لپ‌تاپ تو عالی کار می‌کند، ولی وقتی پروژه به سرور یا محیط Production می‌رود مسیر عوض می‌شود. اگر مسیر، Server، Database یا تاریخ را همه‌جا داخل کد نوشته باشی، نگهداری پروژه سخت می‌شود.

Parameter کمک می‌کند مقدارهای قابل تغییر را از منطق اصلی Query جدا کنیم.

۲۷.۱. Parameter چیست؟

Parameter یک مقدار نام‌دار است که Query می‌تواند از آن استفاده کند؛ مثل ServerName، DatabaseName، FolderPath، StartDate یا Environment.

۲۷.۲. مثال ساده FolderPath

pFolderPath = "D:\BI\SalesFiles"

به جای نوشتن مسیر در چند Query، همه از pFolderPath استفاده می‌کنند.

۲۷.۳. ساخت Parameter

از Manage Parameters می‌توان Parameter جدید ساخت، نوع داده، مقدار فعلی و در صورت نیاز لیست مقادیر پیشنهادی را تعیین کرد.

۲۷.۴. Naming Convention

پیشوند p باعث می‌شود Parameterها در Query List سریع قابل تشخیص باشند.

۲۷.۵. استفاده در Folder.Files

Source = Folder.Files(pFolderPath)

حالا تغییر مسیر فقط از یک نقطه انجام می‌شود.

۲۷.۶. SQL Server Parameter

Source = Sql.Database(pServerName, pDatabaseName)

این الگو برای جابه‌جایی بین سرورها یا Databaseهای مختلف مفید است.

۲۷.۷. Parameter با Data Type درست

Parameter تاریخ را Date، عدد را Number و مسیر را Text تعریف کن. Type درست جلوی بخشی از خطاهای تبدیل را می‌گیرد.

۲۷.۸. Suggested Values

Parameter می‌تواند Any value، List of values یا Query-based پیشنهاد داشته باشد. برای Environment مثلاً List شامل DEV، TEST و PROD خواناتر است.

۲۷.۹. Environment Parameter

pEnvironment = "DEV"

۲۷.۱۰. Mapping محیط به Server

if pEnvironment = "DEV" then "SQL-DEV"
else if pEnvironment = "TEST" then "SQL-TEST"
else "SQL-PROD"

برای پروژه بزرگ بهتر است تنظیمات محیط ساختاریافته‌تر و قابل کنترل طراحی شوند.

۲۷.۱۱. Config Table

به جای تعداد زیادی if می‌توان جدول Configuration داشت که Environment را به Server، Database یا Path مربوط کند.

۲۷.۱۲. Parameter و امنیت

Parameter محل نگهداری Password نیست. Credentialها باید از سازوکارهای امن Data Source، Gateway و Service استفاده کنند.

نکته: رمز عبور را داخل M Code یا Parameter معمولی Hard-code نکن.

۲۷.۱۳. Parameter تاریخ

pStartDate = #date(2025, 1, 1)

برای محدود کردن بازه داده، تست Query یا بعضی سناریوهای Incremental می‌توان Parameter تاریخ داشت.

۲۷.۱۴. Filter با Parameter

Table.SelectRows(Source, each [OrderDate] >= pStartDate)

اگر Source از Query Folding پشتیبانی کند، جایگاه این Filter می‌تواند روی Performance اثر مهمی داشته باشد.

۲۷.۱۵. RangeStart و RangeEnd

در سناریوهای Incremental Refresh از Parameterهای تاریخ/زمان با الگوی مشخص استفاده می‌شود. جزئیات آن را در فصل Incremental Refresh عمیق‌تر بررسی می‌کنیم.

۲۷.۱۶. Parameter برای فایل نمونه

در پروژه‌های آموزشی یا توسعه می‌توان مسیر Sample Data را Parameter کرد تا توسعه‌دهنده بدون تغییر منطق Query داده آزمایشی متفاوتی انتخاب کند.

۲۷.۱۷. Parameter برای API

BaseUrl = pApiBaseUrl

Base URL یا بعضی Query Optionها می‌توانند Parameter شوند، ولی Credential و Token باید طبق روش امن Connector/API مدیریت شوند.

۲۷.۱۸. Dynamic Data Source؛ احتیاط

ساخت URL یا Source کاملاً پویا ممکن است روی Refresh در Service محدودیت‌هایی ایجاد کند. طراحی Parameter باید با قابلیت‌های Connector و Service واقعی تست شود.

۲۷.۱۹. Parameter و Query Folding

Parameter به خودی خود بد نیست؛ مهم این است که Transformation نهایی قابلیت Fold شدن را حفظ کند یا نه. Native Query و Source-specific behavior باید تست شوند.

۲۷.۲۰. Parameter و Native Query

اگر SQL دستی می‌نویسی، Parameterization باید با دقت امنیتی انجام شود. Concatenate کردن ورودی کنترل‌نشده به SQL می‌تواند طراحی بدی باشد.

نکته: برای ورودی‌های SQL، روش امن و پشتیبانی‌شده Connector را انتخاب کن؛ رشته‌سازی کورکورانه نکن.

۲۷.۲۱. Hard-codeهای رایج

۲۷.۲۲. مثال شرکت هانا

تیم BI سه محیط دارد: Development، Test و Production. Queryهای فروش نباید برای هر Release دستی ویرایش شوند.

pEnvironment تعیین می‌کند Query به Configuration مناسب همان محیط وصل شود.

۲۷.۲۳. معماری پیشنهادی

pEnvironment
→ EnvironmentConfig
→ Server / Database / Folder
→ Staging Queries
→ Model

۲۷.۲۴. Dev Data کوچک‌تر

در Development گاهی می‌خواهیم فقط بازه کوچکی از داده را بخوانیم تا Refresh سریع باشد. Parameter تاریخ یا Row Limit کنترل‌شده می‌تواند کمک کند.

اما قبل از Release باید رفتار Production جداگانه Validate شود.

۲۷.۲۵. Test Environment

محیط Test باید تا حد امکان رفتار Production را شبیه‌سازی کند، ولی با داده و دسترسی کنترل‌شده. Parameter فقط ابزار انتخاب محیط است؛ جای فرآیند Release را نمی‌گیرد.

۲۷.۲۶. Production

در Production تغییر Parameter باید کنترل‌شده باشد. تغییر اشتباه Server یا Folder می‌تواند گزارش را به منبع غلط وصل کند.

۲۷.۲۷. Parameter Documentation

۲۷.۲۸. Default Value

Default باید برای توسعه امن باشد. مثلاً بهتر است فایل نمونه یا DEV به صورت پیش‌فرض باشد، نه Production حساس؛ البته سیاست تیم تعیین‌کننده است.

۲۷.۲۹. Required Parameter

برای Parameter حیاتی می‌توان منطق Validation داشت تا مقدار خالی یا ناشناخته باعث اتصال اشتباه نشود.

۲۷.۳۰. Validation Environment

if List.Contains({"DEV","TEST","PROD"}, pEnvironment)
then pEnvironment
else error "Invalid environment"

۲۷.۳۱. Parameter Query یا Parameter UI؟

گاهی Config را به صورت Query/Table نگه می‌داریم و گاهی Manage Parameters مناسب‌تر است. انتخاب به تعداد تنظیمات، Governance و معماری Deployment بستگی دارد.

۲۷.۳۲. Config Table مزیتش چیست؟

وقتی تنظیمات زیاد می‌شوند، جدول Config خواناتر است و می‌توان چند ویژگی محیط را یکجا مدیریت کرد.

۲۷.۳۳. Parameter و Functions

Functionهای M می‌توانند ورودی بگیرند. این با Parameter سراسری فرق دارد: Function Parameter برای هر فراخوانی مقدار متفاوت می‌گیرد.

۲۷.۳۴. مثال Function

(FileContent as binary) as table =>
    ...

در Combine Files دقیقاً با چنین مفهومی برخورد کردیم.

۲۷.۳۵. Global Parameter یا Function Argument

۲۷.۳۶. خطای رایج؛ Parameter برای همه چیز

هر ثابت کوچکی لازم نیست Parameter شود. اگر بیش از حد Parameter بسازی، پروژه گیج‌کننده می‌شود. فقط چیزهایی را بیرون بکش که واقعاً Configuration هستند.

۲۷.۳۷. خطای رایج؛ Password Parameter

Parameter معمولی Secrets Vault نیست. Credential را در جای درست خودش مدیریت کن.

۲۷.۳۸. خطای رایج؛ PROD به عنوان Default

در بعضی تیم‌ها این کار باعث می‌شود توسعه‌دهنده ناخواسته Query سنگین را روی Production اجرا کند. Default را با Governance تیم هماهنگ کن.

۲۷.۳۹. خطای رایج؛ مسیر شخصی

C:\Users\Ali\Desktop\Sales

این مسیر برای پروژه تیمی و Production شکننده است. مسیر سازمانی یا Storage مناسب انتخاب کن.

۲۷.۴۰. Parameter و Gateway

اگر Server یا Folder تغییر کند، Data Source Mapping و Gateway هم باید با مقصد جدید سازگار باشد. Parameter به تنهایی Gateway را پیکربندی نمی‌کند.

۲۷.۴۱. Parameter و Deployment

در فرآیند انتشار می‌توان تنظیمات محیط مقصد را مدیریت کرد تا Artifact یکسان با Configuration مناسب اجرا شود.

روش دقیق بسته به قابلیت‌های جاری Power BI/Fabric و معماری تیم است.

۲۷.۴۲. تست قبل از Release

۲۷.۴۳. Configuration Drift

اگر Parameterهای Desktop، Service و مستندات با هم هماهنگ نباشند، Configuration Drift ایجاد می‌شود. مالک و فرآیند تغییر تنظیمات را مشخص کن.

۲۷.۴۴. Audit تنظیمات

برای سیستم‌های حساس ثبت اینکه چه Environment و Sourceای در Release استفاده شده می‌تواند بخشی از مستندات استقرار باشد.

۲۷.۴۵. Best Practiceها

Hard-codeهای محیطی را شناسایی کن.

Parameterها را نام‌گذاری استاندارد کن.

Secret را Parameter نکن.

مقادیر مجاز Environment را Validate کن.

Config را مستند کن.

تغییر Source را با Gateway و Service تست کن.

DEV/TEST/PROD را از منطق Transform جدا نگه دار.

از Parameter فقط جایی استفاده کن که واقعاً Configuration است.

۲۷.۴۶. تمرین پایه

یک pFolderPath بساز و Query فروش را طوری تغییر بده که Folder.Files از آن استفاده کند.

۲۷.۴۷. تمرین SQL

pServerName و pDatabaseName تعریف کن و یک Source فرضی Sql.Database را با آن‌ها بساز.

۲۷.۴۸. تمرین Environment

pEnvironment با سه مقدار DEV/TEST/PROD بساز و بر اساس آن ServerName مناسب را انتخاب کن.

۲۷.۴۹. تمرین Validation

اگر مقدار Environment چیزی غیر از سه مقدار مجاز بود، Query باید Error قابل فهم تولید کند.

۲۷.۵۰. تمرین چالشی

یک Configuration Table طراحی کن که برای هر محیط Server، Database، FolderPath و StartDate داشته باشد. توضیح بده کدام موارد نباید داخل این جدول ذخیره شوند.

۲۷.۵۱. پروژه فصل؛ Environment-Aware BI شرکت هانا

هدف این است که یک نسخه Query بتواند بدون ویرایش منطق اصلی در محیط‌های مختلف اجرا شود و اتصال اشتباه به Production هم قابل کنترل باشد.

جمع‌بندی فصل بیست‌وهفتم

Parameterها پروژه Power Query را قابل تنظیم‌تر و قابل استقرارتر می‌کنند. نکته اصلی این است که Configuration را از Transformation Logic جدا کنیم.

Configuration ≠ Transformation Logic

در فصل بیست‌وهشتم وارد زبان M می‌شویم؛ ساختار let/in، Stepها، توابع، List/Record/Table و اینکه چطور از حالت فقط کلیک‌کردن در Power Query عبور کنیم.