هوش تجاری (Business Intelligence)

هوش تجاری (Business Intelligence)

به اشتراک بگذاریم برای یادگیری، یاد بگیریم برای به اشتراک گذاری
هوش تجاری (Business Intelligence)

هوش تجاری (Business Intelligence)

به اشتراک بگذاریم برای یادگیری، یاد بگیریم برای به اشتراک گذاری

تبدیل تاریخ شمسی به میلادی،میلادی به شمسی ومیلادی به قمری در SQL

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


لازم به ذکر است که تابع تبدیل تاریخ میلادی به شمسی توسط آقای رضا راد نوشته شده است.


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

پس از دانلود و اجرای کوئری ها، از طریق دستورات زیر میتوانید تبدیل تارخ میلادی به شمسی و شمسی به میلادی را انجام دهید.


از کوئری‌ زیر برای مشاهده نتیجه استفاده کنید.

select dbo.GregorianToPersian('1980/01/01')

select dbo.ShamsitoMiladi('1358/10/11')

تبدیل تاریخ میلادی به قمری

 

از دستور زیر برای تبدلی تاریخ میلادی به قمری استفاده کنید.

 

SELECT CONVERT (nvarchar(30),GETDATE(),130) as Date

 

برای بدست آوردن سن از دستور زیر استفاده کنید.

 

SELECT DateDiff(yy , (select  dbo.ShamsitoMiladi ('1365/01/01') ), GetDate())

تبدیل_تاریخ SQL هوش تجاری داده کاوی انبار داده تبدیل_تاریخ_شمسی به میلادی
حامد پالیک یکشنبه 19 آذر 1391 ساعت 11:48 ق.ظ
13 نظر

مدل‌های چند بعدی و جدولی در SQL Server 2012

در BI برای ایجاد گزارشات چند بعدی و تحلیل داده‌ها  از مدل سازی چند بعدی (Multidimensional Modeling) استفاده می‌شود. مدل سازی چند بعدی بر پایه‌ی روش سنتی تحلیل‌های بر خط (OLAP) ؛ مکعب‌های داده‌ای، معیارها و ابعاد را ایجاد می‌کند. در OLAP چند تکنیک‌ ذخیره سازی وجود دارد و به واسطه‌ی آن، با وجود حجم زیادی از داده‌ها سرعت پاسخ به کوئری‌ها کوتاه است.

با انتشار SQL Server 2012 از سوی ماکروسافت قابلیت‌ها و ابزارهای جدیدی نیز به SQL اضافه شد. یکی از این قابلیت‌ها در بخش هوش تجاری، مدل سازی جدولی یا فهرستی (Tabular Modeling ) می‌باشد که نوع دیگری از مدل سازی است. از این نوع مدل سازی نیز در ایجاد گزارشات و تحلیل داده‌ها استفاده می شود.

 مدل‌ سازی جدولی بر اساس PowerPivot و برای Excel 2010 ایجاد شده است. در مدل سازی جدولی داده‌ها در جداول رابطه‌ای سازماندهی می‌شوند و از دو روش In-Memory و DirectQuery برای ذخیره‌سازی استفاده می‌کند و به همین دلیل سرعت دسترسی به داده‌ها بسیار بالا است. این نوع مدل سازی برای افرادی که سالهاست با برنامه‌هایی همچون Excel کار می‌کنند بسیار مناسب است.

جهت استفاده از حالت جدولی در SQL باید در هنگام نصب برنامه مشخص نمود که قصد استفاده از مدل‌سازی جدولی را داریم. 

در جدول زیر برخی از مهمترین ویژگی‌های دو مدل چند بعدی و جدولی با یکدیگر مقایسه شده‌اند.


ویژگی‌ها

مدل‌سازی چند بعدی

مدل‌سازی جدولی

توضیحات

مدت زمان ایجاد یک Solution

زیاد

کم

در مدل‌سازی جدولی سرعت ایجاد Solution بسیار بالاتر است.

مدت زمان یادگیری

زیاد

کم

یادگیری مدل‌سازی جدولی بسیار سریع‌تر از مدل‌های چند بعدی است.

نحوه ارتباط میان داده‌ها

- یک به چند

- چند به چند

- یک به چند

- چند به چند با استفاده از عبارات DAX

در مدل‌سازی جدولی فقط با استفاده از DAX می‌توان به یک ارتباط چند به چند دست پیدا کرد.

سلسله مراتب‌ها

- سلسله مراتب‌های استاندارد

- سلسله مراتب‌های پدر فرزندی

 

- سلسله مراتب‌های استاندارد

- سلسله مراتب‌های پدر فرزندی با استفاده از DAX

در مدل‌سازی جدولی فقط با استفاده از DAX می‌توان سلسله مراتب‌های پدر فرزندی ایجاد نمود.

ویژگی‌های اضافی در مدل‌سازی

Perspectives, translations, actions, drillthrough, stored procedures, and write-back.

Perspectives and drillthrough.

در مدل سازی جدولی فقط می‌توان از drillthrough و Perspectives استفاده نمود.

زبان برنامه ‌نویسی

MDX

DAX

در مدل سازی جدولی برای نوشتن عبارات محاسباتی از DAX استفاده می‌شود.

عبارات محاسباتی

تمامی عبارات محاسباتی ساده و پیچیده

تمامی عبارات محاسباتی ساده و برخی از عبارات پیچیده

در مدل سازی جدولی نمی‌توان از بسیاری عبارات محاسباتی پیچیده استفاده کرد.

توابع تجمیعی

Sum, Count, Min, Max, Distinct Count, None, ByAccount, AverageOfChildren, FirstChild, LastChild, FirstNonEmpty, and LastNonEmpty.

Sum, Count, Min, Max, Average, DistinctCount, and various time intelligence functions like FirstDate, LastDate, OpeningBalanceMonth, and ClosingBalanceMonth.

در این قسمت نیز در مدل چند بعدی بهتر عمل شده است.

کلید‌های ارزیابی عملکرد (KPIs)

دارد

دارد

 

تبدیل نرخ ارز

 

 

پشتیبانی توسط

Business Intelligence Wizard 

پشتیبانی توسط DAX

 

حجم داده‌ها جهت دسترسی و ذخیره‌سازی

چندین ترابایت

چند میلیون رکورد

مدل‌سازی جدولی برا حجم وسیعی از داده‌ها پیشنهاد نمی‌شود.

منبع داده‌ها

بانک‌های اطلاعاتی رابطه‌ای

- بانک‌های اطلاعاتی رابطه‌ای

- فایل‌های Excel

- فایل‌های متنی

- OData feeds

Azure Data Market-

Analysis Services-

 

ذخیره‌سازی داده‌ها

-MOLAP

-ROLAP

- In-Memory

-DirectQuery

In-Memory تمامی داده‌ها در حافظه کش می‌شوند.

DirectQuery داده‌ها در SQL Server 2012 ذخیره می‌شوند.

فشرده سازی داده‌ها

معمولا تا 3x

معمولا تا 10x

 

امنیت

- امنیت در سطح عضوهای دایمنشن

- امنیت در سطح هر سطر

 

 


با توجه به جدول بالا به نظر می‌رسد استفاده از مدل‌سازی جدولی برای سازمان‌های کوچکی که حجم داده‌ کمی دارند بسیار مفید، کم هزینه و مناسب باشد.


منبع

حامد پالیک شنبه 18 آذر 1391 ساعت 04:13 ب.ظ
0 نظر

بدست آوردن تعداد سطر‌های جدول

معمولا برای بدست آوردن تعداد سطر‌های یک جدول از دستور زیر استفاده می‌شود.

Select count(*) from table_name 

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

select sum(1) from table_name

select count(1) from table_name

'exec sp_spaceused ‘table_name

DBCC CHECKTABLE(‘table_name’)

حامد پالیک شنبه 18 آذر 1391 ساعت 09:14 ق.ظ
0 نظر

مراحل و نحوه بارگذاری داده ها در انبار داده

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


۱- استخراج داده‌ها از پایگاه‌های داده به یک مخزن واحد

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


۲- پالایش داده‌ها

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

در پالایش داده‌ها مراحل زیر انجام می‌شود.


پاک‌سازی داده‌ها (Data Cleaner): ممکن است در دنیای امروز میلیون‌ها مجموعه داده وجود داشته باشد، اما به راستی تمام این مجموعه از داده‌ها بدون اشکال هستند؟ آیا تمامی مقادیر فیلدهای هر رکورد  پر شده است  و یا  مقادیر داخل فیلدها داده‌های صحیح دارند؟ اگر داده‌ها  از منابع یکسان مثل فایل‌ها  یا پایگاه‌های داده‌ای گرفته شوند خطاهایی از قبیل اشتباهات تایپی، داده‌ها‌ی نادرست و فیلدهای بدون مقدار را خواهیم داشت و چنانچه داده‌ها  از منابع مختلف مثل پایگاه داده‌ها‌ی مختلف یا سیستم اطلاعاتی مبتنی بر وب گرفته شوند با توجه به نمایش‌ها‌ی داده‌ای مختلف خطاها بیشتر بوده و پاک‌سازی داده‌ها  اهمیت بیشتری پیدا خواهد کرد.

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

یکپارچه‌سازی (Integration): این فاز شامل ترکیب داده­های دریافتی از منابع اطلاعاتی مختلف، استفاده از متاداده‌ها برای شناسایی، حذف افزونگی داده­ها، تشخیص و رفع برخوردهای داده­ای می­باشد.

یکپارچه سازی داده­ها از سه فاز کلی تشکیل شده است:

شناسایی فیلدهای یکسان: فیلدهای یکسان که در جدول‌های مختلف دارای نام‌های مختلف می‌باشند.

شناسایی افزونگی‌های موجود در داده‌های ورودی: داده­های ورودی گاهی دارای افزونگی هستند. مثلاً بخشی از رکورد در جدول دیگری وجود دارد.

مشخص کردن برخورد‌های داده­ای: مثالی از برخوردهای داده­ای، یکسان نبودن واحدهای نمایش داده­ای است. مثلاً فیلد وزن در یک جدول بر حسب کیلوگرم و در جدولی دیگر بر حسب گرم ذخیره شده است.

تبدیل داده‌ها (Data Transformation): در مجموعه داده های بزرگ، به نمونه هایی که از رفتار کلی مدل داده ای تبعیت نمی‌کنند و بطور کلی متفاوت یا ناهماهنگ با مجموعه باقیانده داده ها هستند، داده های نامنطبق گفته می‌شود.

داده‌های نامنطبق می‌توانند توسط خطای اندازه گیری ایجاد شونده یا نتیجه نوع داده ای درونی باشند. برای مثال اگر سن فردی در پایگاه داده 1- باشد، مقدار فوق قطعا غلط است و با یک مقدار پیش فرض فیلد "سن ثبت نشده" می تواند در برنامه مشخص گردد.

کاهش داده‌ها (Reduction): در این مرحله، عملیات کاهش داده­ها انجام می­گیرد که شامل تکنیک‌هایی برای نمایش کمینه اطلاعات موجود است.

این فاز از سه بخش  تشکیل می­شود:


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

فشرده سازی داده­ها: از تکنیک‌های فشرده­سازی برای کاهش اندازه داده­ها استفاده می­شود.

کد کردن داده­ها: داده­ها در صورت امکان با پارامترها و اطلاعات کوچک‌تر جایگزین می­شوند.


٣- بارگذاری داده های پالایش شده

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

حال انبار داده‌ها با مقادیر اولیه ساخته شده است، اما این پایان کار نیست! هر روزه داده‌های بیشتر و جدیدی به پایگاه‌ داده‌ها اضافه می‌شود و باید شرایطی را فراهم کنیم تا این داده‌ها به صورت خودکار و بدون دخالت کاربر، پس از استخراج و پالایش در انبار داده بارگذاری شود.

پس از بارگذاری داده‌ها نوبت به استفاده از اطلاعات ذخیره شده در انبار داده‌ها است. این کار توسط ابزارهای گزارش گیری (Reporting Services)، داده‌کاوی و OLAP انجام می‌شود.



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


منبع عکس

حامد پالیک چهارشنبه 15 آذر 1391 ساعت 12:12 ب.ظ
2 نظر

حذف صفر‌های سمت چپ عدد در SQL

برای اینکار فقط کافیست داده‌ها را به نوع int تبدیل کنید. برای درک بهتر این موضوع کوئری زیر را اجرا کنید. 

 

create table #data (data varchar(10))

insert into #data

select '12345' as data union all

select '00123' as data union all

select '0060' union all

select '00101' union all

select '00021' union all  

select '20000'

select   data as Before,

case when convert(int,data) is not null then cast(cast(data as int) as varchar(10)) else data end as After 

from    #data

حامد پالیک دوشنبه 13 آذر 1391 ساعت 01:34 ب.ظ
0 نظر