بهینه سازی پروژه Data Warehouse و SSIS برای دیتابیس pubs)

✅ توضیح کامل پروژه (Project Description) من یک پروژه‌ی کامل Data Warehouse + SSIS + ETL مربوط به دیتابیس Pubs دارم که شامل موارد زیر است: دیتابیس عملیاتی (OLTP) دیتابیس Staging دیتابیس Data Warehouse پروژه کامل SSIS شامل: پکیج‌های Dim پکیج‌های Fact Full Load Incremental Load با استفاده از CDC پکیج Orchestration فایل‌ها به‌صورت Zip آماده هستند (dtproj, dtsx, ispac, backup, sql) ✅ هدف من این است که این پروژه: از نظر طراحی دیتامدل استانداردسازی شود از نظر کارایی و معماری بهینه‌سازی شود و به یک پروژه DW/ETL تمیز، حرفه‌ای و Production-Ready تبدیل شود. --- ✅ وظایف دقیق فریلنسر (Scope of Work) فریلنسر باید دقیقاً کارهای زیر را انجام دهد: --- 1️⃣ اصلاح و استانداردسازی دیتابیس Staging 🔹 مشکلات فعلی: نام بعضی جدول‌ها دارای فاصله است (مثل Staging Geography) نام‌گذاری ستون‌ها یکدست نیست بعضی جدول‌ها ساختار بهینه برای ETL ندارند ✅ خروجی مورد انتظار: طراحی مجدد Staging با مشخصات: بدون فاصله در اسم جدول‌ها اسکیما جدا (stg) جدول‌های استاندارد: Publisher Store Job Employee Author Title TitleAuthor Sales (با ستون OperationType برای Incremental) --- 2️⃣ بازطراحی Data Warehouse (DW) به صورت حرفه‌ای ✅ طراحی مجدد ابعاد (Dimensions): DimDate DimGeography DimPublisher DimStore DimJob DimEmployee DimAuthor (SCD Type 2) DimTitle (SCD Type 2) ✅ طراحی Factها: FactSales (با مقادیر مالی واقعی) FactTitleAuthor (Bridge / Fact) ✅ ویژگی‌های اجباری: استفاده از Surrogate Key افزودن ستون‌های: ValidFrom ValidTo CurrentFlag افزودن شاخص‌های (Index) مناسب رعایت Star Schema واقعی --- 3️⃣ بهینه‌سازی FactSales از نظر تحلیلی ✅ وضعیت فعلی: FactSales فقط qty و چند ستون متنی دارد. ✅ وضعیت مطلوب: افزودن: UnitPrice GrossAmount DiscountPercent DiscountAmount NetAmount امکان گزارش‌گیری مالی مستقیم بدون Join اضافی --- 4️⃣ اصلاح و بازنویسی پکیج‌های SSIS بر اساس ساختار جدید ✅ اصلاح پکیج‌های زیر با ساختار جدید: Dim Packages: DimPublisher DimGeography DimStore DimTitle DimJob DimEmployee DimAuthor Fact Packages: FactSales (Full Load) FactSales (Incremental Load via CDC) FactTitleAuthor ✅ Orchestration: حفظ ترتیب درست اجرا وابستگی صحیح بین Dimها و Factها ✅ Staging Load: Load از OLTP → Staging Load از Staging → DW --- 5️⃣ اضافه کردن لاگ‌گیری حرفه‌ای ETL ✅ طراحی و اتصال SSIS به: جدول: ETL_Batch ETL_ErrorLog ✅ ثبت: Start / End Time تعداد رکورد Insert / Update / Delete خطاهای Data Flow --- 6️⃣ تحویل نهایی مورد انتظار (Deliverables) فریلنسر موظف است موارد زیر را تحویل دهد: ✅ 1. فایل Backup نهایی دیتابیس بهینه‌شده (.bak) ✅ 2. پروژه کامل SSIS اصلاح‌شده (.sln, .dtproj, .dtsx, .ispac) ✅ 3. اسکریپت ساخت DW و Staging (.sql) ✅ 4. مستند مختصر شامل: Star Schema نهایی جریان ETL نحوه اجرای Full و Incremental Load --- ✅ تکنولوژی‌ها (Technical Requirements) SQL Server SSIS (SQL Server Integration Services) Visual Studio / SSDT CDC (Change Data Capture) Data Warehouse Modeling Star Schema SCD Type 2 ---