Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can I join a cube to a DB? Expand / Collapse
Author
Message
Posted Monday, July 8, 2013 2:02 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 7:44 AM
Points: 701, Visits: 1,731
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?




Post #1471354
Posted Thursday, July 18, 2013 3:34 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 83, Visits: 682
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.
Post #1474952
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse