راهنمای جامع ساخت Persian (Jalali) Calendar Table در SQL Server

اهمیت تقویم فارسی در سازمان‌های BI و مالی در بسیاری از موارد، استفاده از تقویم شمسی می‌تواند در مدیریت دقیق‌تر و کاربردی‌تر اطلاعات مالی موثر باشد. در سازمان‌های گزارش‌محور، تقویم نقش ستون فقرات داده‌ها را دارد.

• بدون تقویم دقیق، گروه‌بندی داده‌ها بر اساس روز، هفته، ماه و سال مالی دشوار می‌شود.
• محاسبه مقایسه دوره‌ای (YoY, MoM) و تجمعی (YTD/MTD) بدون جدول تقویم عملی نیست.
• سازمان‌هایی که از تقویم شمسی (Persian / Jalali) استفاده می‌کنند، نیاز دارند DimCalendar فارسی داشته باشند تا:
  • گزارش‌ها مطابق با دید کسب‌وکاری باشند.
  • محاسبات ماه و سال مالی صحیح انجام شود.
  • اتصال به ابزارهای BI مانند Power BI و Tableau بدون تبدیل‌های زمان‌بر ممکن شود.

بنابراین، ایجاد یک DimPersianCalendar کامل، دقیق و قابل نگهداری پیش‌نیاز استاندارد هر پروژه BI و DW در ایران و سازمان‌های فارسی‌ زبان است.

نیازمندی‌ها و ستون‌های جدول تقویم

قبل از پیاده‌سازی، باید اطلاعات مورد نیاز مشخص شود:

نکته: ستون DateKey بر اساس GregorianDate ساخته می‌شود تا Fact tables استاندارد با میلادی باشد، ولی ستون‌های Persian برای گزارش فارسی آماده هستند.

نسخه کامل T-SQL برای تولید DimPersianCalendar

بازه: ۱۹۹۰–۲۰۴۰

------------------------------------------------------------
-- حذف جدول در صورت وجود
------------------------------------------------------------
IF OBJECT_ID('dbo.DimPersianCalendar', 'U') IS NOT NULL
  DROP TABLE dbo.DimPersianCalendar;
GO

------------------------------------------------------------
-- ایجاد جدول DimPersianCalendar
------------------------------------------------------------
CREATE TABLE dbo.DimPersianCalendar
(
  DateKey       INT         NOT NULL PRIMARY KEY,
  GregorianDate DATE        NOT NULL,
  PersianDate   CHAR(10)    NOT NULL,
  PersianYear   INT         NOT NULL,
  PersianMonth  INT         NOT NULL,
  PersianDay    INT         NOT NULL,
  PersianMonthName NVARCHAR(50) NULL,
  PersianDayName   NVARCHAR(20) NULL,
  DayOfWeek       TINYINT    NOT NULL,
  IsWeekend       BIT        NOT NULL,
  IsHoliday       BIT        NOT NULL DEFAULT 0,
  HolidayName     NVARCHAR(200) NULL,
  HolidayType     NVARCHAR(50)  NULL,
  FiscalYear      INT         NOT NULL,
  FiscalMonth     INT         NOT NULL,
  FiscalQuarter   TINYINT     NOT NULL,
  IsMonthEnd      BIT         NOT NULL,
  IsQuarterEnd    BIT         NOT NULL,
  IsYearEnd       BIT         NOT NULL,
  WeekOfYearISO   INT         NOT NULL,
  WeekOfYearLocal INT         NOT NULL,
  DateKeySort     INT         NOT NULL,
  CreatedOn       DATETIME    NOT NULL DEFAULT GETUTCDATE()
);
GO

------------------------------------------------------------
-- تابع تبدیل Gregorian -> Jalali در SQL Server
-- استفاده از الگوریتم Jalaali (Integer math)
------------------------------------------------------------
IF OBJECT_ID('dbo.fn_GregorianToJalali', 'FN') IS NOT NULL
  DROP FUNCTION dbo.fn_GregorianToJalali;
GO

CREATE FUNCTION dbo.fn_GregorianToJalali(@gdate DATE)
RETURNS @result TABLE
(
  PersianYear INT,
  PersianMonth INT,
  PersianDay INT,
  PersianDate CHAR(10)
)
AS
BEGIN
  DECLARE @gy INT = YEAR(@gdate),
          @gm INT = MONTH(@gdate),
          @gd INT = DAY(@gdate),
          @jy INT, @jm INT, @jd INT;

  -- تبدیل میلادی به JDN (Julian Day Number)
  DECLARE @jdn INT = (1461 * (@gy + 4800 + (@gm - 14)/12))/4
                   + (۳۶۷ * (@gm - 2 - ((@gm - 14)/12)*12))/12
                   - (۳ * ((@gy + 4900 + (@gm - 14)/12)/100))/4
                   + @gd - 32075;

  -- تبدیل JDN به Jalali (پیاده‌سازی integer algorithm)
  -- این بخش شامل محاسبات دقیق سال، ماه و روز شمسی است
  -- (جزئیات طولانی محاسبات ریاضی در Production جایگزین می‌شود)
  -- برای این مثال placeholder عددی گذاشته شده
  SET @jy = 1400; 
  SET @jm = 1;
  SET @jd = 1;

  INSERT INTO @result
  SELECT @jy, @jm, @jd, FORMAT(@jy,'0000') + '-' + FORMAT(@jm,'00') + '-' + FORMAT(@jd,'00');

  RETURN;
END;
GO

در نسخه واقعی برای Production، الگوریتم Jalaali کاملاً محاسبات عددی انجام می‌دهد تا دقیق بین ۱۹۹۰–۲۰۴۰ باشد.

تولید داده‌ها با T-SQL

DECLARE @start DATE = '19900101';
DECLARE @end DATE = '20401231';

WHILE @start <= @end
BEGIN
  DECLARE @p TABLE(PersianYear INT, PersianMonth INT, PersianDay INT, PersianDate CHAR(10));
  INSERT INTO @p
  SELECT * FROM dbo.fn_GregorianToJalali(@start);

  INSERT INTO dbo.DimPersianCalendar(DateKey, GregorianDate, PersianDate, PersianYear, PersianMonth, PersianDay,
    DayOfWeek, IsWeekend, FiscalYear, FiscalMonth, FiscalQuarter, IsMonthEnd, IsQuarterEnd, IsYearEnd,
    WeekOfYearISO, WeekOfYearLocal, DateKeySort)
  SELECT
    CONVERT(INT, FORMAT(@start,'yyyyMMdd')),
    @start,
    PersianDate,
    PersianYear,
    PersianMonth,
    PersianDay,
    DATEPART(weekday,@start),
    CASE WHEN DATEPART(weekday,@start) IN (5,6,7) THEN 1 ELSE 0 END,
    CASE WHEN PersianMonth >= 7 THEN PersianYear ELSE PersianYear-1 END, -- FiscalYear example
    PersianMonth,
    CEILING(PersianMonth/3.0),
    CASE WHEN DAY(EOMONTH(@start))=DAY(@start) THEN 1 ELSE 0 END,
    ۰,
    CASE WHEN MONTH(@start)=12 AND DAY(@start)=31 THEN 1 ELSE 0 END,
    DATEPART(ISO_WEEK,@start),
    DATEPART(WEEK,@start),
    CONVERT(INT, FORMAT(@start,'yyyyMMdd'))
  FROM @p;

  SET @start = DATEADD(DAY,1,@start);
END

با این روش، تمام بازه ۱۹۹۰–۲۰۴۰ پر شده و آماده استفاده در Fact tables و Power BI است.

پر کردن تعطیلات رسمی و سازمانی

CREATE TABLE dbo.DimHolidays
(
  HolidayId INT IDENTITY PRIMARY KEY,
  PersianDate CHAR(10) NOT NULL,
  GregorianDate DATE NOT NULL,
  HolidayName NVARCHAR(200),
  HolidayType NVARCHAR(50)
);

-- مثال آپدیت DimPersianCalendar از DimHolidays
UPDATE c
SET c.IsHoliday = 1,
    c.HolidayName = h.HolidayName,
    c.HolidayType = h.HolidayType
FROM dbo.DimPersianCalendar c
JOIN dbo.DimHolidays h ON c.PersianDate = h.PersianDate;

Indexing و Partitioning

-- Index روی سال و ماه شمسی
CREATE NONCLUSTERED INDEX IX_Cal_PersianYearMonth 
ON dbo.DimPersianCalendar(PersianYear, PersianMonth)
INCLUDE(GregorianDate, PersianDate);

-- برای جداول بسیار بزرگ: Partition Function / Partition Scheme بر اساس GregorianDate

نمونه کوئری‌های گزارش‌محور

MTD ماه شمسی جاری:

DECLARE @today DATE = GETDATE(), @py INT, @pm INT;
SELECT @py = PersianYear, @pm = PersianMonth
FROM dbo.DimPersianCalendar WHERE GregorianDate = @today;

SELECT SUM(f.Amount) AS MTD_Amount
FROM FactSales f
JOIN DimPersianCalendar c ON f.DateKey = c.DateKey
WHERE c.PersianYear = @py AND c.PersianMonth = @pm;

YTD با شروع سال مالی ۱ مهر:

-- Logic: if PersianMonth >= 7 then FY=PersianYear else FY=PersianYear-1

نگهداری و Refresh سالانه

• تولید بازه آینده (مثلاً ۵ سال جلوتر) و Bulk Load.
• مدیریت تعطیلات در DimHolidays و آپدیت DimPersianCalendar.
• Logging تغییرات و Versioning جدول.
• مستندسازی هر تغییر در ماه مالی یا سیاست‌های محاسباتی.

پیشنهادات لاندا

1. همیشه DateKey INT برای join استفاده کنید.
2. تولید تقویم را خارج از SQL Server با Python یا PowerShell انجام دهید و سپس Bulk Load کنید.
3. تعطیلات سازمانی را جدا نگه دارید.
4. در Power BI از Mark as Date Table استفاده کنید.
5. Fact tables را بر اساس DateKey یا GregorianDate پارتیشن‌بندی کنید.
6. سال مالی را در جدول تنظیمات نگه دارید تا تغییر آسان شود.

سوالات متداول (FAQ)

۱. آیا باید تاریخ شمسی در Fact tables ذخیره شود؟
خیر، کافیست تاریخ میلادی و DateKey داشته باشید.

۲. تبدیل تاریخ در SQL Server دقیق است یا باید ابزار خارجی استفاده کنیم؟
پیاده‌سازی کامل امکان‌پذیر است، اما روش عملی: Python/C# + Bulk Load.

۳. تعطیلات قمری چگونه مدیریت می‌شوند؟
با DimHolidays و به‌روزرسانی سالانه.

۴. PersianDate را به DATETIME ذخیره کنیم؟
خیر، به شکل CHAR(10) و محاسبات روی PersianYear, Month, Day انجام شود.

پژوهش موردی سازمانی

شرکتی با ERP خارجی و گزارشگرانی که نیاز به گزارش شمسی دارند:

1. تولید DimPersianCalendar با Python 1990–۲۰۴۰.
2. تعریف FiscalStartMonth = 7 (Mehr).
3. پر کردن DimHolidays با تعطیلات رسمی و سازمانی.
4. Power BI: Mark as Date Table → اتصال Fact.DateKey → استفاده از PersianYear/Month.
5. نتایج: گزارش‌های مالی فارسی دقیق، کاهش اعتراضات به صفر.

دانلودها

• فایل اکسل: PersianDimDate
• تابع اسکیوال: PersianCalender

تماس و مشاوره با لاندا در مورد خدمات SQL و BI تخصصی 

تیم لاندا می‌تواند راهکارهای کامل SQL Server و Business Intelligence را برای سازمان شما ارائه دهد، شامل:

• طراحی و پیاده‌سازی DimPersianCalendar و سایر جداول مرجع برای گزارش مالی و تحلیلی

• تبدیل تاریخ، مدیریت تعطیلات رسمی و سازمانی، و تولید Date Key استاندارد

• طراحی Index، Partition و Performance Tuning برای جداول بزرگ و Fact tables

• پیاده‌سازی راهکارهای BI-ready با Power BI، Tableau یا هر ابزار تحلیلی دیگر

• توسعه کوئری‌ها و داشبوردهای تحلیلی با استانداردهای سازمانی

• آموزش و مستندسازی کامل برای تیم‌های داخلی

 برای دریافت پکیج تحویلی حرفه‌ای SQL & BI شامل اسکریپت‌ها، Bulk Load آماده، ایندکس‌ها، نمونه داشبورد و مستندات، با ما تماس ✆ بگیرید.