Блог

    Блог посвящен решениям на базе платформы Jedox.
    Jedox Base - это бесплатный продукт для планирования и анализа.
    Сайт вендора: http://www.jedox.com
    Последняя версия: jedox 6 от 23.09.2015

Последние записи


Теги

Информация

Импорт данных из MS SQL в бесплатный BI Jedox

добавлено: 08 окт 15
понравилось:0
просмотров: 2515
комментов: 2

теги:

Автор: sqlolap

Итак, как и обещал, раскрою тему как при помощи бесплатного BI Jedox закачать данные из MS SQL в куб.
О самой программе Jedox. Написали немцы в 2002 г., сейчас уже существует версия 6. Как платная, так и бесплатная.
Поставить Jedox любой школьник может, заходим на сайт качаем и ставим.
В качестве источника рассмотрим демо-базу Northwind, ее легко скачать и установить. MS SQL поставил Express 2008 R2.

Картинка с другого сайта.
Все поля мне не нужны, пусть куб будет состоять из измерений:
OrderID
CompanyName
ProductName
Measure

Картинка с другого сайта.

Measure состоит из элементов UnitPrice, Quantity, Discount, вычисляемое поле Sum = Quantity X UnitPrice X (1-Discount)
Создадим эти измерения и куб. Кроме того, создадим root элементы в измерениях, чтобы новые найденные значения в указанных измерениях были автоматически добавлены и включены в иерархию.
Для подключения из Jedox в MS SQL нужно создать ODBC.

Картинка с другого сайта.

В платной версии odbc не нужен, у Jedox есть родной коннектор к MSSQL.
Запустим "Мастер импорта", выберем вариант "Запрос ODBC".

Картинка с другого сайта.

Далее выберем уже предварительно настроенный DSN и вводим запрос.
Вот тут несколько вариантов запроса.
У бесплатного Jedox особенность в том, что запись читаемых значений в куб будет идти последовательно как перезапись существующих значений.
Т.е. если в нашем примере будут 2 одинаковые строки вида
666666,SkyNet,New York, USA, T-1000, Robots, 1000, 5, 0
666666,SkyNet,New York, USA, T-1000, Robots, 2000, 6, 0
То в куб попадет последняя прочитанная запись из набора, т.е Sum = 2000 X 6 = 12000
Чтобы избежать этого, нам нужно уникальное поле OrderID.
Сам sql запрос
SELECT     dbo.[Order Details].OrderID, dbo.Customers.CompanyName, dbo.Customers.City, dbo.Customers.Country, dbo.Products.ProductName, 
dbo.Categories.CategoryName, 
                      dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity, dbo.[Order Details].Discount
FROM         dbo.Products INNER JOIN
                      dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID INNER JOIN
                      dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID INNER JOIN
                      dbo.Orders INNER JOIN
                      dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID ON dbo.[Order Details].OrderID = dbo.Orders.OrderID


Картинка с другого сайта.

Нажимаем далее и в первой строке excel появится первая строка из запроса.

Картинка с другого сайта.

Нажимаем снова далее, появится вторая строка из запроса и т.д.
Чтобы не нажимать, можно снять галку "Обновление экрана перед завершением" и нажать "Готово". Мастер переберет все строки из запроса,
подставляя результат запроса в первую строку Excel.
В первом цикле чтении строк набора нам нужно сделать, чтобы измерения пополнились новыми элементами.
Используем команду PALO.EADD (имя базы, имя измерения, имя добавляемого элемента, вес, родительский элемент, тип элемента)
На примере CompanyName команда будет PALO.EADD("localhost/example";"CompanyName";"n";$D$1;"Total";1)

Картинка с другого сайта.

По аналогии такие команды нужно ввести на листе Excel, чтобы обновить все измерения нашего куба.
После этого снова запустим импорт (SQL запрос нужно будет заново написать, мастер импорта не запоминает настройки), снимем галку
"Обновление экрана перед завершением" и нажмем "Готово".
Заходим в измерения и видим, что элементы импортировались.
Если произошла ошибка в процессе добавления элемента, то можно настроить лог или остановку процесса.
Во втором цикле чтения уже записываются значения в ячейки куба.
Для этого задействуем команду PALO.SETDATA(значение, ложь, имя базы, имя куба, имя элемента измерения1,..имя элемента измеренияN)
На примере UnitPrice команда будет PALO.SETDATA($G1;ЛОЖЬ;"localhost/Example";"Orders";$A1;$B1;$E1;"UnitPrice")

Картинка с другого сайта.

После этого снова запустим импорт, снимем галку "Обновление экрана перед завершением" и нажмем "Готово".
Полученные команды сохраним в виде excel файла с 2 листами: один для обновления измерений, второй для обновления ячеек самого куба.
Всё, можно строить ad-hoc отчеты из куба. Тормозить ничего не будет, различий по скорости отчетов и количеству пользователей м/у бесплатной
и платной версии нет.

Картинка с другого сайта.

А как же поле Sum? Сила Jedox тут и проявляется, щелкаем правой кнопкой по ячейке и выбираем "Исследовать значение ячейки"

Картинка с другого сайта.

Видно, как было рассчитано значение суммы и легко перейти в соответствующее правило куба (редактор формул Jedox).

Картинка с другого сайта.

В платной версии все гораздо удобнее, полноценный ETL:
  • Есть родной коннектор к различным СУБД, необязательно использовать ODBC.
  • Все настройки сохраняются в отличие от того же SQL запроса в окне мастера импорта
  • Есть предварительная очистка среза куба
  • Есть возможность добавлять значения к существующему в ячейке куба (т.о. не нужно измерение OrderID)
  • Есть предварительная сортировка элементов перед добавлением в измерение
  • Данные закачиваются в куб гораздо быстрее, чем обычным мастером импорта.
    Кроме того, в платной версии импорт можно поставить в Jedox планировщик заданий. Никаких bat'ников и возни со планировщиком windows не потребуется.
  • Комментарии


    • Если есть MS SQL, то есть и Analysis Services, поэтому в чем смысл использования BI Jedox?

    • MS SQL был выбран в качестве примера, подключиться можно к любому источнику через ODBC. Смысл использования в том, что Jedox бесплатный и более простой для изучения и использования.



    Необходимо войти на сайт, чтобы оставлять комментарии