SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can I join a cube to a DB?


Can I join a cube to a DB?

Author
Message
krypto69
krypto69
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2642 Visits: 2480
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
Spiff
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1664 Visits: 2234
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search