انتخاب بین Date یا Integer برای نمایش تاریخ در Power BI و Tabular

مقدمه

این مقاله دانش فنی لازم را برای انتخاب بین استفاده از Date یا Integer برای ایجاد رابطه بین جدول واقعیت و بُعد تاریخ در اختیار شما قرار می‌دهد.

سوالی که اغلب در طول طراحی یک مدل داده Power BI پرسیده می‌شود این است که آیا بهتر است از یک ستون Integer یا یک ستون Datetime برای پیوند دادن جدول واقعیت با بُعد Date استفاده شود؟ به طور معمول استفاده از اعداد صحیح همیشه انتخاب بهتری در طراحی پایگاه داده بوده است. با این حال، Tabular یک پایگاه داده ستونی در حافظه است و معماری آن کاملاً متفاوت از پایگاه داده‌های رابطه‌ای است که ممکن است بیشتر با آن‌ها کار کرده باشید.

در واقع، در Tabular هیچ تفاوت فنی بین استفاده از Datetime یا یک Integer برای ایجاد یک رابطه وجود ندارد. اندازه پایگاه داده، سرعت اجرای کوئری و هر جزئیات فنی دیگر کاملاً یکسان هستند. بنابراین، انتخاب مربوط به جنبه‌های فنی نیست و به راحتی با هرکدام از آن‌ها طراحی انجام می‌شود. بسته به نیازهای خاص مدل خود، ممکن است یک نوع داده را در مقابل دیگری ترجیح دهید. در رایج‌ترین سناریوها، یک Datetime بهتر است زیرا امکانات بیشتری برای محاسبه مقادیر در تاریخ‌ها بدون اتکا به روابط فراهم می‌کند. با این اوصاف، اگر مدل شما از اعداد صحیح استفاده می‌کند و نیازی به انجام محاسبات در تاریخ‌های نشان‌ داده ‌شده در جدول ندارید، می‌توانید راحت‌ترین نوع داده را انتخاب کنید، یعنی همان نوعی که تاکنون در منبع داده اصلی استفاده شده است.

در ادامه مقاله به اثبات جملات قبلی می‌پردازیم و جزئیات فنی در مورد نحوه آزمایش عملکرد مربوط به دو گزینه استفاده از ستون Datetime یا یک ستون Integer را ارائه می‌دهیم.

ما از پایگاه داده Contoso با ۲ میلیارد ردیف در جدول Sales و تاریخ‌هایی در بازه ۱۰ ساله استفاده می‌کنیم. ما دو پایگاه داده متفاوت ایجاد کردیم: در اولی ستون Order Date به عنوان Datetime ذخیره شده است و در دومی همان ستون Order Date به عنوان یک عدد صحیح با فرمت YYYYMMDD ذخیره می‌شود.

قبل از شروع آزمون، کمی تئوری‌های اولیه لازم است. هنگامی که یک ستون در موتور VertiPaq (موتور پایگاه داده در حافظه Tabular) بارگذاری می‌شود، با استفاده از hash-encoding یا encoding مقدار، فشرده‌سازی انجام می‌شود. hash-encoding رایج‌ترین تکنیک است: VertiPaq یک دیکشنری از مقادیر موجود در ستون ایجاد می‌کند و به جای ذخیره مقادیر، ایندکس آن‌ها را در دیکشنری ذخیره می‌کند. به عبارت دیگر، صرف ‌نظر از نوع داده اصلی ستون، مقادیر ستون به صورت اعداد صحیح ذخیره می‌شوند. بنابراین، وقتی VertiPaq، از hash-encoding برای یک ستون استفاده می‌کند، نوع داده اصلی آن ستون از نقطه نظر فنی اهمیتی ندارد. این ملاحظات در سناریوی ما مهم هستند، زیرا VertiPaq همیشه از hash-encoding برای ستون‌هایی که درگیر یک رابطه هستند استفاده می‌کند.

کارایی hash-encoding تنها به دو فاکتور بستگی دارد: تعداد مقادیر متمایز ستون و توزیع داده‌ها. صرف نظر از اینکه از یک Integer یا یک DateTime استفاده کنیم، روی این دو فاکتور تاثیر یکسانی دارند. به همین دلیل است که اندازه دو ستون یکسان خواهد بود. شکل زیر اندازه ستون Order Date را با استفاده از Datetime نشان می‌دهد.

همان جدول بالا با ستون DateKey که از نوع Integer است به شکل زیر است.

همان‌طور که می‌بینید، استفاده از Integer یک دیکشنری کوچکتر تولید می‌کند. با کمال تعجب، ستون با نوع Integer سایز ستون کمی بزرگتر تولید می‌کند. حدود ۲.۶ گیگابایت فضا اشغال می‌کند. در حالی که هنگام استفاده از ستون با نوع Datetime حدود ۲.۵ گیگابایت فضا می‌گیرد. به طور کلی، این تفاوت ناچیز است و احتمالاً نتیجه انتخاب متفاوت در ترتیب مرتب‌سازی برای برخی از بخش‌های جدول است.

از آنجایی که دو ستون در نهایت اندازه یکسان دارند، تمام تست‌های کارایی که ما انجام دادیم، کارایی یکسانی را نشان می‌دهند. از نقطه نظر اجرای کوئری، هیچ تفاوتی بین استفاده از Integer یا DateTime وجود ندارد.

بررسی‌ها نشان می‌دهد همان طور که انتظار می‌رفت تفاوتی از دیدگاه ذخیره‌سازی و کارایی وجود ندارد. اما هنگام انجام محاسبات، بین دو نوع داده Integer و DateTime تفاوت وجود دارد. برای مثال، اگر بخواهیم میانگین روزهای تحویل را با کم کردن تاریخ سفارش (Order Date) از تاریخ تحویل (Delivery Date) محاسبه کنیم، در سناریویی که جدول Sales تاریخ‌ها را به صورت DateTime ذخیره می‌کند، محاسبه بسیار ساده‌تر است. در واقع، اگر جدول Sales فقط حاوی DateKey به عنوان ستون با نوع Integer باشد، برای محاسبه ابتدا باید تاریخ واقعی را از جدول Date بازیابی کنیم و سپس محاسبه را انجام دهیم.

در اینجا نمونه کوئری، زمانی که جدول Sales یک DateTime را ذخیره می‌کند، به صورت زیر است:

DEFINE
MEASURE Sales[AvgDeliveryDays] =
CALCULATE (
AVERAGEX ( Sales, Sales[Delivery Date] - Sales[Order Date] ),
NOT ISBLANK ( Sales[Delivery Date] )
)
EVALUATE
SUMMARIZECOLUMNS (
'Date'[Year],
'Date'[Month],
"Avg delivery", [AvgDeliveryDays]
)

اگر Order Date را به صورت نوع Integer ذخیره کنیم، کوئری فوق کمی پیچیده‌تر است زیرا اعداد صحیح را برای انجام تفریق باید ابتدا به نوع DateTime تبدیل کند. کوئری به صورت زیر خواهد بود:

DEFINE
MEASURE Sales[AvgDeliveryDays] =
CALCULATE (
AVERAGEX (
Sales,
VAR DeliveryDate =
LOOKUPVALUE ( 'Date'[Date], 'Date'[DateKey], Sales[DeliveryDateKey] )
VAR OrderDate =
RELATED ( 'Date'[Date] )
RETURN
DeliveryDate - OrderDate
),
NOT ISBLANK ( Sales[DeliveryDateKey] )
)
EVALUATE
SUMMARIZECOLUMNS (
'Date'[Year],
'Date'[Month],
"Avg delivery", [AvgDeliveryDays]
)

همچنین تفاوت در پیچیدگی کد در کارایی آن نیز منعکس می‌شود. کوئری با استفاده از ستون Datetime در ۸۷۳ میلی ثانیه اجرا می‌شود:

از طرف دیگر، زمان مورد نیاز برای اجرای کوئری بر روی مدل با نوع Integer به ۳.۵ ثانیه زمان نیاز دارد که ۲.۶ ثانیه از ۳.۵ ثانیه توسط CallbackDataID که شامل استفاده از موتور فرمول (FE) است مورد نیاز است. به این ترتیب، از حافظه کش استفاده نمی‌کند.

برای اطمینان از کامل بودن سناریو می‌توان آن را بهینه‌تر کرد. در واقع، پیچیدگی کد در استفاده از LOOKUPVALUE است. ما از LOOKUPVALUE استفاده می‌کنیم زیرا استفاده از RELATED در یک رابطه غیرفعال در یک ردیف بسیار پیچیده است. یک راه حل ممکن این است که یک جدول DeliveryDate جدید ایجاد کنید که حاوی دو ستون DateKey و Date باشد و رابطه‌ای با جدول Sales بر اساس ستون Sales[DeliveryDateKey] داشته باشد. این جدول جدید یک جدول فنی است که تنها هدف آن استفاده از RELATED به جای LOOKUPVALUE است. کد به صورت زیر خواهد بود:

DEFINE
MEASURE Sales[AvgDeliveryDays] =
CALCULATE (
AVERAGEX (
Sales,
RELATED ( DeliveryDate[Date] ) - RELATED ( 'Date'[Date] )
),
NOT ISBLANK ( Sales[DeliveryDateKey] )
)
EVALUATE
SUMMARIZECOLUMNS (
'Date'[Year],
'Date'[Month],
"Avg delivery", [AvgDeliveryDays]
)

نتیجه‌گیری

بنابراین، می‌توان نتیجه گرفت که هیچ تفاوتی از نظر کارایی و ذخیره‌سازی بین استفاده از یک ستون Datetime با یک ستون Integer وجود ندارد. با این حال، از نظر قابلیت استفاده محاسباتی روی ستون‌ها، تفاوت وجود دارد. با ذخیره یک Datetime، مقدار ستون به راحتی در جدول Sales در دسترس است. این کار نوشتن کد DAX و انجام محاسبات بر اساس ستون Date را آسان‌تر می‌کند. اگر به صورت Integer ذخیره کنید، به دلیل مراحل اضافی مورد نیاز برای تبدیل مقدار Integer به DateTime، چنین محاسبه‌ای پیچیده‌تر است.

در صورت عدم وجود محاسبات مربوط به تاریخ‌ها، راه حل‌های Integer و DateTime تفاوتی ایجاد نمی‌کند. اما در صورتی که محاسباتی را انجام می‌دهید یا می‌خواهید در صورت وجود محاسبات در آینده، سرعت اجرای بالاتری داشته باشید، استفاده از Datetime قطعا انتخاب بهتری خواهد بود.