Интеграция данных Power BI + Azure SQL DB + Office 365
Приветствую всех.
Сегодня на второй встрече SharePoint User Group мы рассмотрели интересную тему по работе с данными, используя Power BI + Azure SQL DB + Office 365.
В этой статье я хочу поделиться примером, который был показан.
Сценарий примера заключается в том, чтобы создать механизм авто обновления данных, в нашем случае данные из сайта SharePoint Online должны синхронизироваться с SQL Azure DB и потом Power BI отчёт должен автоматически забирать их себе в отчёт.
Для примера возьмём один список задач в SharePoint Online с задачами, которые должны синхронизироваться с нашей базой данных в Azure.
1. Создадим базу данных в SQL Azure с названием PowerBIDB
2. Создадим таблицу Tasks и создадим одну хранимую процедуру для загрузки данных.
CREATE TABLE [dbo].[Tasks] (
[Id] INT NOT NULL,
[Title] NVARCHAR (100) NULL,
[AssignedTo] NVARCHAR (100) NULL,
[Status] NVARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE PROCEDURE [dbo].[InsertTasks]
@Id int,
@title nvarchar(100),
@AssignedTo nvarchar(100),
@Status nvarchar(50)
AS
insert into Tasks (Id, Title, AssignedTo, Status) values (@Id, @Title, @AssignedTo, @Status)
3. Создадим консольное приложение в Visual Studio 2015 с названием PowerBIApp
4. Далее запустим NutGet Manager и найдем библиотеку AppForSharePointWebToolKit
5. В app.config добавляет логин и пароль учётной записи SharePoint под которой будем получать данные.
<appSettings>
<add key="SPOAccount" value="Ваш логин" />
<add key="SPOPassword" value="Ваш пароль" />
</appSettings>
6. Создаём два метода на получение логина GetSPOAccountName() и пароля GetSPOSecureStringPassword()
private static SecureString GetSPOSecureStringPassword()
{
try
{
Console.WriteLine(" --> Entered GetSPOSecureStringPassword()");
var secureString = new SecureString();
foreach (char c in ConfigurationManager.AppSettings["SPOPassword"])
{
secureString.AppendChar(c);
}
Console.WriteLine(" --> Constructed the secure password");
return secureString;
}
catch
{
throw;
}
}
private static string GetSPOAccountName()
{
try
{
Console.WriteLine(" --> Entered GetSPOAccountName()");
return ConfigurationManager.AppSettings["SPOAccount"];
}
catch
{
throw;
}
}
7. Создаём обработчик доступа к данным SharePoint
private static ListItemCollection getTaskItems(string lastItemIdOnPage, int itemCount, ClientContext context, string titleOfList)
{
ListItemCollection listItems = null;
if (context != null)
{
// int iSkip = pageIndex * recCount - recCount;
List list = context.Web.Lists.GetByTitle(titleOfList); // replace with your list name
ListItemCollectionPosition itemPosition = new ListItemCollectionPosition();
itemPosition.PagingInfo = lastItemIdOnPage;//string.Format("Paged=TRUE&p_ID={0}", iSkip);
CamlQuery camlQuery = new CamlQuery();
camlQuery.ListItemCollectionPosition = itemPosition;
camlQuery.ViewXml = @"<View>
<ViewFields>
<FieldRef Name='Title'/>
<FieldRef Name='PercentComplete'/>
<FieldRef Name='DueDate'/>
<FieldRef Name='StartDate'/>
<FieldRef Name='Status'/>
<FieldRef Name='Checkmark'/>
<FieldRef Name='Modified'/>
<FieldRef Name='Body'/>
<FieldRef Name='AssignedTo'/>
<FieldRef Name='Priority'/>
<FieldRef Name='notes'/>
<FieldRef Name='result'/>
<FieldRef Name='Created'/>
<FieldRef Name='Author'/>
<FieldRef Name='Editor'/>
<FieldRef Name='ParentId'/>
</ViewFields>
<RowLimit>" + itemCount + @"</RowLimit>
</View>";
listItems = list.GetItems(camlQuery);
context.Load(listItems);
context.ExecuteQuery();
}
return listItems;
}
8. Создаём модель данных для доступа к базе данных SQL Azure с названием PowerBIDBModel
9. Создаём обработчик для записи данных в SQL Azure DB
private static void ImportTasksToDB(ListItemCollection items, ClientContext context)
{
using (PowerBIDBEntities enty = new PowerBIDBEntities())
{
string Status, Name, AssignedTo;
int? Id;
foreach (ListItem item in items)
{
Id = 0;
Status = null;
Name = null;
AssignedTo = null;
if (item["Title"] != null)
Name = item["Title"].ToString();
if (item["Status"] != null)
Status = item["Status"].ToString();
if (item["AssignedTo"] != null)
{
foreach (FieldUserValue AssignedToF in item["AssignedTo"] as FieldUserValue[])
{
if (AssignedToF != null)
{
AssignedTo = AssignedToF.LookupValue + "; ";
}
}
}
enty.InsertTasks(item.Id, Name, AssignedTo, Status);
}
enty.SaveChanges();
}
}
10. Публикуем решение как Azure Web Job с названием PowerBIApp
11. Далее находим Web Job в Azure и запускаем его.
12. Смотрим результат.
13. Далее необходимо создать отчёт используя PowerBI Desktop и опубликовать его в PowerBI.
14. В настройках dataset необходимо настроить также планировщик
Вот и всё. Исходный код можно скачать здесь.