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

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

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

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

• Создание запроса многомерных выражений на языке R с использованием конструкторов из пакета olapR
• Выполнение существующего, допустимого запроса многомерных выражений с использованием olapR и поставщика OLAP

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

• Запросы 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. Если у вас нет куба, получите пример куба одним из следующих способов:

• Создайте куб, который используется в этих примерах, следуя руководству Analysis Services до 4-го урока: Создание куба OLAP.

• Скачайте существующий куб в качестве резервной копии и восстановите его в экземпляр Analysis Services. Например, этот сайт предоставляет полностью обработанный куб в виде zippped format: Adventure Works Multidimensional Model SQL 2014. Извлеките файл и восстановите его в экземпляр SSAS. Дополнительные сведения см. в статье "Резервное копирование и восстановление" или"Восстановление ASDatabase".

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"

Запустите этот запрос как предопределенную строку MDX

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 Country].[Australia]

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

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

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

Перечислите кубы, доступные для указанного подключения

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

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

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

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

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")

Связанный контент

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