در بسیاری از پروژه‌های دات نت، نیاز به استفاده از فایلهای نرم افزار آفیس، از قبیل ورد و اکسل و ... وجود دارد. برای مثال گاهی لازم است اطلاعات  یک گرید، یا هر منبع داده‌ای، در قالب اکسل به کاربر نمایش داده شود. بدین شکل که این فایلها در زمان اجرا ساخته شده و به کاربر نمایش داده شود .حال فرض کنید شما روی سیستم خودتان Office2007 را نصب کرده اید و به اسمبلی‌های این ورژن دسترسی دارید. البته بدون نیاز به نصب آفیس نیز میتوان به این توابع دسترسی داشت و از آنها در برنامه استفاده کرد که همان استفاده از  Primary Interop Assemblies میباشد. مشکلی که ممکن است پیش آید این است که در کامپیوترهای کاربران ممکن است ورژن‌های مختلفی از آفیس نصب باشد مانند 2003 -2007-2010-2013 و اگر با ورژن اسمبلی‌هایی که فراخوانی‌های فایلهای اکسل از طریق آن انجام شده باشد متفاوت باشد، برنامه اجرا نمی‌شود .

در حالت معمول برای نمایش یک فایل آفیس مثل اکسل در برنامه، ابتدا اسمبلی مربوطه را (اکسل در این مثال) که به نام Microsoft.Office.Interop.Excel میباشد به اسمبلی‌های برنامه اضافه کرده (از طریق add reference) و برای نمایش یک فایل اکسل در زمان اجرا از کدهای زیر استفاده مینماییم :

try
            {
               var application = (Microsoft.Office.Interop.Excel.Application)Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application"));  
               Workbook  wrkBook;
                var  wbk = application.Workbooks;
                 wrkBook = wbk.Add();
                wrkBook.Activate();
                application.Visible = true;
             }
            catch (Exception ex)
            {
                Error(ex.Message);
            }

حال اگر آفیس 2010 به عنوان مثال در سیستم ما نصب باشد، ورژن این اسمبلی 14 می‌باشد و اگر این برنامه را در کامپیوتر کلاینتی که آفیس 2007 بر روی آن نصب باشد انتشار دهیم اجرا نمیشود. برای حل این مشکل بنده با استفاده از روش dynamic این موضوع را حل کردم و بنظر می‌رسد راه‌های دیگری نیز برای حل آن وجود داشته باشد.

در این روش با توجه به ورژن آفیسی که بر روی سیستم کاربر نصب شده اسمبلی مربوطه را از سیستم کاربر لود کرده و فایلهای آفیس را اجرا مینماییم. در ابتدا تشخیص میدهیم چه ورژنی از آفیس بر روی سیستم کاربر نصب است :

 string strVersion = null;
                dynamic objEApp =Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application"));
                if (objEApp.Version == "12.0")
                {
                    strVersion = "2007";
                }
                else if (objEApp.Version == "14.0")
                {
                    strVersion = "2010";
                }

روش دیگر برای انجام اینکار استفاده از اطلاعات رجیستری ویندوز است : 

            string strEVersionSubKey = "\\Excel.Application\\CurVer";       
            string strValue = null; //Value Present In Above Key
            string strVersion = null; //Determines Excel Version 
            RegistryKey rkVersion = null; //Registry Key To Determine Excel Version 
            rkVersion = Registry.ClassesRoot.OpenSubKey(strEVersionSubKey, false); //Open Registry Key 
            if ((rkVersion != null)) //If Key Exists
            {
                strValue = (string)rkVersion.GetValue(string.Empty); //Get Value 
                strValue = strValue.Substring(strValue.LastIndexOf(".") + 1); //Store Value 
                switch (strValue) //Determine Version
                {
                    case "11":
                        strVersion = "2003";
                        break;
 
                    case "12":
                        strVersion = "2007";
                        break;
 
                    case "14":
                        strVersion = "2010";
                        break;
 
                }

if (strVersion == "2007")
                {
                    string strAssemblyOff2007 =
                        "Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c";
                    try
                    {
                        Assembly xslExcelAssembly = Assembly.Load(strAssemblyOff2007); //Load Assembly
                        Type type = xslExcelAssembly.GetTypes().Single(t => t.Name == "ApplicationClass");
                        dynamic application = Activator.CreateInstance(type);
                        var workbooks = application.Workbooks;
                        var workbook = workbooks.Add();
                        var worksheet = workbook.Worksheets[1];
                        workbook.Activate();
                        application.Visible = true;
                    }
                    catch (Exception ex)
                    {
                    }
                }

در این قسمت به پیاده سازی و توضیح مدل‌های انجمن خواهیم پرداخت. قبل از شروع پیشنهاد می‌کنم مقالات قبلی را مطالعه کنید.
همکاران این قسمت:
سلمان معروفی 
سید مجتبی حسینی 
پیشنیاز این قسمت:
مقالات SQL Antipattern 

سعی کردیم چندین پروژه‌ی سورس باز را هم بررسی کنیم و در نهایت کاملترین و بهترین روش را پیاده سازی کنیم. NForum ، MyBB ، MVCForum ، بخش CMS مربوط به SmartStore و ساختار دیتابیس StackOverFlow ازجمله‌ی آنها هستند.

  /// <summary>
    /// Represents the Forum
    /// </summary>
    public class Forum
    {

        #region Properties
        /// <summary>
        /// gets or sets Id that Identify Forum
        /// </summary>
        public virtual long Id { get; set; }
        /// <summary>
        /// gets or sets Forum's title
        /// </summary>
        public virtual string Title { get; set; }
        /// <summary>
        /// gets or sets Description of forum
        /// </summary>
        public virtual string Description { get; set; }
        /// <summary>
        /// gets or sets value indicating Custom Slug
        /// </summary>
        public virtual string SlugUrl { get; set; }
        /// <summary>
        /// gets or sets order for display forum
        /// </summary>
        public virtual long DisplayOrder { get; set; }
        /// <summary>
        /// Indicating This Forum is Active or Not
        /// </summary>
        public virtual bool IsActive { get; set; }
        /// <summary>
        /// Indicating This Forum is Close or Not
        /// </summary>
        public virtual bool IsClose { get; set; }
        /// <summary>
        /// Indicating This Forum is Private or Not
        /// </summary>
        public virtual bool IsPrivate { get; set; }
        /// <summary>
        /// sets or gets password for login to Private forums
        /// </summary>
        public virtual string PasswordHash { get; set; }
        /// <summary>
        /// sets or gets depth of forum in tree structure of forums
        /// </summary>
        public virtual int Depth { get; set; }
        /// <summary>
        /// sets or gets Count of  posts That they are Approved
        /// </summary>
        public virtual long ApprovedPostsCount { get; set; }
        /// <summary>
        /// sets or gets Count of  topics That they are Approved
        /// </summary>
        public virtual long ApprovedTopicsCount { get; set; }
        /// <summary>
        /// Gets or sets the id of last topic
        /// </summary>
        public virtual long LastTopicId { get; set; }
        /// <summary>
        /// gets or sets date of creation of last topic
        /// </summary>
        public virtual DateTime? LastTopicCreatedOn { get; set; }
        /// <summary>
        /// gets or sets title of last topic
        /// </summary>
        public virtual string LastTopicTitle { get; set; }
        /// <summary>
        /// gets or sets creator of last topic
        /// </summary>
        public virtual string LastTopicCreator { get; set; }
        /// <summary>
        /// gets or sets id of creator that create last topic
        /// </summary>
        public virtual long LastTopicCreatorId { get; set; }
        /// <summary>
        /// Indicate in this Forum Moderate Topics Before Display 
        /// </summary>
        public virtual bool ModerateTopics { get; set; }
        /// <summary>
        /// Indicate in this Forum Moderate Posts Before Dipslay
        /// </summary>
        public virtual bool ModeratePosts { get; set; }
        /// <summary>
        /// gets or sets Count of posts that they are UnApproved
        /// </summary>
        public virtual long UnApprovedPostsCount { get; set; }
        /// <summary>
        /// gets or sets Count of topics that they are UnApproved
        /// </summary>
        public virtual long UnApprovedTopicsCount { get; set; }
        /// <summary>
        /// gets or sets Rowversion
        /// </summary>
        public virtual byte[] RowVersion { get; set; }
        /// <summary>
        /// gets or sets icon name with size 200*200 px for snippet 
        /// </summary>
        public virtual string SocialSnippetIconName { get; set; }
        /// <summary>
        /// gets or sets title for snippet
        /// </summary>
        public virtual string SocialSnippetTitle { get; set; }
        /// <summary>
        /// gets or sets description for snippet
        /// </summary>
        public virtual string SocialSnippetDescription { get; set; }
        /// <summary>
        /// gets or sets path for tree structure antipattern (1/3/4/23)
        /// </summary>
        public virtual string Path { get; set; }
        /// <summary>
        /// Indicate this forum inherit moderators from parent forum
        /// </summary>
        public virtual bool IsModeratorsInherited { get; set; }
        /// <summary>
        /// gets or set datetime that Last Post is Created In this forum. used for ForumTracking 
        /// </summary>
        public virtual DateTime? LastPostCreatedOn { get; set; }
        #endregion

        #region NavigationProperties
        /// <summary>
        /// sets or gets identifier forum's parent
        /// </summary>
        public virtual long? ParentId { get; set; }
        /// <summary>
        /// sets or gets forum's parent
        /// </summary>
        public virtual Forum Parent { get; set; }
        /// <summary>
        /// sets or gets sub forums of forum
        /// </summary>
        public virtual ICollection<Forum> Children { get; set; }
        /// <summary>
        /// set or get topics of forum
        /// </summary>
        public virtual ICollection<ForumTopic> Topics { get; set; }
        /// <summary>
        /// get or set moderators of this forum
        /// </summary>
        public virtual ICollection<ForumModerator> Moderators { get; set; }
        /// <summary>
        /// get or set Subscriptions List 
        /// </summary>
        public virtual ICollection<User> Subscribers { get; set; }
        /// <summary>
        /// get or set Announcements Collection of this Forum
        /// </summary>
        public virtual ICollection<ForumAnnouncement> Announcements { get; set; }
        /// <summary>
        /// get or set Trackers List Of this Forum
        /// </summary>
        public virtual ICollection<ForumTracker> Trackers  { get; set; }
        /// <summary>
        /// get or set Posts List that Posted in this forum for increase Performance for get Posts Count 
        /// </summary>
        public virtual ICollection<ForumPost> Posts  { get; set; }
        /// <summary>
        /// get or set 
        /// </summary>
        public virtual ICollection<ForumTopicTracker> TopicTrackers  { get; set; }
        #endregion

 /// <summary>
    /// Represents The Moderator For Forum
    /// </summary>
    public class ForumModerator
    {
        #region NavigationProperties
        /// <summary>
        /// gets or sets Forum
        /// </summary>
        public virtual Forum Forum { get; set; }
        /// <summary>
        /// gets or sets identifier of forum
        /// </summary>
        public virtual long ForumId { get; set; }
        /// <summary>
        /// gets or sets user that moderate forum
        /// </summary>
        public virtual User Moderator { get; set; }
        /// <summary>
        /// gets or sets id of user that moderate forum
        /// </summary>
        public virtual long ModeratorId { get; set; }
        /// <summary>
        /// gets or sets permission of user that moderate forum
        /// </summary>
        public virtual ForumModeratorPermissions Permissions { get; set; }
        /// <summary>
        /// indicate moderator's permissions in this forum apply with
        /// </summary>
        public virtual bool ApplyChildren { get; set; }
        #endregion
    }

    [Flags]
    public enum  ForumModeratorPermissions
    {
        CanEditPosts=1,
        CanDeletePosts=2,
        CanManageTopics=4,
        CanOpenCloseTopics=8,
       ...
    }

    /// <summary>
    /// Represents the Announcement that shown Top Of Forums
    /// </summary>
    public class ForumAnnouncement
    {
        #region Ctor
        /// <summary>
        /// create one instance of <see cref="ForumAnnouncement"/>
        /// </summary>
        public ForumAnnouncement()
        {
            Id = SequentialGuidGenerator.NewSequentialGuid();
           CreatedOn = DateTime.Now;
        }
        #endregion

        #region Properties
        /// <summary>
        /// gets or sets Identifier 
        /// </summary>
        public virtual Guid Id { get; set; }
        /// <summary>
        /// gets or sets DateTime That this Announcement Will be Shown
        /// </summary>
        public virtual DateTime StartOn { get; set; }
        /// <summary>
        /// gets or sets DateTime That this Announcement Will be Finished 
        /// </summary>
        public virtual DateTime? ExpireOn { get; set; }
        /// <summary>
        /// gets or sets Content of this Announcement
        /// </summary>
        public virtual string Message { get; set; }
        /// <summary>
        /// Indicate this Announcement Will be shown on Children Forums
        /// </summary>
        public virtual bool ApplyChildren { get; set; }
        /// <summary>
        /// gets or sets datetime that this record created
        /// </summary>
        public virtual DateTime CreatedOn { get; set; }
        #endregion

        #region NavigationProperties
        /// <summary>
        /// gets or sets Forum that associated With this Announcement
        /// </summary>
        public virtual Forum Forum { get; set; }
        /// <summary>
        /// gets or sets Identifier of Forum that associated With this Announcement
        /// </summary>
        public virtual long ForumId { get; set; }
        #endregion
    }

 /// <summary>
    /// Represents a base class for AuditLog
    /// </summary>
    public abstract class AuditBaseEntity
    {
        #region Properties
        /// <summary>
        /// sets or gets identifier
        /// </summary>
        public virtual long Id { get; set; }
        /// <summary>
        /// gets or sets datetime that is created
        /// </summary>
        public  virtual DateTime CreatedOn { get; set; }
        /// <summary>
        /// gets or sets datetime that is modified
        /// </summary>
        public virtual DateTime? LastModifiedOn { get; set; }
        /// <summary>
        /// gets or sets reason of Last Update for increase performance
        /// </summary>
        public virtual string LastModifyReason { get; set; }
        /// <summary>
        /// gets or sets displayName of Last Modifier  for increase performance
        /// </summary>
        public virtual string LastModifier{ get; set; }
        /// <summary>
        /// indicate this entity is Locked for Modify
        /// </summary>
        public virtual bool ModifyLocked { get; set; }
        /// <summary>
        /// gets or sets rowversion for synchronization problem
        /// </summary>
        public virtual byte[] RowVersion { get; set; }
        /// <summary>
        /// gets or sets count of this content's Updates
        /// </summary>
        public virtual int ModifyCount { get; set; }
        #endregion

        #region NavigationProperties
        /// <summary>
        /// gets or sets creator of this record
        /// </summary>
        public virtual User Creator { get; set; }
        /// <summary>
        /// gets or sets creator's Id of this record
        /// </summary>
        public virtual long CreatorId { get; set; }
        #endregion
    }

  /// <summary>
    /// Represents the Topic in the Forums
    /// </summary>
    public class ForumTopic 
    {
        #region Ctor
        /// <summary>
        /// create one instance of <see cref="ForumTopic"/>
        /// </summary>
        public ForumTopic()
        {
            CreatedOn = DateTime.Now;
        }
        #endregion

        #region Properties
        /// <summary>
        /// sets or gets identifier
        /// </summary>
        public virtual long Id { get; set; }
        /// <summary>
        /// gets or sets datetime that is created
        /// </summary>
        public virtual DateTime CreatedOn { get; set; }
        /// <summary>
        /// gets or sets Title Of this topic
        /// </summary>
        public virtual string Title { get; set; }
        /// <summary>
        /// gets or sets name of tags that assosiated with 
        /// this content fo increase performance
        /// </summary>
        public virtual string TagNames { get; set; }
        /// <summary>
        /// indicate this topic is Sticky and will be shown top of forum
        /// </summary>
        public virtual bool IsSticky { get; set; }
        /// <summary>
        /// indicate this topic is closed
        /// </summary>
        public virtual bool IsClosed { get; set; }
        /// <summary>
        /// gets or sets identifier of  last post in this topic
        /// </summary>
        public virtual long LastPostId { get; set; }
        /// <summary>
        /// gets or sets identifier of Last user that post in this topic
        /// </summary>
        public virtual long LastPosterId { get; set; }
        /// <summary>
        /// gets or sets title of last Post in this topic
        /// </summary>
        public virtual string LastPostTitle { get; set; }
        /// <summary>
        /// gets or sets displayName of user that create lastpost in this topic
        /// </summary>
        public virtual string LastPoster { get; set; }
        /// <summary>
        /// gets or sets datetime that last post posted in this topic
        /// </summary>
        public virtual DateTime? LastPostCreatedOn { get; set; }
        /// <summary>
        /// indicate this topic is approved
        /// </summary>
        public virtual bool IsApproved { get; set; }
        /// <summary>
        /// indicate this topic is type of Announcements and shown in Annoucements sections
        /// </summary>
        public virtual bool IsAnnouncement { get; set; }
        /// <summary>
        /// gets or sets viewed count 
        /// </summary>
        public virtual long ViewCount { get; set; }
        /// <summary>
        /// gets or sets count of posts that they are approved
        /// </summary>
        public virtual int ApprovedPostsCount { get; set; }
        /// <summary>
        /// gets or sets count of posts that they are Unapproved
        /// </summary>
        public virtual int UnApprovedPostsCount { get; set; }
        /// <summary>
        /// gets or sets specifications of this topic's rating
        /// </summary>
        public virtual Rating Rating { get; set; }
        /// <summary>
        /// gets or sets datetime that this topic closed
        /// </summary>
        public virtual DateTime? ClosedOn { get; set; }
        /// <summary>
        /// gets or sets reason that this topic colsed
        /// </summary>
        public virtual string ClosedReason { get; set; }
        /// <summary>
        /// gets or sets count of reports
        /// </summary>
        public virtual int ReportsCount { get; set; }
        /// <summary>
        /// indicate the posts of this topic should be Moderate Before Dipslay
        /// </summary>
        public virtual bool ModeratePosts { get; set; }
        /// <summary>
        /// gets or sets Level of this topic
        /// </summary>
        public virtual ForumTopicLevel Level { get; set; }
        /// <summary>
        /// gets or sets type of this topic
        /// </summary>
        public virtual ForumTopicType Type { get; set; }
        #endregion

        #region NavigationProperties
        /// <summary>
        /// gets or sets Collection of tags that associated with this topic
        /// </summary>
        public virtual ICollection<Tag> Tags { get; set; }
        /// <summary>
        /// gets or sets forum
        /// </summary>
        public virtual Forum Forum { get; set; }
        /// <summary>
        /// gets or sets identifier of Forum
        /// </summary>
        public virtual long ForumId { get; set; }
        /// <summary>
        /// gets or sets Posts Of this topic
        /// </summary>
        public virtual ICollection<ForumPost> Posts { get; set; }
        /// <summary>
        /// get or set Subscriptions List 
        /// </summary>
        public virtual ICollection<User> Subscribers { get; set; }
        /// <summary>
        /// get or set Trackkers list of this Topic
        /// </summary>
        public virtual ICollection<ForumTopicTracker> Trackers { get; set; }
        /// <summary>
        /// gets or sets creator of this record
        /// </summary>
        public virtual User Creator { get; set; }
        /// <summary>
        /// gets or sets creator's Id of this record
        /// </summary>
        public virtual long CreatorId { get; set; }
        #endregion
    }

 public enum ForumTopicType
    {
        Non,
        Tutorial,
        Conversation,
        Question,
        News,
        Article
    }

    public enum ForumTopicLevel
    {
        Professional,
        Intermediate,
        Beginner
    }

AutoMapper کتابخانه‌ای برای نگاشت اطلاعات یک شیء به شی‌ءایی دیگر به صورت خودکار می‌باشد.

در این مقاله چگونگی رسیدگی به Null property را در AutoMapper   بررسی خواهیم کرد. فرض کنید شیء منبع دارای یک خاصیت Null  است و می‌خواهید به وسیله Automaper شیء منبع را به مقصد نگاشت نمایید. اما می‌خواهید در صورت Null بودن شیء مبدا، یک مقدار پیش فرض برای شیء مقصد در نظر گرفته شود .

برای نمونه کلاسuser   را که در آن از کلاس Address یک خاصیت تعریف شده، در نظر بگیرید. اگر مقدار آدرس در شیء منبع خالی بود شاید شما بخواهید مقدار آن را به صورت empty string و یا با یک مقدار پیش فرض در مقصد مقدار دهی کنید.

همانند مثال زیر : 

public class UserSource
{
  public Address Address{get;set;}
}
 
public class UserDestination
{
  public string Address{get;set;}
}

AutoMapper.Mapper.CreateMap<UserSource, UserDestination>()
          .ForMember(dest => dest.Address
          , opt => opt.NullSubstitute("Address not found")
          );

var model = AutoMapper.Mapper.Map<UserSource, UserDestination>(user);
var models = AutoMapper.Mapper.Map<IEnumerable<UserSource>, IEnumerable<UserDestination>>(users);

بخش اول : Jaywalking  

در این بخش در حال توسعه ویژگی نرم افزاری هستیم که در آن هرکاربر به عنوان یک کاربر اصلی برای یک محصول تخصیص داده می‌شود. در طراحی اصلی ما فقط اجازه می‌دهیم یک کاربر متعلق به هر محصول باشد، اما امکان چنین تقاضایی وجود دارد که چند کاربر نیز به یک محصول اختصاص داده شوند.

در این صورت، اگر پایگاه داده را به نحوی تغییر دهیم که شناسه‌ی حساب کاربران را در لیستی که با کاما از یکدیگر جدا شده‌اند ذخیره نماییم، خیلی ساده‌تر به نظر می‌رسد نسبت به اینکه بصورت جداگانه آنها را ثبت نماییم.

برنامه نویسان معمولا برای جلوگیری از ایجاد جدول واسطی [1] که رابطه‌های چند به چند زیادی دارد از یک لیست که با کاما داده‌هایش از هم جدا شده‌اند، استفاده می‌کنند. بدین جهت اسم این بخش jaywalking ,antipatten می‌باشد، زیرا jaywalking نیز عملیاتی است که از تقاطع جلوگیری می‌کند.

1.1  هدف:  ذخیره کردن چندین صفت  

طراحی کردن جدولی که ستون آن فقط یک مقدار دارد، بسیار ساده و آسان می‌باشد. شما می‌توانید نوع داده‌ایی که متعلق به ستون می‌باشد را انتخاب نمایید. مثلا از نوع (int,date…)

چگونه می‌توانیم مجموعه‌ایی از مقادیری که به یکدیگر مرتبط هستند را در یک ستون ذخیره نماییم ؟

در مثال ردیابی خطای پایگاه داده‌، ما یک محصول را به یک کاربر، با استفاده از ستونی که نوع آن integer است، مرتبط می‌نماییم. هر کاربر ممکن است محصولاتی داشته باشد و هر محصول به یک contact اشاره کند. بنابراین یک ارتباط چند به یک بین محصولات و کاربر برقرار است. برعکس این موضوع نیز صادق است؛ یعنی امکان دارد هر محصول متعلق به چندین کاربر باشد و یک ارتباط یک به چند ایجاد شود. در زیر جدول محصولات را به صورت عادی آورده شده است: 

CREATE TABLE Products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(1000),
account_id BIGINT UNSIGNED,
-- . . .
FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);

INSERT INTO Products (product_id, product_name, account_id)
VALUES (DEFAULT, 'Visual TurboBuilder' , 12);

CREATE TABLE Products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(1000),
account_id VARCHAR(100), -- comma-separated list
-- . . .
);

INSERT INTO Products (product_id, product_name, account_id)
VALUES (DEFAULT, 'Visual TurboBuilder' , '12,34' );

SELECT * FROM Products WHERE account_id REGEXP '[[:<:]]12[[:>:]]' ;

SELECT * FROM Products AS p JOIN Accounts AS a
ON p.account_id REGEXP '[[:<:]]' || a.account_id || '[[:>:]]'
WHERE p.product_id = 123;

SELECT product_id, LENGTH(account_id) - LENGTH(REPLACE(account_id, ',' , '' )) + 1
AS contacts_per_product
FROM Products;

UPDATE Products
SET account_id = account_id || ',' || 56
WHERE product_id = 123;

<?php
$stmt = $pdo->query(
"SELECT account_id FROM Products WHERE product_id = 123");
$row = $stmt->fetch();
$contact_list = $row['account_id' ];


// change list in PHP code
$value_to_remove = "34";
$contact_list = split(",", $contact_list);
$key_to_remove = array_search($value_to_remove, $contact_list);
unset($contact_list[$key_to_remove]);
$contact_list = join(",", $contact_list);
$stmt = $pdo->prepare(
"UPDATE Products SET account_id = ?
WHERE product_id = 123");
$stmt->execute(array($contact_list));

INSERT INTO Products (product_id, product_name, account_id)
VALUES (DEFAULT, 'Visual TurboBuilder' , '12,34,banana' );

UPDATE Products SET account_id = '10,14,18,22,26,30,34,38,42,46'
WHERE product_id = 123;  

UPDATE Products SET account_id = '101418,222630,343842,467790'
WHERE product_id = 123;

CREATE TABLE Contacts ( product_id BIGINT UNSIGNED NOT NULL,
account_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (product_id, account_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id),
FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);

INSERT INTO Contacts (product_id, account_id) VALUES (456, 34);

DELETE FROM Contacts WHERE product_id = 456 AND account_id = 34;

SELECT account_id, COUNT(*) AS products_per_account
FROM Contacts
GROUP BY account_id;

مقدمه

1- Full Load vs Incremental Load

پروسه Full Load شامل مراحل زیر بود:

1. Drop Indexes: از آنجا که Index‌ها زمان بارگذاری را افزایش می‌دادند، این عمل صورت می‌پذیرفت.
2. Truncate Tables: تمامی رکوردهای موجود در جداول حذف می‌شدند.
3. Bulk Copy
4. Load Data
5. Post Process: شامل عملیاتی نظیر شاخص گذاری روی داده هایی است که اخیراً بارگذاری شده اند و....

روی  هم رفته Full Load مسئله ای مشکل ساز بود، زیرا نیاز به زمانی برای بارگذاری مجدد داده‌ها داشت و مسئله‌ی مهم‌تر نداشتن امکان دستیابی به گزارشاتی تاریخچه ای با ماهیت زمان برای مشتریان کسب وکار بود. به این دلیل که همواره یک کپی از آخرین داده‌های موجود در سیستم عملیاتی درون DW قرار می‌گرفت؛ که با بکارگیری Full Load اغلب قادر به ارائه‌ی این نوع از گزارشات نبودیم، بدین ترتیب سازمان‌ها به نسل دوم روی آورند که در این دیدگاه از مفهوم Incremental Load استفاده می‌شود. اشکال زیر مراحلی که در این روش انجام می‌شود را نمایان می‌سازد:

Incremental Load with an Extract In area

Incremental Load without an Extract In area

مراحل Incremental Load شامل:

1. بارگذاری تغییرات نسبت به آخرین فرآیند بارگذاری انجام شده
2. درج / بروزرسانی تغییرات درون Production area
3. درج / بروزرسانی Consumption area نسبت به Production area

تفاوت‌های اصلی میان Full Load و Incremental Load در این است که در Incremental Load:

• نیازی به پردازش‌های اضافی جهت حذف شاخص ها، پاک کردن تمامی رکورد‌های جداول و ساخت مجدد شاخص‌ها نیست.
• البته نیاز به رویه ای جهت شناسایی تغییرات می‌باشد.
• و همچنین نیاز به بروزرسانی  بعلاوه درج رکوردهای جدید نیز می‌باشد.

ترکیب این عوامل برای ساخت Incremental Load کارآمد تر، منجر به پیچیده‌تر شدن پیاده سازی و نگهداری آن نیز می‌شود.

2- Detecting Net Changes

فرآیند لود افزایشی ETL، بایست قادر به شناسائی رکورد‌های تغییریافته در مبداء باشد، که این عمل با استفاده از هر یک از تکنیک‌های Push یا Pull انجام می‌شود.

• در تکنیک Pull، فرآیند ETL رکوردهای تغییریافته در مبداء را انتخاب می‌کند:

• ایده‌آل وجود داشتن یک ستون Last Changed در سیستم مبداء است؛ که از آن می‌توان جهت انتخاب رکوردهای تغییر یافته استفاده نمود.
• چنانچه ستون Last Changed وجود نداشته باشد، تمامی رکوردهای مبداء باید با رکورد‌های مقصد مقایسه شود.

• در تکنیک Push، مبداء تغییرات را شناسائی می‌کند و آنها را به سمت مقصد Push می‌کند؛ این درخواست می‌تواند توسط فرآیند ETL انجام شود.

2-1- Pulling Net Changes – Last Change Column

2-2- Pulling Net Changes – No Last Change Column

1. Join میان مبداء و مقصد با استفاده از یک دستور Left Outer Join است.
2. تمامی رکورد‌های مبداء که در مقصد وجود ندارند، پردازش می‌شوند.
3. زمانی که رکوردی در مقصد وجود داشته باشد مقادیر داده‌های مبداء و مقصد مقایسه می‌شوند.
4. تمامی رکوردهای مبداء که تغییر یافته اند پردازش می‌شوند.

2-3- Pushing Net Changes

تفاوت این دو روش به شرح زیر است:

1. در سناریو اول (شکل سمت چپ)؛ بانک اطلاعاتی رابطه ای سیستم مبداء Transaction Log را مرتب مانیتور می‌کند تا تغییرات را شناسائی کرده و در ادامه تمامی این تغییرات را در جدولی در مقصد درج می‌کند.
2. در سناریو دوم؛ توسعه دهندگان Trigger هایی ایجاد می‌کنند تا هر زمان که رکوردی تغییر یافت، تغییرات در جدولی که در مقصد وجود دارد درج گردد.

مسئله ای که در هر دو مورد وجود دارد Load اضافه ای است؛ که روی سیستم مبداء وجود دارد و می‌تواند Performance سیستم‌های OLTP را تحت تاثیر قرار دهد. به هر روی سناریو نخست معمولاً کاراتر از سناریویی است که از Trigger استفاده می‌کند.

3- ETL Patterns

پس از شناسائی رکوردهایی که در مبداء تغییر یافته اند، نیاز داریم تا این تغییرات در مقصد اعمال شود. در این قسمت به معرفی الگوهایی که برای اعمال این تغییرات وجود دارد می‌پردازیم.

3-1- Destination load Patterns

تشخیص چگونگی اضافه نمودن تغییرات در مقصد تابع دو عامل زیر است:

• آیا رکورد هم اینک در مقصد وجود دارد؟
• الگوی استفاده شده برای جدول مقصد به کدام شکل است؟ (Update یا Versioned Insert)

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

3-2- Versioned Insert Pattern

Kimball Type II Slowly Changing Dimension نمونه ای از الگوی Versioned Insert است؛ که در آن نمونه ای از یک موجودیت دارای ورژن‌های متعددی است. مطابق تصویر زیر؛ این الگو به ستون‌های اضافه ای نیاز دارند که وضعیت نمونه ای از یک رکورد را نمایش دهد.

این ستون‌ها به شرح زیر هستند:

• Start Date: زمانی که وضعیت آن نمونه از رکورد فعال می‌شود.
• End Date: زمانی که وضعیت آن نمونه از رکورد غیر فعال می‌شود.
• Record Status: وضعیت‌های یک رکورد را نشان می‌دهد، که حداقل به شکل Active یا Inactive است.
• # Version: این ستون که اختیاری می‌باشد، ورژن آن نمونه از رکورد را ثبت می‌کند.

برای مثال شکل زیر؛ بیانگر وضعیت اولیه رکوردی در این الگو است:

فرض کنید که این رکورد در تاریخ March 2 , 2010 در سیستم مبداء تغییر می‌کند. فرآیند ETL این تغییر را شناسائی می‌کند و همانند تصویر زیر؛ به شکل نمونه ای ثانویه از این رکورد، اقدام به درج آن می‌کند.

توجه داشته باشید زمانی که رکورد دوم در جدول درج می‌شود، به منظور بازتاب این تغییر؛ رکورد اول به شکل زیر بروزرسانی می‌گردد:

• End Date: تا این زمان وضعیت این رکورد فعال بوده است.
• Record Status:که Active به Inactive تغییر پیدا می‌کند.

در برخی از پیاده سازی‌های DW عمدتاً از الگوی Versioned Insert استفاده می‌شود و هرگز از الگوی Update استفاده نمی‌شود. مزیت این استراتژی در این است که تمامی تاریخچه تغییرات ردیابی و ثبت می‌شود. به هر روی غالباً هزینه ثبت کردن این تغییرات منجر به ایجاد نسخه‌های زیادی از تغییرات می‌شود. تیم DW برای مواردی که تغییرات متاثر از گزارشات تاریخچه ای نیستند، می‌توانند الگوی Update را در نظر گیرند.

3-3- Update Pattern

الگوی Update روی رکورد موجود، تغییرات سیستم مبداء را بروزرسانی می‌کند. مزیت این روش در این است که همواره یک رکورد وجود دارد و در نتیجه باعث ایجاد Query‌های کارآمدتر می‌شود. تصویر زیر بیانگر ستون هایی است که برای پشتیبانی از الگوی Update بایست ایجاد کرد.

این ستون‌ها به شرح زیر هستند:

• Record Status: وضعیت‌های یک رکورد را نشان می‌دهد که حداقل به شکل Active یا Inactive است.
• # Version: این ستون که اختیاری می‌باشد، ورژن آن نمونه از رکورد را ثبت می‌کند.

موارد اصلی الگوی Update عبارتند از:

• تاریخ ثبت نمی‌شود. ابزاری ارزشمند برای نظارت بر داده ها، تغییرات تاریخی است و زمانی که ممیزی داده رخ می‌دهد؛ می‌تواند مفید واقع شود.
• بروزرسانی‌ها یک الگوی مبتنی بر مجموعه هستند. استفاده از بروزرسانی هر بار یک رکورد در ابزار ETL خیلی کارآمد (موجه) نیست.

یک روش دیگر برای در نظر گرفتن موارد فوق؛ اضافه کردن یک جدول برای درج ورژن‌ها به الگوی Update است که در شکل زیر نشان داده شده است.

اضافه نمودن یک جدول تاریخچه، که تمامی تغییرات سیستم مبداء را ثبت  می‌کند؛ نظارت و ممیزی داده‌ها را نیز فراهم می‌کند و همچنین بروزرسانی‌های کارآمد مبتنی بر مجموعه را برای جداول DW به ارمغان می‌آورد.

3-4- Versioned Insert: Net Changes 

این الگو غالباً در جداول حجیم Fact که بروزرسانی آنها پر هزینه است استفاده می‌شود. شکل زیر منطق استفاده شده در این الگو را نشان می‌دهد.

• مقادیر مالی و عددی محاسبه شده؛ به عنوان یک Net Change از نمونه قبلی رکورد در جدول Fact ذخیره می‌شود.
• هیچ گونه فعالیت Post Processing صورت نمی‌گیرد (از قبیل بروزرسانی جداول Fact پس از کامل شدن Data Flow). هدف استفاده از این الگو اجتناب از بروزرسانی روی جداول بسیار حجیم می‌باشد.
• عدم بروزرسانی و همچنین اندازه جدول Fact زمینه ای را فراهم می‌کند که منطق شناسائی رکوردهای تغییریافته پیچیده تر  می‌شود. این پیچیدگی از آنجا ناشی می‌شود که نیاز به مقایسه رکوردهای جدول Fact آتی با جدول Fact موجود می‌باشد.

4- Data Integration Best Practices

هم اکنون پس از آشنایی با مفاهیم و الگو‌های توزیع داده‌ها به ارائه تعدادی نمونه می‌پردازیم؛ که بتوان این ایده‌ها و الگوها را در عمل پوشش داد.

4-1- Basic Data Flow Patterns

هر یک از الگوهای Update Pattern و Versioned Insert Pattern می‌توانند برای انواعی از جداول بکار روند که معروفترین آن‌ها توسط Kimball ساخته شده اند.

• (Slowly Changing Dimension Type I (SCD I: از Update Pattern استفاده می‌کند.
• (Slowly Changing Dimension Type II (SCD II: از Versioned Insert Pattern استفاده می‌کند.
• Fact Table: نوع الگویی که استفاده می‌کند به نوع جدول Fact ای که Load خواهد شد بستگی دارد.

4-1-1- Update Pattern 

مطابق تصویر زیر جدولی که تنها حاوی ورژن فعلی رکورد هاست؛ از Update Dataflow Pattern استفاده می‌کند.

مواردی که در مورد این گردش کاری باید در نظر داشت به شرح زیر است:

• این Data Flow فقط سطرهایی را به یک مقصد اضافه خواهد کرد. SSIS دارای گزینه “Table or view fast load” می‌باشد که بارگذاری‌های انبوه و سریع را پشتیبانی می‌کند.
• درون یک Data Flow بروزرسانی  رکورد‌ها را می‌توان با استفاده از تبدیل OLE DB Command انجام داد. توجه داشته باشید خروجی‌های این تبدیل در یک دستور Update به ازای هر رکورد بکار می‌رود؛ مفهوم بروزرسانی انبوه در این Data Flow وجود ندارد. بدین ترتیب الگوی فعلی ارائه شده؛ تنها رکوردها را درج می‌کند و هرگز در این Data Flow رکوردها Update نمی‌شوند.
• هر جدول دارای یک جدول تاریخچه است که برای ذخیره همه فعالیت‌های مرتبط با آن بکار می‌رود. یک رکورد در جدول تاریخچه زمانی درج خواهد شد؛ که رکورد مبداء در مقصد وجود داشته باشد ولی دارای مقداری متفاوت باشد.

• راه دیگر فرستادن تغییرات رکوردها به یک جدول کاری است که پس از پایان یافتن فرآیند Update ، خالی (Truncate) می‌شود.
• مزیت نگهداری تمامی رکوردها در یک جدول تاریخچه؛ ایجاد یک دنباله ممیزی است که می‌تواند برای نظارت بر داده‌ها به منظور نمایان ساختن موارد مطرح شده توسط مصرف کننده‌های کسب و کار استفاده شود.

• گزینه‌های متفاوتی برای تشخیص تغییرات رکوردها وجود دارد که در ادامه به شرح آنها می‌پردازیم.

شکل زیر نمایش دهنده چگونگی پیاده سازی Update Dataflow Pattern در یک SSIS می‌باشد:

این SSIS شامل عناصر زیر است:

• Destination table lookup:

به منظور تشخیص اینکه رکورد در جدول مقصد وجود دارد از “lkpPersonContact” استفاده می‌کنیم.

• Change detection logic:

با استفاده از “DidRecordChange” مبداء و مقصد مقایسه می‌شوند. اگر تفاوتی بین مبداء و مقصد وجود نداشت؛ رکورد نادیده گرفته می‌شود. چنانچه بین مبداء و مقصد تفاوت وجود داشت؛ رکورد در جدول تاریخچه درج خواهد شد.

• Detection Inserts:

رکوردها در جدول مقصد درج خواهند شد در صورتیکه در آن وجود نداشته باشند.

• Destination History Inserts:

رکوردها در جدول تاریخچه مقصد درج خواهند شد، در صورتیکه (در مقصد) وجود داشته باشند.

پس از اتمام Data Flow یک روال Post-processing مسئولیت بروزرسانی رکوردهای جدول اصلی و رکوردهای ذخیره شده در جدول تاریخچه را بر عهده دارد که می‌تواند مطابق تصویر زیر با استفاده از یک Execute Process Task پیاده سازی شود.

PostProcess مسئولیت اجرای تمامی فعالیت‌های زیر را در این الگو برعهده دارد که شامل:

• بروزرسانی رکوردهای جداول با استفاده از رکوردهای درج شده در جدول تاریخچه.
• درج تمامی رکوردهای جدید (نسخه اولیه و در درون جدول تاریخچه). کلید اصلی جداولی که ستون  آنها IDENTITY است مقدار نامشخصی دارد؛ تا زمانی که درج صورت گیرد، این به معنای آن است که پیش از انتقال آنها به جدول تاریخچه نیاز است منتظر درج شدن آنها باشیم.

4-1-2- Update Pattern – ETL Framework

تصویر زیر بیانگر انجام این عملیات با استفاده از ابزارهای ETL است.
در نگاه نخستین ممکن است Data Flow از نوع اصلی خود پیچیده‌تر به نظر آید؛ که در واقع این گونه نیز هست، زیرا در فاز توسعه بیشتر Framework‌ها جهت پیاده سازی به یک زمان اضافه‌تری نیاز دارند. به هر روی این زمان جهت اجتناب از هزینه روزانه تطبیق داده‌ها گرفته خواهد شد.
مزایای حاصل شده از افزودن این منطق اضافی عبارت است از:

• پشتیبانی از ستون هایی که کارهای ممیزی و نظارت بر داده‌ها را آسانتر می‌کنند.
• تعداد سطرها شاخص مناسبی است که می‌تواند بهبود آن Data Flow خاص را فراهم کند. ناظر اطلاعات با استفاده از تعداد رکوردها می‌تواند ناهنجاری‌ها را شناسائی کند.

بهره برداران ETL و ناظران اطلاعات می‌توانند با استفاده از خلاصه تعداد رکوردها درک بیشتری درباره فعالیت‌های آن کسب کنند. پس از آنکه تعداد رکوردها، مشکوک به نظر آمد؛ تحقیقات بیشتری می‌تواند اتفاق افتد. (با عمیق‌تر شدن در جزئیات گزارشات)
 

4-1-3- Versioned Insert Pattern

جدولی که به صورت Versioned Insert پر شده است می‌تواند از Versioned Insert Dataflow Pattern استفاده کند. همانند شکل زیر که گردش کار در آن برای کارآئی بیشتر بازنگری شده است.

توجه داشته باشید Data Flow در این روش شامل:

• تمامی رکوردهای جدید و تغییر یافته در جدول Versioned Insert قرار می‌گیرند.
• این روش دارای Data Flow ساده‌تری نسبت به الگوی Update می‌باشد.

شکل زیر SSIS versioned insert data flow pattern را نشان می‌دهد:
 

تعدادی نکته در Data Flow فوق وجود دارد که عبارتند از:

• در شیء “lkpDimGeography” گزینه “Redirect rows to no match output” با مقدار “Ignore Failures” تنظیم شده است.
• شیء “DidRecordChange” بررسی می‌کند چنانچه ستون‌های مبداء و مقصد یکسان باشند، آیا کلید اصلی جدول مقصد Not Null است. اگر این عبارت True ارزیابی شود، رکورد نادیده گرفته می‌شود.
• منطق شناسائی تغییرات دربردارنده تغییرات ستون داده ای در مبداء نمی‌باشد.
• ستون و تعداد رکوردها مشابه با Data Flow قبلی (ETL Framework) می‌باشد.

4-1-4- Update vs. Versioned Insert

الگوی Versioned Insert نسبت الگوی Update دارای پیاده سازی ساده‌تر و فعالیت‌های I/O کمتری است. از منظر دیگر، جدولی که از الگوی Update استفاده می‌کند، دارای تعداد رکوردهای کمتری است که می‌تواند به معنای Performance بهتر نیز تعبیر شود. ممکن است سوالی مطرح شود، اینکه چرا برای انجام کار به جدول تاریخچه نیاز است؛ این جدول را که نمی‌توان Truncate نمود، پس چرا به منظور بروزرسانی از جدول اصلی استفاده می‌شود؟ پاسخ این پرسش در این است که جدول تاریخچه، ناظر اطلاعات و ممیزین داده را قادر می‌سازد، تغییرات در طول زمان را پیگیری نمایند.
 

4-2- Dimension Patterns

بروزرسانی Dimension موارد زیر را شامل می‌شود:

• پیگیری تاریخچه
• انجام بروزرسانی
• تشخیص رکوردهای جدید
• مدیریت surrogate keys

چنانچه با یک Dimension کوچک مواجه هستید (با مقدار هزاران رکورد یا کمتر، که با صدها هزار رکورد یا بیشتر ضدیت دارد)،  می‌توانید از تبدیل “Slowly Changing Dimension” که بصورت Built-in در SSIS موجود است، استفاده نمائید. به هر روی با آنکه این تبدیل چندین ویژگی محدودکننده Performance دارد، اغلب کارآمدتر از پروسسه هایی که توسط خودتان ایجاد می‌شود. در واقع فرآیند بارگذاری در جداول Dimension با مقایسه داده‌ها بین مبداء و مقصد انجام می‌شود. به طور معمول مقایسه روی یک ورژن جدید و یا مجموعه ای از سطرهای جدید یک جدول با مجموعه داده‌های موجود در جدول متناظرش صورت می‌گیرد. پس از تشخیص چگونگی تغییر در داده ها، یک سری عملیات درج و بروزرسانی انجام می‌شود. شکل زیر نمونه ای از پردازش سریع در Dimension را نمایش می‌دهد؛ که شامل مراحل اساسی زیر است:

• منبع فوقانی سمت چپ، رکوردها را در یک SSIS از یک سیستم مبداء (یا یک سیستم میانی) به شکل Pull دریافت می‌کند. منبع فوقانی سمت راست، داده‌ها را از خود جدول Dimension به شکل Pull دریافت می‌کند.
• با استفاده از Merge Join رکوردها از طریق Source Key شان مقایسه می‌شوند. (در شکل بعدی جزئیات این مقایسه نمایش داده شده است.)
• با استفاده از یک Conditional Spilt داده‌ها ارزیابی می‌شوند؛ سطرها یا مستقیماً در جدول Dimension درج می‌شوند (منبع تحتانی سمت چپ) و یا در یک جدول عملیاتی (منبع تحتانی سمت راست) جهت انجام بروزرسانی درج می‌شوند.
• در گام پایانی (که نمایش داده نشده) مجموعه ای از بروزرسانی بین جدول عملیاتی و جدول Dimension صورت می‌گیرد.

با Merge Join ارتباطی بین رکوردهای مبداء و رکوردهای مقصد برقرار می‌شود. (در این مثال “CustomerAlternateKey”). هنگامی که از این دیدگاه استفاده می‌کنید، خاطر جمع شوید که نوع Join با مقدار “Left outer join” تنظیم شده است؛ بدین ترتیب قادر هستید تا رکوردهای جدید را از مبداء تشخیص دهید؛ از آنجا که هنوز در جدول Dimension قرار نگرفته اند.

گام پایانی به منظور تشخیص اینکه آیا رکورد، جدید یا تغییر یافته است (یا بلاتکلیف است)، مقایسه داده هاست. شکل زیر نمایش می‌دهد چگونه این ارزیابی با استفاده از تبدیل “Conditional Spilt” صورت می‌گیرد.

Conditional Spilt مستقیماً با استفاده از یک Adapter تعریف شده روی مقصد یا یک جدول کاری بروزرسانی که از یک Adapter تعریف شده روی مقصد استفاده می‌کند؛ توسط مجموعه دستور Update زیر، رکوردها را در جدول Dimension قرار می‌دهد. دستور Update زیر مستقیماً با استفاده از روش Join روی جدول Dimension و جدول کاری، مجموعه ای را بصورت انبوه بروزرسانی می‌کند.

UPDATE AdventureWorksDW2008R2.dbo.DimCustomer
    SET AddressLine1 = stgDimCustomerUpdates.AddressLine1
    , AddressLine2 = stgDimCustomerUpdates.AddressLine2
    , BirthDate = stgDimCustomerUpdates.BirthDate
    , CommuteDistance = stgDimCustomerUpdates.CommuteDistance
    , DateFirstPurchase = stgDimCustomerUpdates.DateFirstPurchase
    , EmailAddress = stgDimCustomerUpdates.EmailAddress
    , EnglishEducation = stgDimCustomerUpdates.EnglishEducation
    , EnglishOccupation = stgDimCustomerUpdates.EnglishOccupation
    , FirstName = stgDimCustomerUpdates.FirstName
    , Gender = stgDimCustomerUpdates.Gender
    , GeographyKey = stgDimCustomerUpdates.GeographyKey
    , HouseOwnerFlag = stgDimCustomerUpdates.HouseOwnerFlag
    , LastName = stgDimCustomerUpdates.LastName
    , MaritalStatus = stgDimCustomerUpdates.MaritalStatus
    , MiddleName = stgDimCustomerUpdates.MiddleName
    , NumberCarsOwned = stgDimCustomerUpdates.NumberCarsOwned
    , NumberChildrenAtHome = stgDimCustomerUpdates.NumberChildrenAtHome
    , Phone = stgDimCustomerUpdates.Phone
    , Suffix = stgDimCustomerUpdates.Suffix
    , Title = stgDimCustomerUpdates.Title
    , TotalChildren = stgDimCustomerUpdates.TotalChildren
FROM AdventureWorksDW2008.dbo.DimCustomer DimCustomer
  INNER JOIN dbo.stgDimCustomerUpdates ON
DimCustomer.CustomerAlternateKey = stgDimCustomerUpdates.CustomerAlternateKey

4-3- Fact Table Patterns

جداول Fact به پردازش‌های منحصر به فردی نیازمند هستند، نخست به کلیدهای Surrogate جدول Dimension نیاز دارند تا Measure‌های محاسبه شدنی را بدست آورند. این اعمال از طریق تبدیلات Lookup، Merge Join و Derived Column صورت می‌گیرد. با بروزرسانی ها، تفاضل رکورد‌ها و یا Snapshot بیشتر این فرآیندهای دشوار انجام می‌شوند.

4-3-1- Inserts

روی اغلب جداول Fact عمل درج صورت می‌گیرد؛ که کار متداولی در جدول Fact می‌باشد. شاید ساده‌ترین کار که در فرآیند ساخت ETL صورت می‌گیرد، عملیات درج روی تنها تعدادی از جدول Fact می‌باشد. درج کردن در صورت لزوم بارگذاری انبوه داده ها، مدیریت شاخص‌ها و مدیریت پارتیشن‌ها را شامل می‌شود.

4-3-2- Updates

بروزرسانی روی جداول Fact معمولاً به یکی از سه طریق زیر انجام می‌گیرد:

• از طریق یک تغییر یا بروزرسانی رکورد
• از طریق یک دستور Insert خنثی کننده (Via an Insert of a compensating transaction)
• با استفاده از یک SQL MERGE

در موردی که تغییرات با فرکانس کمی روی جدول Fact صورت می‌گیرد و یا فرآیند بروزرسانی قابل مدیریت است؛ ساده‌ترین روش انجام یک دستور Update روی جدول Fact می‌باشد. نکته  مهمی که هنگام انجام بروزرسانی باید به خاطر داشته باشید، استفاده از روش بروزرسانی مبتنی بر مجموعه است؛ به همان طریق که در قسمت الگوهای Dimension ذکر آن رفت.
در طریقی دیگر (درج compensating) می‌توان اقدام به درج رکورد تغییر یافته نمود، تا ترجیحاً بروزرسانی روی آن صورت گیرد. این استراتژی به سادگی داده‌های جدول Fact میان سیستم مبداء و مقصد را که تغییر یافته اند، به صورت یک رکورد جدید درج خواهد کرد. تصویر زیر مثالی از اجرای موارد فوق را نمایش می‌دهد.
 

در آخرین روش از یک دستور SQL MERGE استفاده می‌شود که در آن با استفاده از ادغام و مقایسه، تمامی داده‌های جدید و تغییر یافته جدول Fact، درج و یا بروزرسانی می‌شوند. نمونه ای از استفاده دستور Merge به شرح زیر است:

MERGE dbo.FactSalesQuota AS T
USING SSIS_PDS.dbo.stgFactSalesQuota AS S
ON T.EmployeeKey = S.EmployeeKey
AND T.DateKey = S.DateKey
WHEN MATCHED AND BY target
THEN INSERT(EmployeeKey, DateKey, CalendarYear, CalendarQuarter, SalesAmountQuota)
VALUES(S.EmployeeKey, S.DateKey, S.CalendarYear, S.CalendarQuarter, S.SalesAmountQuota)
WHEN MATCHED AND T.SalesAmountQuota != S.SalesAmountQuota
THEN UPDATE SET T.SalesAmountQuota = S.SalesAmountQuota
;

4-3-3- Managing Inferred Members

زمانیکه یک ارجاع در جدول Fact به یک عضو Dimension که هنوز بارگذاری نشده‌است بوجود  آید؛ یک Inferred Member تعبیر می‌شود. به سه طریق می‌توان این Inferred Member‌ها را مدیریت نمود:

• رکوردهای جدول Fact پیش از درج اسکن شوند؛ ایجاد هر Inferred Member در Dimension و سپس بارگذاری رکوردها در جدول Fact
• در طول عملیات بارگذاری روی Fact؛ هر رکورد مفقوده شده به یک جدول موقتی ارسال شود، رکوردهای مفقوده شده به Dimension اضافه شود، در ادامه مجدداً آن رکوردهای Fact در جدول Fact بارگذاری شوند.
• در یک Data Flow زمانی که یک رکورد مفقود شده، بلاتکلیف تعبیر می‌شود؛ آن زمان یک رکورد به Dimension اضافه شود و Surrogate Key بدست آمده را برگردانیم؛ سپس Dimension بارگذاری شود.

شکل زیر این موارد را نمایش می‌دهد:

موجودیت‌های زیر را در نظر بگیرید: 

public class Customer
{
    public Customer()
    {
        Orders = new ObservableCollection<Order>();
    }
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Family { get; set; }

    public string FullName
    {
        get
        {
            return Name + " " + Family;
        }
    }

    public virtual IList<Order> Orders { get; set; }
}

public class Product
{
    public Product()
    {
    }

    public Guid Id { get; set; }
    public string Name { get; set; }
    public int Price { get; set; }
}
public class OrderDetail
{
    public Guid Id { get; set; }
    public Guid ProductId { get; set; }
    public int Count { get; set; }
    public Guid OrderId { get; set; }
    public int Price { get; set; }

    public virtual Order Order { get; set; }
    public virtual Product Product { get; set; }

    public string ProductName
    {
        get
        {
            return Product != null ? Product.Name : string.Empty;
        }
    }
}

public class Order
{
    public Order()
    {
        OrderDetail = new ObservableCollection<OrderDetail>();
    }
    public Guid Id { get; set; }
    public DateTime Date { get; set; }

    public Guid CustomerId { get; set; }
    public virtual Customer Customer { get; set; }
    public virtual IList<OrderDetail> OrderDetail { get; set; }

    public string CustomerFullName
    {
        get
        {
            return Customer == null ? string.Empty : Customer.FullName;
        }
    }

    public int TotalPrice
    {
        get
        {
            if (OrderDetail == null)
                return 0;

            return
                OrderDetail.Where(orderdetail => orderdetail.Product != null)
                .Sum(orderdetail => orderdetail.Price*orderdetail.Count);
        }
    }
}

و نگاشت موجودیت ها: 

public class CustomerConfiguration : EntityTypeConfiguration<Customer>
{
    public CustomerConfiguration()
    {
        HasKey(c => c.Id);
        Property(c => c.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    }
}
public class ProductConfiguration : EntityTypeConfiguration<Product>
{
    public ProductConfiguration()
    {
        HasKey(p => p.Id);
        Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    }
}
public class OrderDetailConfiguration : EntityTypeConfiguration<OrderDetail>
{
    public OrderDetailConfiguration()
    {
        HasKey(od => od.Id);
        Property(od => od.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    }
}
public class OrderConfiguration: EntityTypeConfiguration<Order>
{
    public OrderConfiguration()
    {
        HasKey(o => o.Id);
        Property(o => o.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    }
}

و برای معرفی موجودیت‌ها به Entity Framwork کلاس StoreDbContext را به صورت زیر تعریف می‌کنیم:

public class StoreDbContext : DbContext
{
    public StoreDbContext()
        : base("name=StoreDb")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new CustomerConfiguration());
        modelBuilder.Configurations.Add(new OrderConfiguration());
        modelBuilder.Configurations.Add(new OrderDetailConfiguration());
        modelBuilder.Configurations.Add(new ProductConfiguration());
    }

    public DbSet<Customer> Customers { get; set; }
    public DbSet<Product> Products { get; set; }
    public DbSet<Order> Orders { get; set; }
    public DbSet<OrderDetail> OrderDetails { get; set; }
}

جهت مقدار دهی اولیه به database تستی یک DataBaseInitializer به صورت زیر تعریف می‌کنیم:

public class MyTestDb : DropCreateDatabaseAlways<StoreDbContext>
{
    protected override void Seed(StoreDbContext context)
    {
        var customer1 = new Customer { Name = "Vahid", Family = "Nasiri" };
        var customer2 = new Customer { Name = "Mohsen", Family = "Jamshidi" };
        var customer3 = new Customer { Name = "Mohsen", Family = "Akbari" };

        var product1 = new Product {Name = "CPU", Price = 350000};
        var product2 = new Product {Name = "Monitor", Price = 500000};
        var product3 = new Product {Name = "Keyboard", Price = 30000};
        var product4 = new Product {Name = "Mouse", Price = 20000};
        var product5 = new Product {Name = "Power", Price = 70000};
        var product6 = new Product {Name = "Hard", Price = 250000};

        var order1 = new Order
        {
            Customer = customer1, Date = new DateTime(2013, 1, 1),
            OrderDetail = new List<OrderDetail>
            {
                new OrderDetail {Product = product1, Count = 1, Price = product1.Price},
                new OrderDetail {Product = product2, Count = 1, Price = product2.Price},
                new OrderDetail {Product = product3, Count = 1, Price = product3.Price},
            }
        };

        var order2 = new Order
        {
            Customer = customer1,
            Date = new DateTime(2013, 1, 5),
            OrderDetail = new List<OrderDetail>
            {
                new OrderDetail {Product = product1, Count = 2, Price = product1.Price},
                new OrderDetail {Product = product3, Count = 4, Price = product3.Price},
            }
        };

        var order3 = new Order
        {
            Customer = customer1,
            Date = new DateTime(2013, 1, 9),
            OrderDetail = new List<OrderDetail>
            {
                new OrderDetail {Product = product1, Count = 4, Price = product1.Price},
                new OrderDetail {Product = product3, Count = 5, Price = product3.Price},
                new OrderDetail {Product = product5, Count = 6, Price = product5.Price},
            }
        };

        var order4 = new Order
        {
            Customer = customer2,
            Date = new DateTime(2013, 1, 9),
            OrderDetail = new List<OrderDetail>
            {
                new OrderDetail {Product = product4, Count = 1, Price = product4.Price},
                new OrderDetail {Product = product3, Count = 1, Price = product3.Price},
                new OrderDetail {Product = product6, Count = 1, Price = product6.Price},
            }
        };

        var order5 = new Order
        {
            Customer = customer2,
            Date = new DateTime(2013, 1, 12),
            OrderDetail = new List<OrderDetail>
            {
                new OrderDetail {Product = product4, Count = 1, Price = product4.Price},
                new OrderDetail {Product = product5, Count = 2, Price = product5.Price},
                new OrderDetail {Product = product6, Count = 5, Price = product6.Price},
            }
        };

        context.Customers.Add(customer3);

        context.Orders.Add(order1);
        context.Orders.Add(order2);
        context.Orders.Add(order3);
        context.Orders.Add(order4);
        context.Orders.Add(order5);

        context.SaveChanges();
    }

و در ابتدای برنامه کد زیر را جهت مقداردهی اولیه به Database مان قرار می‌دهیم:

Database.SetInitializer(new MyTestDb());

در انتها ConnectionString را در App.Config به صورت زیر تعریف می‌کنیم:

<connectionStrings>
    <add name="StoreDb" connectionString="Data Source=.\SQLEXPRESS;
Initial Catalog=StoreDBTest;Integrated Security = true" providerName="System.Data.SqlClient"/>
</connectionStrings>

بسیار خوب، حالا همه چیز محیاست برای اجرای اولین پرس و جو:

using (var context = new StoreDbContext())
{
    var query = context.Customers;

    foreach (var customer in query)
    {
        Console.WriteLine("Customer Name: {0}, Customer Family: {1}", 
                                  customer.Name, customer.Family);
    }
}

پرس و جوی تعریف شده لیست تمام Customer‌ها را باز می‌گرداند. query فقط یک "عبارت" پرس و جو هست و زمانی اجرا می‌شود که از آن درخواست نتیجه شود. در مثال بالا این درخواست در اجرای حلقه foreach اتفاق می‌افتد و درست در این لحظه است که دستور SQL ساخته شده و به Database فرستاده می‌شود. EF در این حالت تمام داده‌ها را در یک لحظه باز نمی‌گرداند بلکه این ارتباط فعال است تا حلقه به پایان برسد و تمام داده‌ها از database واکشی شود. خروجی به صورت زیر خواهد بود:

Customer Name: Vahid, Customer Family: Nasiri
Customer Name: Mohsen, Customer Family: Jamshidi
Customer Name: Mohsen, Customer Family: Akbari

var customers = context.Customers.ToList();

خط بالا دیگر یک عبارت پرس و جو نخواهد بود بلکه لیست تمام Customer هاست که به یکباره از database بازگشت داده شده است. در ادامه هرجا که از customers استفاده کنیم دیگر پرس و جویی به database فرستاده نخواهد شد.

پرس و جوی زیر مشتریهایی که نام آنها Mohsen هست را باز می‌گرداند:

private static void Query3()
{
    using (var context = new StoreDbContext())
    {
        var methodSyntaxquery = context.Customers
                   .Where(c => c.Name == "Mohsen");
        var sqlSyntaxquery = from c in context.Customers
                             where c.Name == "Mohsen"
                             select c;

        foreach (var customer in methodSyntaxquery)
        {
            Console.WriteLine("Customer Name: {0}, Customer Family: {1}", 
                                      customer.Name, customer.Family);
        }
    }

    // Output:
    // Customer Name: Mohsen, Customer Family: Jamshidi
    // Customer Name: Mohsen, Customer Family: Akbari
}

همانطور که مشاهده می‌کنید پرس و جو به دو روش Method Syntax و Sql Syntax نوشته شده است.

روش Method Syntax روشی است که از متدهای الحاقی (Extention Method)  ‌و عبارت‌های لامبدا (Lambda Expersion) برای نوشتن پرس و جو استفاده می‌شود. اما #C روش Sql Syntax را که همانند دستورات SQL هست، نیز فراهم کرده است تا کسانیکه آشنایی با این روش دارند، از این روش استفاده کنند. در نهایت این روش به Method Syntax تبدیل خواهد شد بنابراین پیشنهاد می‌شود که از همین روش استفاده شود تا با دست و پنجه نرم کردن با این روش، از مزایای آن در بخشهای دیگر کدنویسی استفاده شود.

اگر به نوع Customers که در DbContext تعریف شده است، دقت کرده باشید، خواهید دید که DbSet می‌باشد. DbSet کلاس و اینترفیس‌های متفاوتی را پیاده سازی کرده است که در ادامه با آنها آشنا خواهیم شد:

• IQueryable<TEntity>, IEnumerable<TEntity>, IQueryable, IEnumerable: که امکان استفاده از متدهای نام آشنای LINQ را برای ما فراهم می‌کند. البته فراموش نشود که EF از Provider ای با نام LINQ To Entity برای تفسیر پرس و جوی ما و ساخت دستور SQL متناظر آن استفاده می‌کند. بنابراین تمامی متدهایی که در LINQ To Object استفاده می‌شوند در اینجا قابل استفاده نیستند. بطور مثال اگر در پرس و جو از LastOrDefault روی Customer استفاده شود در زمان اجرا با خطای زیر مواجه خواهیم شد و در نتیجه در استفاده از این متدها به این مسئله باید دقت شود. 

• <IDbSet<TEntity: که دارای متدهای Add, Attach, Create, Find, Remove, Local می‌باشد و برای بحث ما Find و Local جهت ساخت پرس و جو استفاده می‌شوند که  در ادامه توضیح داده خواهند شد.
  
• <DbQuery<TEntity: که دارای متدهای AsNoTracking و Include می‌باشد و در ادامه توضیح داده خواهند شد.
  

private static void Query4()
{
    using (var context = new StoreDbContext())
    {
        var customer = context.Customers.Find(new Guid("2ee2fd32-e0e9-4955-bace-1995839d4367"));

        if (customer == null)
            Console.WriteLine("Customer not found");
        else
            Console.WriteLine("Customer Name: {0}, Customer Family: {1}", customer.Name, customer.Family);
    }
}

private static void Query5()
{
    using (var context = new StoreDbContext())
    {
        try
        {
            var customer1 = context.Customers.Single(c => c.Name == "Unkown");  // Exception: Sequence contains no elements
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }

        try
        {
            var customer2 = context.Customers.Single(c => c.Name == "Mohsen");  // Exception: Sequence contains more than one element
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }

        var customer3 = context.Customers.SingleOrDefault(c => c.Name == "Unkown");  // customer3 == null

        var customer4 = context.Customers.Single(c => c.Name == "Vahid");  // customer4 != null
    }
}

private static void Query6()
{
    using (var context = new StoreDbContext())
    {
        try
        {
            var customer1 = context.Customers.First(c => c.Name == "Unkown");  // Exception: Sequence contains no elements
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }

        var customer2 = context.Customers.FirstOrDefault(c => c.Name == "Unknown");  // customer2 == null
        var customer3 = context.Customers.First(c => c.Name == "Mohsen");
    }
}