Извлечение данных Dynamics NAV через прямое подключение к SQL Server

Итак, в предыдущей статье  мы узнали как скопировать данные из Dynamics NAV в Microsoft Excel.
Способы это были быстрые и приятные (почти), но подходят они только для относительно небольших объемов данных.
Что делать если нужно много данных? Например, я хочу получить текущие остатки в количественном и стоимостном выражении (скажу по секрету, в последнее время я регулярно занимаюсь миграцией данных и переношу товарные транзакции за несколько лет).

Обороты за несколько лет это 150000 – 500000 записей в товар книга операций и столько же в операциях стоимости. Копировать это через буфер обмена довольно грустно, поэтому я использую возможность MS Excel по подключению к внешним источникам данных, таким как SQL Server (а последние три года я пользую исключительно его, даже на малых инсталляциях).

Начнем с того, что запустим Microsoft Excel (я использую версию 2010), перейдем на закладку Данные и выберем кнопку Получение внешних данных.

Получение внешних данных

В подменю следует нажать кнопку Получение из других источников – С сервера SQL Server
Откроется окно, где следует указать имя SQL Server, а также способ аутентификации.

Выбор SQL Server и способа аутентификации

Я рекомендую прислушаться к советам Microsoft и использовать Windows аутентификацию.

Кстати если вы испытываете затруднение при выборе сервера, то нужно в Dynamics NAV выбрать меню Файл – База данных – Информация. Там же можно узнать имя базы данных.

Информация о базе данных Microsoft Dynamics NAV

Итак, после выбора сервера, Microsoft Excel предложит вам выбрать базу данных и таблицу.

Выбор таблицы для подключения

Примечание: если вы не видите требуемую базу данных в списке, проверьте, что у пользователя, под которым вы подключаетесь к базе данных, есть соответствующие права. На мой взгляд, достаточно прав db_datareader. Для этого потребуется подключиться к SQL Server с помощью Microsoft SQL Server Management Studio.

Назначение прав db_datereader

Где смотреть имя базы данных я сообщал ранее, а теперь пару слов о таблицах.

  1. В SQL не отображаются идентификаторы таблиц (номера), только их имена (кстати, это одна из причин по которой в базе данных нельзя иметь объекты с одинаковыми именами).
  2. Таблицы отсортированы по алфавиту. У большинства таблиц есть префикс, состоящий из названия Организации и символа $. Дело в том, что большинство таблиц хранят данные на уровне Организации, поэтому в SQL создается столько таблиц, сколько Организаций в базе. Также в базе данных существует ряд таблиц, которые хранят данные независимо от Организации, у нее префикса нет.

Примечание: чтобы сделать таблицу, которая хранит данные независимо от Организации, нужно в ее свойствах указать в ее свойствах DataPerCompany = No. Детальная инструкция: в Dynamics NAV, меню Инструменты -> Object Designer, выбрать таблицу, нажать кнопку Design, установить курсор на пустую строку и выбрать меню Вид -> Properties. Делать это надо очень осторожно, особенно если данная таблица имеет связи с полями других таблицами.

Итак, выбрали таблицу, теперь можно нажать кнопку Готово. В окне Импорт данных выбираем режим Таблица, вставку на имеющийся лист.

Импорт данных в Microsoft Excel из Dynamics NAV

Microsoft Excel подключится к базе данных и отобразит на листе данные из выбранной таблицы.

Данные Dynamics NAV на листе Microsoft Excel

Что можно делать с этими данными будет рассказано в следующей статье .

Кроме таблицы также можно выбрать индексированное представление (Indexed View), которое отличается от таблиц наличием суффикса $VSIFT$X.
При выборе индексированного представления на листе данных будут отображаться поля входящие в ключ и поля SumIndexFields, на которых данное индексированние представление и основывается (подробнее про SumIndexFields и технологию SIFT можно прочесть в приложении к данной статье).
Это возможность одновременно имеет и плюс и минусы.
Плюс - на листе будут отражены агрегированные данные. Т.е. если вы выберете индексированное представление, которое базируется на ключе

Item No.,Location Code,Posting Date с полями SumIndexFields: Invoiced Quantity и Cost Amount (Actual)

То на листе Microsoft Excel по товару Х на складе У за 15е число вы получите одну запись независимо от количества операций в таблице.
Основной минус заключается в том, что довольно сложно определить, какое именно индексированное представление выбрать.

Есть два варианта решить эту проблему.
Вариант 1. Нужно подключиться к SQL Server с помощью Microsoft SQL Server Management Studio. Затем открыть нужную базу данных, перейти в папку Views и посмотреть состав полей (Columns) для всех индексированных представлений, связанных с нужной таблицей.

Состав полей входящих в индексированное представление

Сверху перечислены поля, которые входят в ключю Ниже, с префиксом SUM$ перечислены поля, накапливающие данные.

Вариант2.
Следует в Dynamics NAV создать списочную форму на базе таблицы 2000000063 Key (в списке таблиц данная таблица не видна). Добавить в форму столбцы TableNo, No., TableName, Key, SumIndexFields, MaintainSIFTIndex

Мастер форм в Dynamics NAV

С помощью этой формы вы можете относительно легко узнать какие поля связаны с тем или иным индексированным представлением.

Ключи в Dynamics NAV

Хочу предупредить, что поле No в этом деле не помощник. Дело в том, что при создании индексированных представлений на SQL Server используются ключи, для которых включен флаг MaintainSIFTIndex.

Например, индексированное представления CRONUS Россия ЗАО$Value Entry$VSIFT$6 основывается на ключе 7 (потому как ключ 2 пропущен – для него не установлен флаг в поле MaintainSIFTIndex).

Поэтому считаем руками или пишем функцию.

Теперь, когда мы знаем на что рассчитывать при подключении в качестве источника данных индексированное представление, давайте посмотрим как будет выглядеть результат.

Подключение индексированного представления в качестве внешнего источника данных

В новой выборке записей оказалось 431 против 555, полученных непосредственно из Value Entry, но итог по полю Cost Amount (Actual) не изменился.

Приложение: Специальные Поля Таблиц. Фрагмент книги Руководство разработчика приложений

В дополнение к обычным полям, которые применяются для хранения данных в таблицах, есть еще три вида специальных полей которые используются для получения данных:

  • SumIndexFields (Суммирующие Поля)
  • FlowFields (Вычисляемые Поля)
  • FlowFilter

Что такое суммирующие поля (SumIndexFields)?
SumIndexField – это десятичное поле, которое можно присоединить к определению ключа. Это фундаментальная особенность баз данных в формате Dynamics NAV, составляющая основу для FlowFields. Суммирующие поля SumIndexFields позволяют быстро выполнять вычисление сумм числовых столбцов в таблицах, в том числе даже в таблицах, содержащих тысячи записей. Это достигается благодаря тому, что поля SumIndexFields обновляются при обновлении записей базы данных.

Какие преимущества дают поля SumIndexFields?
SumIndexField позволяет быстро вычислять сумму столбцов и отображать ее через FlowFields.
Например, необходимо получить сумму всех значений в поле Amount. В обычной системе баз данных СУБД приходится получать доступ к каждой записи и добавлять каждое значение поля Amount. Это занимает много времени, если база данных содержит тысячи записей. Благодаря системе Dynamics NAV для получения суммы поля Amount этих записей доступ должен быть получен всего лишь дважды (если используется наиболее подходящий ключ).
Эта специальная индексная структура, SumIndexField, связана с ключом. Каждый ключ может содержать максимум 20 полей SumIndexFields.
При проектировании базы данных десятичное поле может быть связано с ключом как SumIndexField. Оно инструктирует СУБД создать и обновить структуру, содержащую накопленную сумму значений в столбце. При выборе нового текущего ключа становится доступным любое поле SumIndexFields, связанное с ним.
Что Такое Вычисляемые Поля FlowFields?
Поле FlowFields – мощный инструмент системы баз данных C/SIDE. Вычисляемые поля FlowField – фундаментальная концепция, которая оказывает значительное влияние на проектирование приложения C/SIDE.
Поле FlowField и лежащая в его основе концепция суммирующих полей SumIndexFields разработаны для увеличения производительности в таких областях, как вычисление баланса клиентов. В традиционных системах баз данных это предполагает необходимость многократного получения доступа и выполнения множества вычислений, прежде чем будет получен результат. Как такой результат будет получен немедленно при использовании FlowFields, станет ясно при прочтении остальной части этого раздела.
Поле FlowFields не является постоянной частью данных таблицы. Вычисляемое поле можно представить себе в качестве виртуального поля, являющегося расширением данных таблицы. Фактически информация, содержащаяся в динамическом поле, существует только во время выполнения. Значения в вычисляемом поле автоматически инициализируются в 0 (нуль). Для обновления динамического поля необходимо использовать C/AL-функцию .CALCFIELDS. Обратите внимание, что, когда динамическое поле является прямым исходным выражением элемента управления формы, оно рассчитывается автоматически в момент отображения формы.


Типы вычисляемых полей (FlowField)
Существует семь типов динамических полей:

  • Sum  Сумма значений указанного набора записей в столбце таблицы, возвращает значение типа Decimal.
  • Average Среднее значение указанного набора записей в столбце таблицы, возвращает значение типа Decimal.
  • Exist  Показывает, существуют ли какие-либо записи в указанном наборе таблицы, возвращает значение типа Boolean.
  • Count  Число записей в указанном наборе таблицы, возвращает значение типа Integer.
  • Min  Минимальное значение в столбце в указанном наборе таблицы.
  • Max  Максимальное значение в столбце в указанном наборе таблицы.
  • Lookup Производится поиск в столбце другой таблицы.

Пример
Рассмотрим таблицу Customer представленную на следующем рисунке. Эта таблица содержит два вычисляемых поля. Поле Any Entries – вычисляемое поле типа Exist, поле Balance – вычисляемое поле типа Sum.
Вычисляемые поля в Dynamics NAV

На рисунке показано, что вычисляемое поле Balance для клиента 10000 является итогом по полю Amount операций, связанных с клиентом 10000

Sum = 10 + 20 + 30 = 60

Значения, отображаемые в полях Balance таблицы Customer для клиентов 10010, 10020, 10040 рассчитываются таким же образом. Для клиента 10030 значение 0 (ноль), так как в таблице Customer Entry нет операций для клиента 10030.
Формула расчета для поля Balance:

Sum(”Customer Entries”.Amount WHERE(CustNo=FIELD(CustNo)))

Соответственно для поля Any Entries, которое показывает, существуют ли операции для данного клиента, формула расчета будет такой:

Exist(”Customer Entries” WHERE(CustNo=FIELD(CustNo)))

Метки:



Оставьте свой отзыв!