Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Параметризированные запросы к Linked Server и JOIN результатов этих запросов.  [new]
McCar
Member

Откуда: Саратов
Сообщений: 778
MS SQL 2012.

Задача:
Свести в одной таблице в RS отчете данные из двух олапных SSAS баз.
До сих пор такого рода требования реализовывались через RS функцию Lookup() - которая позволяет "джойнить" данные из двух RS датасетов на стороне Reporting Services.
Вопрос: можно ли это сделать стороне MS SQL через Linked Server?
Мы можем заджойнить два OpenQuery(...) запроса, но в моем случае в в каждый из MDX запросов нужно передавать параметры, чего, насколько я понимаю, в запросе к Linked Server -у мы сделать не можем.
Как еще?
Формировать запросы динамически и выполнять через Exec? Есть примеры?
Спасибо.
27 окт 14, 11:56    [16762800]     Ответить | Цитировать Сообщить модератору
 Re: Параметризированные запросы к Linked Server и JOIN результатов этих запросов.  [new]
Glory
Member

Откуда:
Сообщений: 104751
McCar
Формировать запросы динамически и выполнять через Exec? Есть примеры?

https://www.sql.ru/faq/faq_topic.aspx?fid=104
27 окт 14, 11:57    [16762808]     Ответить | Цитировать Сообщить модератору
 Re: Параметризированные запросы к Linked Server и JOIN результатов этих запросов.  [new]
McCar
Member

Откуда: Саратов
Сообщений: 778
Glory
McCar
Формировать запросы динамически и выполнять через Exec? Есть примеры?

https://www.sql.ru/faq/faq_topic.aspx?fid=104

Ну это понятно.
У меня там получится более извращенный случай, сформировать динамически запрос, в котором джойнятся два OpenQuery запроса.
Перед тем как начать ковыряться, решил уточнить, - нет ли каких то путей попроще.
27 окт 14, 12:23    [16762966]     Ответить | Цитировать Сообщить модератору
 Re: Параметризированные запросы к Linked Server и JOIN результатов этих запросов.  [new]
Glory
Member

Откуда:
Сообщений: 104751
McCar
У меня там получится более извращенный случай, сформировать динамически запрос, в котором джойнятся два OpenQuery запроса.

Да хоть 100 OpenQuery. Нет никакой разницы.


McCar
Перед тем как начать ковыряться, решил уточнить, - нет ли каких то путей попроще.

Вы опеределитесь с задачей
Для соединения двух запросов динамика не нужна
27 окт 14, 12:25    [16762981]     Ответить | Цитировать Сообщить модератору
 Re: Параметризированные запросы к Linked Server и JOIN результатов этих запросов.  [new]
McCar
Member

Откуда: Саратов
Сообщений: 778
Glory
McCar
У меня там получится более извращенный случай, сформировать динамически запрос, в котором джойнятся два OpenQuery запроса.

Да хоть 100 OpenQuery. Нет никакой разницы.


McCar
Перед тем как начать ковыряться, решил уточнить, - нет ли каких то путей попроще.

Вы опеределитесь с задачей
Для соединения двух запросов динамика не нужна

Так там параметризировнные запросы.
Если бы не это, тогда конечно, а так приходится динамически формировать строку с нужными параметрами.
27 окт 14, 12:30    [16763022]     Ответить | Цитировать Сообщить модератору
 Re: Параметризированные запросы к Linked Server и JOIN результатов этих запросов.  [new]
Glory
Member

Откуда:
Сообщений: 104751
McCar
Так там параметризировнные запросы.
Если бы не это, тогда конечно, а так приходится динамически формировать строку с нужными параметрами.

Ну так формируйте. В чем проблема то ?
27 окт 14, 12:32    [16763027]     Ответить | Цитировать Сообщить модератору
 Re: Параметризированные запросы к Linked Server и JOIN результатов этих запросов.  [new]
McCar
Member

Откуда: Саратов
Сообщений: 778
Glory
McCar
Так там параметризировнные запросы.
Если бы не это, тогда конечно, а так приходится динамически формировать строку с нужными параметрами.

Ну так формируйте. В чем проблема то ?

Проблемы нет.
Просто хотел уточнить - как проще.
Лепить громоздкий динамический запрос с джойном или есть еще варианты.
Например - выполнить в хранимке два exec с динамическим SQL c записью в табличную переменную и затем уже джойнить то, что в их запишется.
27 окт 14, 12:47    [16763087]     Ответить | Цитировать Сообщить модератору
 Re: Параметризированные запросы к Linked Server и JOIN результатов этих запросов.  [new]
Glory
Member

Откуда:
Сообщений: 104751
McCar
выполнить в хранимке два exec с динамическим SQL

Т.е. вместо одного динамического запроса составить и выполнить два ???
Ну тогда лучше 3 или 4.
27 окт 14, 12:49    [16763094]     Ответить | Цитировать Сообщить модератору
 Re: Параметризированные запросы к Linked Server и JOIN результатов этих запросов.  [new]
McCar
Member

Откуда: Саратов
Сообщений: 778
Glory
McCar
выполнить в хранимке два exec с динамическим SQL

Т.е. вместо одного динамического запроса составить и выполнить два ???
Ну тогда лучше 3 или 4.

Вот, монстуозно оно получилось, но может кому пригодиться или кто предложит вариант попроще :
+

alter  PROCEDURE SP_GET_EL_BOARD_REP_SUMS (  @pDateScopeType int  /*1 - год, 2 - месяцы, 4- дни, 8 - аггрегатный суммы "по текущ дату",  to do - можно с битовыми масками для универсальности заморочиться*/)
as 
begin
declare @AllData table (DateKey varchar(100), [DateCaption] nvarchar(100),[DateLevel] int not null,   [YEAR] int not null,  OwnerType nvarchar(20) not null,  OrdKitchType nvarchar(20) not null, SumDiscount  decimal(18,6))
declare @mdx nvarchar(max); declare @SQL nvarchar(max);declare @DataSourceName nvarchar(max) ; 
declare @MetadataYearName nvarchar(100),  @MetadataOrdKithcTypeName nvarchar(100),  @MetadataOwnerTypeName nvarchar(100);
declare @DataSourceNumber int =1;

while @DataSourceNumber<=2
begin 
set @DataSourceName= case when @DataSourceNumber=1 then 'SSAS-ORDERS' else 'SSAS-OrdersTHR4' end 
set @mdx= case when @DataSourceNumber=1 then  '
with 
Member pDateScopeType as      @pDateScopeType /*1 - год, 2 - месяцы, 4- дни, 8 - аггрегатный суммы "по текущ дату",  to do - можно с битовыми масками для универсальности заморочиться*/
set [Today] as  Exists([DimDateRP].[h-Year-Month-Date].[DATE], [DimDateRP].[IS_CURRENT_DATE].&[1])
set [YesterDay] as   [Today](0).Lag(1) 
set [repMonth] as  [YesterDay](0).Parent
set [repYear] as  [repMonth](0).Parent
set [todayLastYear] as PARALLELPERIOD( [DimDateRP].[h-Year-Month-Date].[Year],1,[YesterDay](0) )
set [sThisMonthUpToToday] as [YesterDay](0).Parent.FirstChild:[YesterDay](0)
set [sLastYearMonthUpToToday] as [todayLastYear](0).Parent.FirstChild:[todayLastYear](0)
set [sThisYearUpToDate] as [today](0).Parent.Parent.FirstChild.FirstChild:[YesterDay](0)
set [sLastYearUpToToday] as [todayLastYear](0).Parent.Parent.FirstChild.FirstChild:[todayLastYear](0)
--
Member [DimDateRP].[h-Year-Month-Date].[ThisYearMonthUpToToday] as aggregate([sThisMonthUpToToday] ), Caption=''За этот месяц  до текущ даты''
Member [DimDateRP].[h-Year-Month-Date].[LastYearMonthUpToToday] as aggregate([sLastYearMonthUpToToday]), Caption=''За этот месяц прошлого года до текущ даты''
Member [DimDateRP].[h-Year-Month-Date].[LastYearUpToToday] as aggregate([sLastYearUpToToday]), Caption=''За прошлый год до текущ даты''
Member [DimDateRP].[h-Year-Month-Date].[ThisYearUpToToday] as aggregate([sThisYearUpToDate]), Caption=''За этот год до текущ даты''
set [sCalcDates] as  {[DimDateRP].[h-Year-Month-Date].[ThisYearMonthUpToToday],[DimDateRP].[h-Year-Month-Date].[LastYearMonthUpToToday],[DimDateRP].[h-Year-Month-Date].[ThisYearUpToToday],[DimDateRP].[h-Year-Month-Date].[LastYearUpToToday]}
member qcmIsNotFuture as case when (Exists( [DimDateRP].[h-Year-Month-Date],  {null:[YesterDay](0)})).Count>0 then 1 else null  end 
Member  qcmMonthsAgo  as  case when Cint(pDateScopeType)=2 then  Count({[DimDateRP].[h-Year-Month-Date].CurrentMember:[repMonth](0)})-1 else null end /*месяцев назад*/

set [sRepDates] as
CASE 
when Cint(pDateScopeType)=1 then {[DimDateRP].[h-Year-Month-Date].[Year].&[2009]:[repMonth](0).Parent}
when Cint(pDateScopeType)=2 then [repMonth](0).Parent.Lag(1).Children+[repMonth](0).Parent.Children
when Cint(pDateScopeType)=4 then [repMonth](0).Children
when Cint(pDateScopeType)=8 then [sCalcDates]
end 
member qcmDateLevel as  [DimDateRP].[h-Year-Month-Date].Level.Ordinal
Member qcmDateKey as [DimDateRP].[h-Year-Month-Date].Member_key
Member qcmDateCaption as [DimDateRP].[h-Year-Month-Date].Member_caption
Member qcmIsRepMonth as  case when [DimDateRP].[Month].Member_key  =[repMonth](0).Member_key then 1 else 0 end 
 member [DimGoods].[GoodsTree].[NonRoyalty] as [DimGoods].[GoodsTree].[All]  -[DimGoods].[GoodsTree].&[134036190]
set [sOwnerType] as {[DimShopsForParentOrder].[OwnerType].&[8131439] /*торговый дом*/,[DimShopsForParentOrder].[OwnerType].&[8131442] /*Дилер*/,[DimShopsForParentOrder].[OwnerType].&[152457771]/*DIE*/,[DimShopsForParentOrder].[OwnerType].&[152457602]/*B2B*/}

Member  [DimOrder].[OrdKitchType].[qmKitch] as  Aggregate({[DimOrder].[OrdKitchType].&[1],[DimOrder].[OrdKitchType].&[2]},[DimOrder].[ORDER_TYPE_PARENT].&[12]), Caption=''Кухни''
Member  [DimOrder].[OrdKitchType].[qmBatrRooms] as  Aggregate({[DimOrder].[OrdKitchType].&[1],[DimOrder].[OrdKitchType].&[2]},[DimOrder].[ORDER_TYPE_PARENT].&[45307498]), Caption=''Ванные''
Member  [DimOrder].[OrdKitchType].[qmSeparateOrders] AS aggregate([DimOrder].[OrdKitchType].[OrdKitchType]- {[DimOrder].[OrdKitchType].[1],[DimOrder].[OrdKitchType].&[2]}) , Caption =''Отдельные заказы''
set [sKithType] as { [DimOrder].[OrdKitchType].[qmKitch] , [DimOrder].[OrdKitchType].[qmBatrRooms],[DimOrder].[OrdKitchType].[qmSeparateOrders]}
Member qcmSumDisCount as    ([SUM DISCOUNT]/1.18)  

SELECT {qcmDateKey,qcmDateCaption,qcmDateLevel, qcmIsRepMonth, qcmMonthsAgo,qcmSumDisCount
} ON 0 ,
[sRepDates]
*sOwnerType
*[sKithType]
--  DIMENSION PROPERTIES Member_, [DimDateRP].[h-Year-Month-Date].[Month].[MonthInYear]
 ON 1  
FROM [ORDERS_CUBE]   WHERE 
([DimDateRP].[DATE_TYPE].&[5] /*Дата продажи*/,
[DimOrder].[PROFIT_TYPE].&[122853039]
,--[DimOrder].[RECLAMATION_TYPE].&[0],
 [DimOrderRow].[IS_FROM_ORDER_COMP].&[0]
,[DimGoods].[GoodsTree].[NonRoyalty])
' 
else /*Запрос для THR Данных*/
'
With 
Member pDateScopeType as     @pDateScopeType /*1 - год, 2 - месяцы, 4- дни,  to do - можно с битовыми масками для универсальности заморочиться*/
MEMBER [DimSupplyContract].[Состояние доставки].[csDeliverStateConditions] AS ''Aggregate({[DimSupplyContract].[Состояние доставки].[All].UNKNOWNMEMBER,[DimSupplyContract].[Состояние доставки].&[3],[DimSupplyContract].[Состояние доставки].&[0]})'' 
MEMBER [DimContact].[Корпоративный Клиент].[csCorpClCondition] AS ''Aggregate({[DimContact].[Корпоративный Клиент].[Корпоративный Клиент]-[DimContact].[Корпоративный Клиент].&[2]/*все кроме оби*/})''
MEMBER [CURRENT STATE].[DOC STATES].[csCurrentStateCondition] AS ''Aggregate({[CURRENT STATE].[DOC STATES].[DOC STATES]-[CURRENT STATE].[DOC STATES].&[2]/*все кроме отменен*/})''
MEMBER [DimSupplyContract].[OrderType].[qmKitch] AS ''Aggregate({[DimSupplyContract].[OrderType].[All].UNKNOWNMEMBER,[DimSupplyContract].[OrderType].&[97],[DimSupplyContract].[OrderType].&[967189],[DimSupplyContract].[OrderType].&[935234],[DimSupplyContract].[OrderType].&[935233],[DimSupplyContract].[OrderType].&[935232],[DimSupplyContract].[OrderType].&[935231],[DimSupplyContract].[OrderType].&[88],[DimSupplyContract].[OrderType].&[85],[DimSupplyContract].[OrderType].&[82],[DimSupplyContract].[OrderType].&[81],[DimSupplyContract].[OrderType].&[6931376],[DimSupplyContract].[OrderType].&[3315696],[DimSupplyContract].[OrderType].&[1058174]})'' , Caption=''Кухни''
MEMBER [DimSupplyContract].[OrderType].[qmBathRooms] AS ''Aggregate({[DimSupplyContract].[OrderType].&[89],[DimSupplyContract].[OrderType].&[84]})'', Caption=''Ванные'' 
--
set [today] as     [sTodayByFIRST_CRIT_DATE_APPEARED] 
set [sDateEnd] as [today] 
set [sRepMonth] as  [sDateEnd](0).Parent
set [sRepDates] as
CASE 
when Cint(pDateScopeType)=1 then {[FIRST_CRIT_DATE_APPEARED].[Г-М-Д].[Year].&[2009]:[sRepMonth](0).Parent}
when Cint(pDateScopeType)=2 then [sRepMonth](0).Parent.Lag(1).Children+[sRepMonth](0).Parent.Children
when Cint(pDateScopeType)=4 then [sRepMonth](0).Children
end 

Member qcmDateLevel as   [FIRST_CRIT_DATE_APPEARED].[Г-М-Д].Level.Ordinal 
Member qcmDateKey as case when qcmDateLevel=3 then [FIRST_CRIT_DATE_APPEARED].[Г-М-Д].Member_value  else [FIRST_CRIT_DATE_APPEARED].[Г-М-Д].Member_key end
Member qcmDateCaption as [FIRST_CRIT_DATE_APPEARED].[Г-М-Д].Member_caption

Member qcmSumDisCount as    ([ITEMS_SUM])  
Member qcmSP_DC_for_kitchens as    [SP_DC_for_kitchens]
SELECT
{qcmDateKey,qcmDateCaption,qcmDateLevel,qcmSumDisCount,qcmSP_DC_for_kitchens} on 0,
[sRepDates]
 * {[DimSupplyContract].[OrderType].[qmKitch], [DimSupplyContract].[OrderType].[qmBathRooms]}
 ON 1  
  FROM [THR4_Supply_Contracts]
  WHERE (
  [IsInSale].[YesOrNo].&[1]
,[DimSupplyContract].[Состояние доставки].[csDeliverStateConditions]
,[DimContact].[Корпоративный Клиент].[csCorpClCondition]
,[CURRENT STATE].[DOC STATES].[csCurrentStateCondition]
 ) 
' 
end ;
set @mdx=replace(@mdx,'@pDateScopeType', cast(@pDateScopeType as varchar(2))); /*параметризация*/
set @mdx=Replace(@mdx,'''', ''''''); /*кавычки заэскепить*/
set @MetadataYearName= case when @DataSourceNumber=1  then '[DimDateRP].[h-Year-Month-Date].[Year].[MEMBER_CAPTION]'  else '[FIRST_CRIT_DATE_APPEARED].[Г-М-Д].[Year].[MEMBER_CAPTION]' end ;
set @MetadataOrdKithcTypeName =case when @DataSourceNumber=1 then '[DimOrder].[OrdKitchType].[OrdKitchType].[MEMBER_CAPTION]' else '[DimSupplyContract].[OrderType].[OrderType].[MEMBER_CAPTION]' end 
set @MetadataOwnerTypeName =case when @DataSourceNumber=1  then '"[DimShopsForParentOrder].[OwnerType].[OwnerType].[MEMBER_CAPTION]"' else '''ТД-Клиенту''' end;
set @sql='select CAST("[Measures].[qcmDateKey]" as int) qcmDateKey,
"[Measures].[qcmDateCaption]",	
cast("[Measures].[qcmDateLevel]" as int) qcmDateLevel,	
cast(cast("'+@MetadataYearName+'" as char(4)) as  int) [Year],
'+@MetadataOwnerTypeName+',
"'+@MetadataOrdKithcTypeName+'" OwnerType,	
cast("[Measures].[qcmSumDisCount]" as decimal(18,6))/1000000 qcmSumDisCount
from openquery(['+@DataSourceName+'],'''+@mdx+''')';
INSERT INTO @AllData exec(  @sql) ;
set @DataSourceNumber=@DataSourceNumber+1;
end

select mrd.* from @AllData mrd --where SumDiscount is null
end 

--exec  SP_GET_EL_BOARD_REP_SUMS 2



Сообщение было отредактировано: 28 окт 14, 18:46
28 окт 14, 18:41    [16769132]     Ответить | Цитировать Сообщить модератору
 Re: Параметризированные запросы к Linked Server и JOIN результатов этих запросов.  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
McCar
MS SQL 2012.

Задача:
Свести в одной таблице в RS отчете данные из двух олапных SSAS баз.
До сих пор такого рода требования реализовывались через RS функцию Lookup() - которая позволяет "джойнить" данные из двух RS датасетов на стороне Reporting Services.
Вопрос: можно ли это сделать стороне MS SQL через Linked Server?
Мы можем заджойнить два OpenQuery(...) запроса, но в моем случае в в каждый из MDX запросов нужно передавать параметры, чего, насколько я понимаю, в запросе к Linked Server -у мы сделать не можем.
Как еще?
Формировать запросы динамически и выполнять через Exec? Есть примеры?
Спасибо.


Я рекомендую вам исполнять такие запросы через CLR функцию, так как MDX не гарантирует стабильный набор колонок. Принимает на вход запрос и список, колонок, которые при отсутствии колонки заполняются NULL. Также OPENQUERY имеет ограничение на длину запроса, а MDX-ы могут быть длинными. Если это отчёт и результат нужен на лету, то лучше поступить так.

Если вы готовы предаггрегировать запросы и сохранять результат, то я рекомендую вам использовать SSIS.
28 окт 14, 19:10    [16769210]     Ответить | Цитировать Сообщить модератору
 Re: Параметризированные запросы к Linked Server и JOIN результатов этих запросов.  [new]
McCar
Member

Откуда: Саратов
Сообщений: 778
[quot a_voronin]

Я рекомендую вам исполнять такие запросы через CLR функцию, так как MDX не гарантирует стабильный набор колонок. Принимает на вход запрос и список, колонок, которые при отсутствии колонки заполняются NULL. Также OPENQUERY имеет ограничение на длину запроса, а MDX-ы могут быть длинными. Если это отчёт и результат нужен на лету, то лучше поступить так.
[quot a_voronin]
Есть примеры кода? Поделитесь?

a_voronin
Если вы готовы предаггрегировать запросы и сохранять результат, то я рекомендую вам использовать SSIS.

Для каждого отчета держать свою табличку с пред-агрегированными данными - пожалуй не готов.
29 окт 14, 09:04    [16770819]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить