در این مقاله سعیم بر اینه که روش های متفاوت انتقال داده از power bi به sql server رو با هم مرور کنیم. هر روش بسته به نیاز میتونه مورد استفاده شما قرار بگیره ولی اگه به دنبال روش ساده تر و سریع تر هستید و وقت تست و بررسی روش های متفاوت رو ندارید؛ استفاده از Dax Studio رو برای انتقال داده پیشنهاد می کنم. در مثال این مقاله از دیتابیس AdventureworksDW2016CTP3 و جدول FactResellerSales و البته بخشی از ستون ها استفاده می کنم. دیتابیسی هم که قراره جداول رو انتقال بدم دیتابیس local خواهد بود.

 

روش اول _ استفاده از Copy Table:

در این روش می تونید کل داده های جدول رو copy کنید و در اکسل یا یک فایل متنی paste کنید. این روش سادست و البته برای مواردی بهتره استفاده بشه که تعداد جداول کم و همچنین تعداد رکوردها زیاد نباشه چون قراره کل رکورد ها به ram منتقل بشه پس حواستون باشه داده های زیاد داشته باشید و منابع کم به احتمال زیاد کامپیوترتون هنگ میکنه. برای این کار از قسمت Model در power bi جدول مورد نظر رو باز کنید و در قسمتی از جدول راست کلیک کنید و گزینه Copy table رو انتخاب کنید. برای انتقال به دیتابیس بهتره از فایل csv استفاده کنید که انتقال رو راحت تر میکنه و نیاز به سرویس ها و تنظیمات اکسل روی دیتابیس ندارید.

دیتا رو پس از کپی به اکسل منتقل کنید و سپس با فرمت csv ذخیره کنید. و سپس با استفاده از import data از دیتابیس مورد نظر به دیتابیس منتقل کنید. هر چند پیشنهاد من SSIS هستش برای انتقال داده ها چون هم ساده تره و هم به خطاهای عدم مغایرت data type ها بر نمی خورید یا کمتر مواجه میشد.

 

روش دوم _ استفاده از SSIS:

برای استفاده از این روش SSDT رو دانلود و نصب کنید. در مقاله قبل در مورد روش اتصال به power bi از طریق اکسل و دیتابیس توضیح دادم ولی به طور مختصر ما قراره پورت power bi رو پیدا کنیم و از اون طریق به power bi متصل بشیم.

ابتدا لازمه که فایل power bi رو ذخیره کنید و البته فایل باز باشه. در مرحله بعد در قسمت نوار آدرس File Explorer آدرس زیر رو قرار بدید.

فقط دقت کنید اگه پوشه خالی هستش احتمالا شما فایل رو با نسخه Power BI RS باز کردید که اگه چند پوشه به عقب برگردید می تونید فولدر Power BI Desktop SSRS رو پیدا کنید و از اون مسیر ادامه بدید. در ادامه به پوشه AnalysisServicesWorkspacesXXXXXXXXX وارد بشید مدل در این پوشه قرار گرفته. البته شماره ای هم در انتهاش وجود داره. اگر بیشتر از یه پوشه وجود داره به خاطر اینه که شما چند فایل power bi باز کردید!

به پوشه Data وارد بشید و فایل “msmdsrv.port.txt” رو پیدا کنید. پورت موردنظرمون در این فایل ذخیره شده.

قدم بعدی ساخت پروژه Integration جدید در SSIS خواهد بود.

اگر پیدا نکردید Integration Service رو لازم به نصب دارید!

نام گذاری پروژه و مشخص کردن مسیر ذخیره مرحله بعد خواهد بود

یک Data Flow Task در صفحه قرار دهید و از Other Sources تسک OLEDB Source رو در صفحه قرار دهید.

با دابل کلیک و باز کردن پنچره OLEDB Source Editor یک connection جدید ایجاد کنید.

در پنجره Connection Manger

• قسمت Provider رو به Native OLEDB\Microsoft OLEDB Provider for Analysis Services 13.0 تغییر بدید
• کادر Location رو با ip و port پر کنید برای مثال ما به این صورت خواهد شد localhost:1052 یا میتونید 127.0.0.1:1052 هم قرار بدید. دقت کنید شما به جای 1052 از پورتی که در مرحله قبل بدست آوردید استفاده کنید. همچنین این پورت به صورت رندم ایجاد میشه پس در موارد بعدی تغییر خواهد کرد
• قسمت initial catalog رو هم از لیست با مقدار موجود پر کنید
• در انتها test connection باید موفقیت آمیز باشه

با تایید connection به قسمت OLEDB Source Editor برگردید در این قسمت لیست تمام جداول موجود در مدل رو میتونید مشاهده کنید

اما گزینه دیگه SQL Command که قابلیت استفاده از اسکریپت های DAX رو به ما میده . در مثال ما چون کل رکورد های جدول مد نظره به این صورت خواهد شد:

 

برای مقصد هم از OLEDB Destination استفاده می کنیم. connection رو با provider از نوع Native OLE DB\SQL Server Native Client ایجاد کنید و آدرس سرور و دیتابیس مورد نظر رو وارد کنید. به OLEDB Destination Editor بازگردید. یک جدول جدید ایجاد کنید.

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

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

قسمت Mapping رو هم اصلاح کنید و فیلدهای درست رو به هم متصل کنید.

و در نهایت انتقال داده ها:

 

 

روش سوم _ استفاده از Dax Studio:

در ابتدا Dax studio رو نصب کنید. مثال رو در حال حاضر با نسخه DaxStudio_2_17_3 پیش خواهم برد. در این روش هم لازمه فایل مورد نظر power bi رو باز کنید. سپس از طریق connect در نرم افزار به مدل متصل بشید.

گزینه Export Data را از Advanced انتخاب کنید.

از پنجره باز شده گزینه SQL Tables رو انتخاب کنید.

سرور مورد نظر و دیتابیس مقصد رو مشخص کنید.

جداول مورد نظر برای انتقال رو انتخاب کنید.

و انتقال داده! و جدولی به همان نام در دیتابیس مقصد ایجاد و داده ها ذخیره می شوند. به همین سادگی!

روش چهارم _ استفاده از Python:

در استفاده از این روش از script قسمت query editor استفاده می کنیم هر چند این کار لقمه رو دور سر پیچوندنه ولی توضیحش خالی از لطف نیست. برای این کار لازمه کتابخونه های زیر رو نصب کنید.

• pyodbc
• pandas
• sqlalchemy

کد رو زیاد توضیح نمیدم ولی خلاصش اینه که به وسیله to_sql از کتابخونه pandas قراره جدولی در دیتابیس ایجاد کنیم و داده ها رو insert کنیم. برای این کار لازمه با استفاده از pyodbc ارتباطی با دیتابیس mssql ایجاد کنیم. هر چند که می تونیم این انتقال رو با iterrows بر روی تک تک سطرها و انجام یک اسکریپت insert into انجام بدیم که نیازمند ساخت جدول قبل از ورود داده ها و map  همه ستون هاست (نمونه ای از این کار رو در این لینک ببنید).

اما برای این که کار رو ساده تر کنیم با استفاده از sqlalchemy یک engine میسازیم و از اون طریق متصل میشیم و در حقیقت بخش زیادی از کنترل و مدیریت کار رو به این کتابخونه میسپریم. ابتدا در jupyter کد رو اجرا می کنم که داده هایی رو از فایلی خونده در df ذخیره می کنیم به صورت data frame و سپس داده ها رو به جدول مورد نظر بر روی دیتابیس منتقل می کنیم و در نهایت خروجی تعداد رکوردها رو برمی گردونیم.

برای این که داده ها رو از power bi منتقل کنیم لازمه df رو به dataset تغییر بدیم به این صورت:

و یک python script رو اجرا بگیریم.

و خروجی: