توی قسمت اول و دوم مقاله در مورد کشف داده ها و دگرگونی داده ها توضیح دادم که اگر اون مقاله ها رو ندیدین پیشنهاد می کنم اول اون مقاله ها رو بخونید و بعد ادامه ی این مقاله رو ببینید. توی این قسمت در مورد بارگذاری داده ها (Loading of data) که سومین مرحله از چهار مرحله ای هست که برای تحلیل داده در نظر دارم، توضیح می دم.
توی قسمت اول مقاله ها (کشف داده ها)، نمودار موجودیت-رابطه (ERD) رو به صورت زیر کشیدیم:
نمودارهای ER معمولا به طرح واره های رابطه ای (Relational Schemas) تبدیل می شن تا داده ها رو بهتر نشون بدن. این به این دلیله که نمودارهای ER یک نمودار تصویری از مدل داده هست و برای پیاده سازی در پایگاه داده مناسب نیست. از طرف دیگه، طرحواره های رابطه ای به طور خاص برای پیاده سازی در پایگاه داده طراحی شدن و برای کوئری (سرچ) در داده ها کارآمدتر هستن. تبدیل یک نمودار ER به یک طرح رابطه ای شامل شناسایی موجودیت ها (Entity)، ویژگی ها (Attribute) و روابط (Relationship) موجود در نمودار و نگاشت اونها به جداول، ستون ها و کلیدهای خارجی (Foreign key) در طرح هست.
قبل از اینکه نمودارمون رو به طرح واره ی رابطه ای تبدیل کنیم بیاید با ساختارهایی که توی دیتابیس وجود داره و تعاریف اون ها آشنا بشیم:
- مدل رابطه ای، داده ها رو به صورت "مجموعه از جداول (Collection of tables)" نشون می ده
- به جدول، "رابطه (Relation)" و یا موجودیت (Entity) هم گفته می شه
- توی این جدول، "Interviews"، رابطه یا موجودیت یا Entity تعریف میشه
- به هر ردیف در جدول، Tuple میگیم. (برای مثال توی این جدول ما 3 تاپِل داریم)
- به هر ستون در جدول، Attribute یا ویژگی میگیم. (برای مثال توی این جدول ما 5 اَتریبیوت داریم)
- به مقادیر مجازی که هر ردیف میتونه داشته باشه، Domain میگیم. (برای مثال، توی این جدل، برای ستون ID فقط مقادیر عددی مجاز هست. برای ستون تاریخ، فقط فرمت درست تاریخ مجاز هست و برای ستون استتوس فقط 2 مقدار ثابت مجاز هست). دامین های مرسومی که وجود داره:
برای اینکه بیشتر از دامین ها و مثال هاشون بدونید می تونید به این لینک مراجعه کنید.
کاردینالیتی (Cardinality) به صورت خیلی ساده، مشخص می کنه که در ارتباط هایی که جداول مختلف با هم دارن، عناصرشون(tuples) به چه تعداد حداقل و حداکثر میتونن با هم در ارتباط باشن. برای مثال توی فرایند استخدام، هر آفر شغلی که داده می شه مربوط به 1 و فقط 1 کاندید هست. یک آفر مربوط به چند کاندید نیست و همینطور آفری وجود نداره که مربوط به کاندیدی نباشه. پس اینجا ارتباط 1 به 1 یا one to one وجود داره. یا برای یک پوزیشن میتونیم هیچ مصاحبه ای برای کاندیدی نداشته باشیم و یا برای همون پوزیشن به خصوص مصاحبه برای چندین کاندید داشته باشیم. پس اینجا ارتباطیم 0 به چند یا zero to many خواهد بود.
توی نمودارهای رابطه ای کاردینالیتی رو با خط هایی بین موجودیت ها (جدول ها) به صورت زیر نشون می دیم:
تعیین مدل کاردینالیتی برای تعریف جداول توی دیتابیس هایی مثل mySQL کاربرد داره و برای تحلیل هایی که در ادامه می خوایم انجام بدیم در حال حاضر کاربرد نداره ولی دونستنش خیلی خوبه که اگر خواستین این دیتابیس رو روی دیتابیس هایی مثل mySQL ببرید بتونید این موارد رو در نظر بگیرید.
حالا که با تعاریف و اصطلاحات اولیه ی دیتابیس آشنا شدیم بیاید با چارچوب طرح واره ی رابطه ای (Relational schema) آشنا بشیم:
توی Relational schema اسم جدول (موجودیت/Entity) بالا نوشته میشه و هر ردیف یکی از ویژگی های (َAttribute) اون جدول هستن که توی جدول اصلی مون به جای ستون ها قرار می گیرن.
کلید کنار متغیر اول در این جدول به معنی Primary Key هست که توی بعضی فرم ها به صورت PK کنار اون متغیر نوشته می شه
تایپ داده یا Domain جلوی هر ویژگی به صورت کمرنگ تر نوشته شده.
بعد از کشیدن جداولمون با استفاده از ERD و با استفاده از چارچوب بالا، ارتباط هر ویژگی جدول های مختلف با هم رو با خط مشخص می کنیم که در نهایت به نمودار زیر می رسیم:
ابزارهای مختلفی برای طراحی Relational schema وجود داره که لینک چندتاشون رو در ادامه میذارم:
بعضی از این ابزارها کد SQL رو برای ایجاد جداول در دیتابیس بهتون میدن که اگر بخواید از دیتابیس های SQL استفاده کنید خیلی کارتون رو راحت می کنه:
تعیین ارتباطات:
کدهای بالا برای ایجاد دیتابیس در دیتابیس MySQL هست و ما ازش برای تحلیلی که در ادامه خواهیم داشت استفاده نمی کنیم.
حالا که چارچوب جداولمون رو فهمیدیم چطوریه، میخوایم جدول ها رو روی دیتابیس ایجاد کنیم. ولی چه دیتابیسی برای کار ما مناسب هست؟
اگر از یک نفر توی دپارتمان دیتا بپرسید که دیتابیس برای کار با دیتا چی انتخاب کنیم احتمالا MySQL رو میگه. خوب MySQL و سایر دیتابیس ها امکانات بسیار زیادی رو در اختیار ما قرار میدن ولی آیا این امکانات برای تیم جذب و استخدام نیاز هست؟ راه اندازی دیتابیس MySQL یا سایر دیتابیس ها نیازمند دانش Data Engineering هست. علاوه بر اون اگر بخوایم این دیتابیس رو به اشتراک بذاریم و به جز سیستم خودمون، از سیستم های دیگه ای هم بهش دسترسی داشته باشیم، به سرور برای راه اندازیش نیاز داریم. همینطور برای استفاده ازش و Maintain کردنش باید دانش SQL داشت وارد کردن دیتا و استخراج دیتا ازش نیازمند دانش مربوط به خودش و همین طور یک تیم جداگانه هست.
تمامی این ها باعث میشه که با وجود خوب بودن این دیتابیس، استفاده ازش توسط تیم جذب و استخدام که قرار نیست به صورت خیلی تخصصی کار دیتا انجام بده، کار دشواری بشه. پس باید از یک راه حل ساده تر برای مدیریت داده ها استفاده کرد.
اگرچه گوگل شیت به عنوان دیتابیس طراحی نشده ولی به دلایل زیر میشه ازش این استفاده رو کرد:
برای مدیریت گوگل شیت به یک تیم آی تی جدا برای مدیریت کردن داده ها نیاز نیست. راه اندازیش بسیار ساده هست و برای داده ها با اندازه کوچیک خیلی خوب جواب میده. هر تغییری توش رو خودتون میتونید ایجاد کنید در صورتی که در دیتابیس mySQL، تغییراتی که نیاز دارید نیازمند ارتباط با تیم دیتا هست که خیلی وقت ها انجام پروسه ها رو کند میکنه و تغییرات با تاخیر انجام میشه و یا اصلا تیم دیتا این ریسورس رو نداره که بتونه وقت برای تیم منابع انسانی بذاره.
یکی از مزایای گوگل شیت اینه که چون متعلق به گوگل هست با سایر نرم افزارهای اکوسیستم گوگل یکپارچه هست و به راحتی میشه داده ها رو از این پلتفرم به پلتفرم دیگه برد
یکی از مزایای اصلی گوگل شیت، امکان کار تیمی روی یک شیت هست. با استفاده از این قابلیت تیم ها میتونن به صورت همزمان روی یک شیت کار کنن بدون اینکه نیاز به جا به جایی داده به صورت سنتی داشته باشن و به راحتی میشه این فایل رو با سایر اعضای تیم به اشتراک گذاشت
بنابراین با وجود اینکه گوگل شیت به عنوان دیتابیس ساخته نشده ولی ما برای اندازه ی گزارش های خودمون میتونیم از گوگل شیت به عنوان دیتابیس اصلی اطلاعات استفاده کنیم.
معایب:
در مجموع اگر تیم دیتایی دارید که میتونه توی maintain کردن دیتابیس همراهیتون کنه و این ریسورس رو دارن که بتونن برای تیم جذب و استخدام وقت بذارن خیلی عالیه که از MySQL استفاده کنید ولی اگر میخواید فقط خودتون بدون وابستگی به تیم دیگری کارهای تحلیلیتون رو انجام بدید، بهترین گزینه Google Sheets هست.
برای این کار من یک فایل اصلی Recruitment ایجاد کردم و هر جدولم رو توی یک sheet جدا به صورت زیر گذاشتم:
طبق جداولی که کشیدیم ستون های شیت ها رو ایجاد کردم
توجه کنید که باید به هر شیت دقیقا مثل یک جدول نگاه کنیم و هیچ چیز اضافه ای توی اون شیت وارد نکنیم. حتما ردیف اول هر شیت باید اسم ستون ها باشه و از ردیف دیگری نباید شروع کنید
در دیتابیس، اسم ستون ها نمیتونه یکسان باشه پس اسم های یکتا برای هر ستون انتخاب کنید
کلید اصلی ردیف ها نمیتونه مقداری نداشته باشه، همین طور نمیتونه مقدار تکراری باشه و باید برای هر ردیف نوشته بشه
در طراحی سیستم های اطلاعاتی و پایگاه داده، یکی از مواردی که اهمیت بسیار زیادی داره "منبع واحد حقیقت" یا SSOT - Single Source of Truth هست.
منبع واحد حقیقت به یک مجموعه رویه برای طراحی یک ساختار و مدل اطلاعاتی می گن که هر قسمت از اطلاعات فقط (و تنها فقط) از یک نقطه وارد و یا ویرایش میشن. هر گونه استفاده از این اطلاعات فقط باید با لینک دهی به منبع اصلی باشه. به این ترتیب، در قسمت های مختلف سیستم، چون تمامی اطلاعات به منبع اصلی ورود آن اطلاعات لینک شده، در صورت نیاز به تغییر و یا استفاده مجدد از اطلاعات، دیگه نیاز به اصلاح تمامی قسمت های سیستم نیست و فقط اصلاح در مبدا صورت می گیره. با این طراحی، تمامی ساختار سیستم به هم مرتبط می شن و از دوگانگی اطلاعات، تضاد در اطلاعات، اصلاح و ورود چند باره ی اطلاعات تکراری جلوگیری می شه.
چطور ارتباطات جداول رو با هم مدیریت کردم؟
همونطور که بالاتر گفتم یکی از معایب انتخاب گوگل شیت به عنوان دیتابیس، عدم امکان ایجاد ارتباط واقعی مثل MySQL هست. ولی به روش هایی میشه این مورد رو مدیریت کرد. خوب ما برای هر جدول Primary key تعیین کردیم و برای استفاده از متغیر در اون جدول از این Primary key استفاده می کنیم. اگر بخوایم متغیر دیگه ای رو از جدولی که از کلید اصلیش توی جدول دیگه استفاده کردیم میتونیم از فرمول vlookup و یا xlookup برای ایجاد ارتباط بین جداول استفاده کنیم.
برای مثال میخوایم توی جدول مربوط به مصاحبه، به جای اینکه هربار اطلاعات کامل مربوط به یک شغل مثل دپارتمان، مدیر استخدام، و ریکروتر رو وارد کنیم؛ با ایجاد یک رابطه و با وارد کردن primary key جدول positions در جدول interview به عنوان foreign key، تمام اطلاعات مورد نظرمون از جدول positions خود به خود توی جدول interview وارد بشه و همونطور که توی مقاله ی دگرگونی داده ها توضیح دادم، جدولمون برای راحتی گزارش گیری Denormalize بشه و در عین حال هم اون ارتباط وجود داشته باشه تا اگه دیتای اصلیمون در جدول مرجع تغییر کرد، تمام دیتاهای قبلی هم تغییر کنه و update anomaly پیش نیاد.
توی جداول زیر تمامی ستون هایی که خاکستری هستند با فرمول به جدول دیگر مرتبط شدند و با وارد کردن ID اتوماتیک مقادیر مربوط به اون جدول خونده و جایگذاری می شه. با این روش ما بین جدول ها ارتباط ایجاد کردیم.
قبل از فرمول ها:
استفاده از vlookup
چون داده های جدید همیشه در حال وارد شدن در جداولمون هستن، نیاز داریم که فرمول vlookup رو برای تمامی ردیف ها استفاده کنیم. ولی در اون صورت ردیف هایی که داده ای ندارن، فرمول vlookup براشون ارور میده:
برای حل این مشکل میتونیم از فرمول iferror قبل از vlookup استفاده کنیم
این روش خوبه ولی بازم باید فرمول رو تا یه جایی پایین بکشیم و اگر ردیف هامون زیاد باشه دیتامون حجمش خیلی بالا میره و بعد باز با رسیدن داده به اون فرمول باز هم حواسمون به وجود فرمول در هر ردیف باشه و اگر نبود دوباره ایجادش کنیم.
برای حل این مشکل میتونیم از فرمول arrayformula استفاده کنیم که قابلیتش اینه که شما فقط در ردیف اول ستونتون فرمول رو وارد می کنید و نیازی به وارد کردن فرمول در تک تک سلول ها ندارید و حجم شیتتون بالا نمیره و دیگه نیازی به وارد کردن فرمول در ردیف های بعدی نیست. به عبارتی با 1 فرمول شما کار چندین فرمول رو برای 1 ردیف انجام میدین. همینطور اگر نیاز باشه فرمولتون رو تغییر بدید، دیگه نیازی نیست توی تک تک ردیف ها فرمول تغییر کنه و شما فقط فرمول رو در ردیف اول تغییر میدید و توی تمام ردیف ها اعمال می شه.
=arrayformula(iferror(vlookup(C2:C,'Candidate DB'!$A$2:$E,2,false),""))
*توجه کنید که توی این جدوال فقط ردیف اول ستون های خاکستری که با رنگ زرد مشخصشون کردم فرمول دارن و دیگه نیازی به وارد کردن فرمول در ردیف های بعدی نیست و اتومات با وارد کردن دیتا، فرمول روی اونها اعمال می شه. توضیحات بیشتر و مثال های دیگه در مورد arrayformula رو توی این لینک می تونید ببینید.
اکسل این قابلیت رو داره که تاریخ میلادی رو به صورت تاریخ شمسی "نشون" بده ولی گوگل شیت این قابلیت رو نداره و اگر بخوایم گزارش هامون رو با تاریخ های شمسی بگیریم به مشکل می خوریم. یا باید از ابتدا تاریخ هامون رو به صورت شمسی و با ستون ها جدا برای ماه و سال و روز وارد کنیم (چون گوگل شیت تاریخ شمسی رو به عنوان تاریخ نمیشناسه) و یا گزارش هامون رو با تاریخ میلادی بگیریم که خیلی وقت ها از ما این گزارش رو نمیخوان.
روشی که معمولا این مورد رو در پلتفرمهایی که ساپورت تاریخ فارسی ندارن به این صورت هست که یک جدول جدا برای تاریخ تشکیل میدن و ستون اول رو فرمت صحیح تاریخ میلادی میذارن که Primary Key تعریف میشه. ستون های بعدی، فرمت های تاریخ متناظری هست که اون تاریخ میلادی داره. برای مثال تاریخ شمسی، هفته ی شمسی، روز شمسی و … . به این صورت با یک Relationship ما تاریخ متناظر هر تاریخ میلادی رو میتونیم به دست بیاریم. این دیتاست ها در اینترنت موجود هست و میتونید با سرچ پیداشون کنید.
اکثر فرم ها این قابلیت رو دارن که به گوگل شیت کانکت بشن. به این صورت که وقتی فردی فرم رو پر کرد، دیتا به صورت اتوماتیک توی شیتی که شما تعیین می کنید بشینه و نیازی به وارد کردن داده ها به صورت دستی نباشه. قسمت هایی که این کار رو میشه انجام داد توی فرم ساز های معروف میبینید:
گوگل فرم
فرمالو
پرسلاین
تا اینجا در مورد 1. کشف داده ها، 2. دگرگونی داده ها و 3. بارگذاری داده ها صحبت کردم.
در مقاله ی بعدی در مورد "بصری سازه داده ها" و "دشبورد سازی" صحبت می کنم که نتیجه ی همه ی قسمت های قبلی هست و میتونیم نتایج کارهای قبلی رو توی اون دشبوردها ببینیم.
خوشحال میشم اگر سوالی یا نظری داشتین اینجا برام بنویسید و با به اشتراک گذاشتن این مطلب به کسانی که میتونه براشون این مباحث مفید باشه کمک کنید.
