Поделиться через

Создание запросов многомерных выражений на языке R с помощью olapR

  • Статья

Область применения: SQL Server 2016 (13.x) и более поздних версий

Пакет olapR в Службах машинного обучения SQL Server поддерживает запросы многомерных выражений к кубам, размещенным в SQL Server Analysis Services. Вы можете создавать запросы к существующему кубу, просматривать измерения и другие объекты куба, а также вставлять существующие запросы многомерных выражений для получения данных.

В этой статье описываются два основных применения пакета olapR:

Следующие операции не поддерживаются:

  • Запросы DAX к табличной модели
  • Создание новых объектов OLAP
  • Обратная запись в разделы, включая меры или суммы

Создание запроса многомерных выражений из R

  1. Определите строку подключения, указывающую источник данных OLAP (экземпляр служб SQL Server Analysis Services ) и поставщик MSOLAP.

  2. Используйте функцию OlapConnection(connectionString) , чтобы создать дескриптор для запроса многомерных выражений и передать строку подключения.

  3. Используйте конструктор Query() для создания экземпляра объекта запроса.

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

    • cube() Укажите имя базы данных SSAS. При подключении к именованному экземпляру укажите имя компьютера и имя экземпляра.

    • columns() Укажите имена мер для использования в аргументе ON COLUMNS.

    • rows() Укажите имена мер для использования в аргументе ON ROWS.

    • slicers() Укажите поле или элементы для использования в качестве среза. Срез аналогичен фильтру, который применяется ко всем данным запроса многомерных выражений.

    • axis() Укажите имя дополнительной оси для использования в запросе.

      Куб OLAP может содержать до 128 осей запроса. Обычно первые четыре оси обозначают столбцы, строки, страницы и главы.

      Если запрос относительно прост, для его создания можно использовать функции columns, rowsи т. п. Однако можно также использовать функцию axis() с ненулевым значением индекса для создания запроса многомерных выражений с несколькими квалификаторами или для добавления дополнительных измерений в качестве квалификаторов.

  5. Передайте дескриптор и готовый запрос многомерных выражений в одну из следующих функций в зависимости от вида результатов:

  • executeMD возвращает многомерный массив.
  • execute2D возвращает двухмерный (табличный) кадр данных.

Выполнение допустимого запроса многомерных выражений из R

  1. Определите строку подключения, указывающую источник данных OLAP (экземпляр служб SQL Server Analysis Services ) и поставщик MSOLAP.

  2. Используйте функцию OlapConnection(connectionString) , чтобы создать дескриптор для запроса многомерных выражений и передать строку подключения.

  3. Определите переменную R для хранения текста запроса многомерных выражений.

  4. Передайте дескриптор и переменную, содержащую запрос многомерных выражений, в функции executeMD или execute2Dв зависимости от вида результатов.

    • executeMD возвращает многомерный массив.
    • execute2D возвращает двухмерный (табличный) кадр данных.

Примеры

Следующие примеры основаны на киоске данных AdventureWorks и проекте куба, поскольку этот проект широко доступен в нескольких версиях, включая файлы резервных копий, которые можно легко восстановить в Analysis Services. Если у вас нет куба, получите пример куба одним из следующих способов:

1. Базовое многомерное выражение со срезом

Этот запрос многомерных выражений выбирает меры для количества и объема продаж через Интернет и размещает их на оси столбцов. Она добавляет элементы измерения SalesTerritory в качестве срезадля фильтрации запроса, чтобы в вычислениях использовались только данные по продажам из Австралии.

SELECT {[Measures].[Internet Sales Count], [Measures].[InternetSales-Sales Amount]} ON COLUMNS, 
{[Product].[Product Line].[Product Line].MEMBERS} ON ROWS 
FROM [Analysis Services Tutorial] 
WHERE [Sales Territory].[Sales Territory Country].[Australia]
  • В столбцах можно указать несколько мер в виде элементов строки с разделителями-запятыми.
  • Ось строк использует все возможные значения (все элементы) измерения Product Line.
  • Этот запрос вернет таблицу с тремя столбцами, содержащую сводную сводку по продажам через Интернет из всех стран или регионов.
  • Предложение WHERE определяет ось среза. В этом примере срез использует элемент измерения SalesTerritory для фильтрации запроса, чтобы в вычислениях использовались только данные по продажам из Австралии.

Создание этого запроса с помощью функций, предоставленных в olapR

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs <- OlapConnection(cnnstr)

qry <- Query()
cube(qry) <- "[Analysis Services Tutorial]"
columns(qry) <- c("[Measures].[Internet Sales Count]", "[Measures].[Internet Sales-Sales Amount]")
rows(qry) <- c("[Product].[Product Line].[Product Line].MEMBERS") 
slicers(qry) <- c("[Sales Territory].[Sales Territory Country].[Australia]")

result1 <- executeMD(ocs, qry)

Для именованного экземпляра не забудьте экранировать все символы, которые могут считаться управляющими в R. Например, следующая строка подключения ссылается на экземпляр OLAP01 на сервере с именем ContosoHQ:

cnnstr <- "Data Source=ContosoHQ\\OLAP01; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"

Выполнение этого запроса в качестве заранее определенной строки многомерного выражения

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs <- OlapConnection(cnnstr)

mdx <- "SELECT {[Measures].[Internet Sales Count], [Measures].[InternetSales-Sales Amount]} ON COLUMNS, {[Product].[Product Line].[Product Line].MEMBERS} ON ROWS FROM [Analysis Services Tutorial] WHERE [Sales Territory].[Sales Territory Country].[Australia]"

result2 <- execute2D(ocs, mdx)

Если определить запрос с помощью построителя многомерных выражений в SQL Server Management Studio, а затем сохранить строку многомерного выражения, номера осей будут начинаться с 0, как показано ниже:

SELECT {[Measures].[Internet Sales Count], [Measures].[Internet Sales-Sales Amount]} ON AXIS(0), 
   {[Product].[Product Line].[Product Line].MEMBERS} ON AXIS(1) 
   FROM [Analysis Services Tutorial] 
   WHERE [Sales Territory].[Sales Territory Countr,y].[Australia]

При этом данный запрос все равно можно выполнить в качестве заранее определенной строки многомерного выражения. Однако для создания того же запроса на языке R с помощью функции axis() нужно убедиться, что номера осей начинаются с 1.

2. Просмотр кубов и их полей в экземпляре SSAS

Вы можете использовать функцию explore , чтобы возвратить список кубов, измерений и элементов для использования при создании запроса. Это удобно, когда у вас нет доступа к другим средствам просмотра OLAP либо требуется создать запрос многомерных выражений или управлять им программно.

Вывод списка кубов, доступных в указанном подключении

Чтобы просмотреть все кубы или перспективы для экземпляра, на просмотр которого у вас есть разрешение, укажите дескриптор в качестве аргумента explore.

Внимание

Конечный результат не является кубом. Значение TRUE означает только то, что операция с метаданными выполнена успешно. Если аргументы недопустимы, возникает ошибка.

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs <- OlapConnection(cnnstr)
explore(ocs)
Результаты
Учебник по службам Analysis Services
Продажи через Интернет
Товарооборот посредников
Сводка продаж
[1] TRUE

Получение списка измерений куба

Чтобы просмотреть все измерения куба или перспективы, укажите имя куба или перспективы.

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs \<- OlapConnection(cnnstr)
explore(ocs, "Sales")
Результаты
Клиент
Дата
Регион

Возврат всех элементов заданного измерения и иерархии

После определения источника и создания дескриптора укажите куб, измерение и иерархию для возврата. Элементы в возвращаемых результатах, имеющие префикс ->, представляют потомков предыдущего элемента.

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs <- OlapConnection(cnnstr)
explore(ocs, "Analysis Services Tutorial", "Product", "Product Categories", "Category")
Результаты
Accessories
Bikes
Clothing
Компоненты
-> Компоненты сборки
-> Компоненты сборки

См. также

Использование данных из кубов OLAP на языке R