Can I join a cube to a DB?

• krypto69

  SSChampion

  Points: 13591

  More actions

  July 8, 2013 at 2:02 pm

  #144426

  Hi,

  Brand new with cubes, so please excuse my ignorance.

  I have a cube that has overtime budgeted values.

  I have a SQL2005 Database that has all the 'actual' overtime values people worked.

  I am building a Dashboard in SharePoint 2013.

  So, I need to be able to show the actuals versus the budgeted overtime hours.

  I have queries for both values -

  For actuals:

  USE BPCUTIL

  SELECT [Entity]

  ,[Period]

  ,[Account]

  ,[Department]

  ,[Site]

  ,[ContractPeriod]

  ,[CPAMT]

  ,[PPAMT]

  ,[VALID]

  ,[PPX]

  ,[Recdate]

  ,[RecUser]

  ,[RecID]

  FROM [BPCUTIL].[dbo].[Load_Data_HIST]

  where account in ('5204','5202')

  order by recdate desc

  For the cube data - I captured a trace using a report that shows the budgeted overtime:

  SELECT

  NON EMPTY {[TIME].[2013.0428],[TIME].[2013.1027],[TIME].[2013.1229],[TIME].[2013.0825],[TIME].[2013.0630],[TIME].[2013.0224],[TIME].[2013.TOTAL],[TIME].[2013.1124],[TIME].[2013.0929],[TIME].[2013.0728],[TIME].[2013.0526],[TIME].[2013.0331],[TIME].[2013.0127]} ON 0,

  NON EMPTY {[CATEGORY].[BUDGET2013]} ON 1

  FROM [WSIFIN] WHERE

  ([ENTITY].[CONSOLIDATED],[RPTCURRENCY].[LC],[DATASRC].[TOTALADJ],[CONTRACTPRD].[ALL_CONTRACT_PER],[DEPARTMENT].[CONS_DEPARTMENT],[SITE].[S_CIA],[MEASURES].[PERIODIC],[ACCOUNT.H1].[OH_ALLOC_OFFSET])

  For my dashboard I need to use an SQL Query for pulling the data - Is it possible to write one query to pull from both places?

• Spiff

  SSCertifiable

  Points: 7766

  More actions

  July 18, 2013 at 3:34 am

  #1633572

  Hi Krypto,

  You can indeed. You need to create a linked server to your Analysis Services instance.

  e.g.

  EXEC sp_addlinkedserver

  @server = 'AW'

  , @srvproduct = ''

  , @provider = 'MSOLAP'

  , @datasrc = 'localhost'

  ,@catalog = 'AdventureWorks'

  Then you can query with OPENQUERY and join on this set.

  SELECT DISTINCT

  DimProduct.[EnglishProductName]

  , a.[Internet Sales Amount]

  FROM [AdventureWorksDW2012].dbo.[DimProduct]

  INNER JOIN

  (SELECT

  CAST("[Product].[Product].[Product].[MEMBER_CAPTION]" AS NVARCHAR) AS [Product Name]

  ,"[Measures].[Internet Sales Amount]" AS [Internet Sales Amount]

  FROM OPENQUERY(AW,'SELECT [Measures].[Internet Sales Amount] ON 0, NON EMPTY [Product].[Product].Children ON 1 FROM [Adventure Works]')) a

  ON DimProduct.EnglishProductName = a.[Product Name]

  Probably a really horrible example but hopefully it gives you an idea.