Writeback в SSAS 2012. Маленький пример - план-факт(часть вторая)

 

В Tabular модели как-то не сложилось у Microsoft c writeback.  Writeback mdx в  данной модели нет (это подтверждается данной статьей на msdn). Утверждают, что запись в базу доступна через режим DirectQuery + XVelocity, но я этого не смог добиться. Но все по порядку….

Как было в первой части, для проекта берется база AdventureWorks.

Создаем проект 

1. Определяем источник данных, добавляем таблицы в модель FactFinance, PlanFinace (таблицы фактов), DimOrganisation, DimDepartamentGroup,DimDate. Создаем связи.

2. Создадим показатели в таблице FactFinance - Amount агрегация  Sum и  Average, PlanFinance – Amount агрегация Sum.

Модель создали, вроде бы все ок. А вот теперь  начинается самое интересное.

В Tabular модели есть 4 режима:

1. DirectQuery  -  запросы отправляются реляционному источнику, кэш при этом не используется.
2. DirectQueryWithInMemory – запросы  отправляется реляционному источнику, также может использоваться в кэш. Отправка в реляционку первоочередная.
3. InMemory – запросы отправляются в кэш
4. InMemoryWithDirectQuery – запросы отправляются в кэш, может использоваться реляционка. Отправка в кэш первоочередная(по умолчанию)

Первые два режима работают с DAX запросами и не поддерживают MDX,  последних два режима как DAX, так и MDX. А теперь самое интересное  - клиент, который может работать с режимами DirectQuery,  это Excel 2013. Если у вас ниже офис, то тогда   с такими режимами может работать PowerView или SSRS.  Ну в принципе это было ожидаемо и я думаю в ближайшем будущем выйду дополнения для 2010 офиса. Также DirectQuery не поддерживают некоторые функции DAX, поэтому прежде чем писать формулы на DAX, включите directquery.

Данные режимы включаются следующим образом:

В свойствах модели  DirectQueryMode  поставить On, если выбудете работать с режимами DirectQuery  или DirectQueryWithInMemory

В свойствах проекта выбираем QueryMode:

И делаем деплой.

Как обычно проект лежит здесь.

Так как же обратная запись – да в принципе никак. Потому что не один клиент ни в каком режиме не позволил сделать изменения в реляционном источнике. Поэтому вопрос, связанный с обратной записью, остается открытым. Я думаю вряд ли Microsoft оставит такой продукт без данной функции.

Поживем – увидим.

Writeback в SSAS 2012. Маленький пример - план-факт(часть первая)

Для чего применяется writeback?  И что такое writeback? 
Кратко, writeback – это обратная запись.  То есть, используя excel 2010(в принципе любой клиент, который поддерживает writeback) вы можете внести изменения в  данные хранилища или измерения. Зачем это?
Данная опция нужна для создания “план-факт” анализа, бюджетирования, корректировки данных в измерениях. Рассмотрим небольшой проект план-факт  и как раз на основе данного проекта я покажу работу с writeback.  Источником данных возьмем AdventureWorks 2012. Плюс сделаем отдельную таблицу для плановых значений (она фактически копия таблицы FactFinance). Код для создания таблицы:

USE [AdventureWorksDW2012]

GO

/****** Object:  Table [dbo].[PlanFinance]    Script Date: 17.07.2012 13:10:23 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[PlanFinance](

    [FinanceKey] [int] IDENTITY(1,1) NOT NULL,

    [DateKey] [int] NOT NULL,

    [OrganizationKey] [int] NOT NULL,

    [DepartmentGroupKey] [int] NOT NULL,

    [ScenarioKey] [int] NOT NULL,

    [AccountKey] [int] NOT NULL,

    [Amount] [float] NOT NULL,

    [Date] [datetime] NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PlanFinance]  WITH CHECK ADD  CONSTRAINT [FK_PlanFinance_DimAccount] FOREIGN KEY([AccountKey])

REFERENCES [dbo].[DimAccount] ([AccountKey])

GO

ALTER TABLE [dbo].[PlanFinance] CHECK CONSTRAINT [FK_PlanFinance_DimAccount]

GO

ALTER TABLE [dbo].[PlanFinance]  WITH CHECK ADD  CONSTRAINT [FK_PlanFinance_DimDate] FOREIGN KEY([DateKey])

REFERENCES [dbo].[DimDate] ([DateKey])

GO

ALTER TABLE [dbo].[PlanFinance] CHECK CONSTRAINT [FK_PlanFinance_DimDate]

GO

ALTER TABLE [dbo].[PlanFinance]  WITH CHECK ADD  CONSTRAINT [FK_PlanFinance_DimDepartmentGroup] FOREIGN KEY([DepartmentGroupKey])

REFERENCES [dbo].[DimDepartmentGroup] ([DepartmentGroupKey])

GO

ALTER TABLE [dbo].[PlanFinance] CHECK CONSTRAINT [FK_PlanFinance_DimDepartmentGroup]

GO

ALTER TABLE [dbo].[PlanFinance]  WITH CHECK ADD  CONSTRAINT [FK_PlanFinance_DimOrganization] FOREIGN KEY([OrganizationKey])

REFERENCES [dbo].[DimOrganization] ([OrganizationKey])

GO

ALTER TABLE [dbo].[PlanFinance] CHECK CONSTRAINT [FK_PlanFinance_DimOrganization]

GO

ALTER TABLE [dbo].[PlanFinance]  WITH CHECK ADD  CONSTRAINT [FK_PlanFinance_DimScenario] FOREIGN KEY([ScenarioKey])

REFERENCES [dbo].[DimScenario] ([ScenarioKey])

GO

ALTER TABLE [dbo].[PlanFinance] CHECK CONSTRAINT [FK_PlanFinance_DimScenario]

GO

В связи с тем, что для проектов я уже использую SQL 2012, то рассмотрим реализацию writebak, как в многомерной модели, так и в Tabular модели.
1 Многомерная модель

• Создаем многомерную модель в VS 2010 на основе таблиц FactFinance, PlanFinace (таблицы фактов), DimOrganisation, DimDepartamentGroup,DimDate

• Создаем 3 измерения Dim_Date, Dim_Departament, Dim_Organisation
• Создаем куб и 2 показателя. Один из таблицы FactFinance – Amount, где хранятся наши факты, функцию агрегации используем LastNonEmpty(так как это остатки), второй из таблицы PlanFinance- Amount, функцию агрегации делаем Sum. Название у показателей соответствующие - FactAmount и PlanAmount.
• Включаем writeback. Для этого переходим в раздел Partitions  и правой кнопкой мышки нажимаем на партицию PlanFinance, в выпадающем меню выбираем writeback settings

• В  появившемся окне вводим название таблицы, куда будут записываться изменения, источник данных и Storage Mode: Molap или Rolap. Я использую Molap.

• Нажимаем Ok и деплоем проект. В итоге в хранилище мы должны получить новую таблицу, в моем случае WriteTable_PlanFinance, в которую теперь будут записываться  новые данные, а также  логирование, когда была вставлена запись и кем.

Ограничения: К сожалению, есть ограничения, writeback  работает только с показателями, у которых функция агрегации суммирование. Поэтому например  показатель остатки по счетам или на складе, которые  используются с функцией агрегации LastEmpty или LastNonEmpty, необходимо будет реализовывать через фиктивные функции.
И как обычно выкладываю проект, который вы может скачать здесь.
Во второй части, я расскажу реализацию writeback в  Tabular модели SSAS