Что делать когда нужно объединить в одном запросе многомерные данные и реляционные ?
1. Чтобы воспользоваться этой возможностью, для начала необходимо создать связанный OLAP сервер.
EXEC master.dbo.sp_addlinkedserver
--имя
@server = N'FinanceOlapServer',
@srvproduct=N'',
--провайдер
@provider=N'MSOLAP',
--сервер
@datasrc=N'localhost',
--многомерная база
@catalog=N'FinanceCube_01'
Теперь воспользуемся функцией OPENQUERY которая выполняет передаваемый запрос к указанному связанному серверу.
Если возникает ошибка The 32-bit OLE DB provider "MSOLAP" cannot be loaded in-process on a 64-bit SQL Server.
Делаем по шагам.
1. сначала убираем регистрацию 32 и 64 разрядной DLL
regsvr32 /u "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"
regsvr32 /u "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"
regsvr32 /u "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"
2.Затем заново регистрируем, сначала 32 битную версию
regsvr32 "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"
regsvr32 "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"
regsvr32 "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"
3. Перезапускаем службу SQL
2. Использование OPENROWSET и OPENDATASOURCE
(это альтернативный метод для доступа к таблицам на связанном сервере)
По умолчанию в SQL сервер распределенные запросы разрешены через связанный сервер,
если необходимо делать нерегламентированные распределенные запросы, без создания связанного сервера
необходимо активировать соответствующую опцию .
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
Теперь можно делать запросы в следующем виде с указанием провайдера и строки подключения
SELECT c.*
FROM OPENROWSET('MSOLAP',
'DATASOURCE=localhost;Initial Catalog=FinanceCube_01;',
'Select [Measures].[Факт] on Columns,
[Календарь].[YEAR].allmembers on Rows
From [FINANCE]' ) c
Полный список доступных провайдеров можно посмотреть запустив процедуру
exec sp_enum_oledb_providers
Если отсутствует провайдер Microsoft.ACE.OLEDB.12.0, его можно установить по ссылке
Затем необходимо перезапустить SQL Server.
Если возникает ошибка The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied
поможет следующее, выполнить:
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO
И дать права на папку
C:\Users\<SQL Service account name>\AppData\Local\Temp
Теперь можно загружать, данные напрямую в SQL Server
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'EXCEL 12.0;Database=C:\Shared\TestExcel.xlsx;')...[sheet1$] ;
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'EXCEL 12.0;Database=C:\Shared\TestExcel.xlsx;
HDR=YES;IMEX=1','SELECT * FROM [sheet1$]');
+ Немного юмора
- Пап, я хочу заняться балетом. - Нет, Сережа, это опасно. - Почему? - Я тебе ноги переломаю.
Директор подчиненным: - Вот вы все жалуетесь на кризис, на ухудшение жизни из-за экономического положения... А, между прочим, у вас зарплата в этом году выше на 75%!!! - Простите, выше чем в каком году? - Чем в следующем...
- Не жалеешь, что замуж вышла? - Да что ж я, не человек, что ли? Жалко его, конечно.
