Sergey Belskiy Technical Blog

All about SharePoint

Интеграция данных 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 необходимо настроить также планировщик

 

 

Вот и всё. Исходный код можно скачать здесь.

PowerBIApp.zip