InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 22:09:23 GMT20http://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspxHi Rick,sorry, I hadn't thought of that problem.I have one idea which might work. You could build your 3rd mdx in such a way that it always returns at least one record (for example by including the all member of some dimension in your WHERE-clause). Of course your final SELECT should not return this record. But this could be achieved by defining the correct ON-clause in the join.I hope this helps.Take careMartinSun, 27 Nov 2011 10:42:28 GMTMartin Cremerhttp://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspx[quote][b]Martin Cremer (11/19/2011)[/b][hr]Rick,Maybe I get your question wrong, but wouldn't it help to do a left join to the last query, so it would return the results of the first two queries even if there are no results of the third query?Hope this helpsMartin[/quote]It would be great if a left join would work, but because the field doesn't exist (the [DealerInformation].[Organisation ID].[Organisation ID].[MEMBER_CAPTION]) it still errors.Sat, 19 Nov 2011 07:40:09 GMTRick-153145http://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspxRick,Maybe I get your question wrong, but wouldn't it help to do a left join to the last query, so it would return the results of the first two queries even if there are no results of the third query?Hope this helpsMartinSat, 19 Nov 2011 05:36:44 GMTMartin Cremerhttp://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspxThanks, nice article.I have one issue of my own though and can't find a way around it. I have 3 mdx statements I am joining using openquery, setting a variable and then using sp_executesql to run it. The problem I have is when say the third join is empty, the whole thing comes back as empty. I am setting values on the fly (there are far more vatriables than posted here) and therefore can not simply execute with OPENQUERY into a #temp table and then check for existance of the columns (I am seriously starting to fall out with OPENQUERY).What I need is if there are no results from the third query, to still return the National and Regional results (there are still results) without having to re-execute the whole thing in a nested try/catch. Can anyone think of a novel way to do this as hitting major walls at the moment?[code]SET @SQL = 'SELECT Series , RegionName , OrgName , NationalTurn , CONVERT(DECIMAL(18,2),NationalPercent * 100.0000) AS NationalPercent , RegionalTurn , CONVERT(DECIMAL(18,2),RegionalPercent * 100.0000) AS RegionalPercent , DLRTurn , CONVERT(DECIMAL(18,2),DLRPercent * 100.0000) AS DLRPercent FROM ( SELECT CONVERT(VARCHAR,NationalTurn."[ModelDesc].[Series].[Series].[MEMBER_CAPTION]") AS Series , CONVERT(VARCHAR,RegionalTurn."[DealerInformation].[Region Name].[Region Name].[MEMBER_CAPTION]") AS RegionName , CONVERT(VARCHAR,DLRTurn."[DealerInformation].[Organisation Name].[Organisation Name].[MEMBER_CAPTION]") AS OrgName , CAST(CONVERT(FLOAT,NationalTurn."[Measures].[Turn(Months)]") AS DECIMAL(18,2) ) AS NationalTurn , CAST(CONVERT(FLOAT,NationalTurn."[Measures].[NationalMixPercent]") AS DECIMAL(18,5) ) AS NationalPercent , CAST(CONVERT(FLOAT,RegionalTurn."[Measures].[Turn(Months)]") AS DECIMAL(18,2) ) AS RegionalTurn , CAST(CONVERT(FLOAT,RegionalTurn."[Measures].[RegionalMixPercent]") AS DECIMAL(18,5) ) AS RegionalPercent , CAST(CONVERT(FLOAT,DLRTurn."[Measures].[Turn(Months)]") AS DECIMAL(18,2) ) AS DLRTurn , CAST(CONVERT(FLOAT,DLRTurn."[Measures].[DLRMixPercent]") AS DECIMAL(18,5) ) AS DLRPercent FROM OPENQUERY(MSOLAPSRV,''SELECT { [Measures].[Turn(Months)] , [Measures].[NationalMixPercent] } ON COLUMNS , NONEMPTY( { ([ModelDesc].[Series].[' + @Series + '] ) }, { [Measures].[Turn(Months)] , [Measures].[NationalMixPercent] }) ON ROWS FROM [cube] WHERE ([StockApplication].[PWD App ID].[' + @AppID + ']) '' ) NationalTurn INNER JOIN OPENQUERY(MSOLAPSRV,''SELECT { [Measures].[Turn(Months)] , [Measures].[RegionalMixPercent] } ON COLUMNS , NONEMPTY( { ([ModelDesc].[Series].[' + @Series + '] , [DealerInformation].[Region Name].[' + @Region + ']) } , { [Measures].[Turn(Months)] , [Measures].[RegionalMixPercent] }) ON ROWS FROM [cube] WHERE ([StockApplication].[PWD App ID].[' + @AppID + ']) '' ) RegionalTurn ON CONVERT(VARCHAR,NationalTurn."[ModelDesc].[Series].[Series].[MEMBER_CAPTION]") = CONVERT(VARCHAR,RegionalTurn."[ModelDesc].[Series].[Series].[MEMBER_CAPTION]") INNER JOIN OPENQUERY(MSOLAPSRV,''SELECT { [Measures].[Turn(Months)] , [Measures].[DLRMixPercent] } ON COLUMNS , NONEMPTY( { ([ModelDesc].[Series].[' + @Series + '] , [DealerInformation].[Region Name].[' + @Region + '] , [DealerInformation].[Organisation ID].[' + @Dealer + '] )}, { [Measures].[Turn(Months)] , [Measures].[DLRMixPercent] }) ON ROWS FROM [cube] WHERE ([StockApplication].[PWD App ID].[' + @AppID + ']) '' ) DLRTurn ON CONVERT(VARCHAR,NationalTurn."[ModelDesc].[Series].[Series].[MEMBER_CAPTION]") = CONVERT(VARCHAR,DLRTurn."[ModelDesc].[Series].[Series].[MEMBER_CAPTION]") AND CONVERT(VARCHAR,RegionalTurn."[DealerInformation].[Region Name].[Region Name].[MEMBER_CAPTION]") = CONVERT(VARCHAR,DLRTurn."[DealerInformation].[Region Name].[Region Name].[MEMBER_CAPTION]") ) tblCube' EXEC sp_executesql @SQL[/code]Fri, 18 Nov 2011 06:24:51 GMTRick-153145http://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspxHi Ron, you can use the approach shown in this article to create a report which gets its content from more than one cubes:For example you have one mdx which retrieves the number of invoices per customer. You can write the results into a temp-table with the technique shown in the article.Then you have another mdx from another cube (which then is another linked server) which gets the number of visits on your homepage per customer. Write this into another temp-table.Then you can (inner/outer) join these tables as you like (now we are in normal SQL environment again, where we can do whatever we like with the data).If the cubes are in the same database, have a look at the lookupcube-mdx function (s. [url=http://msdn.microsoft.com/en-us/library/ms144720.aspx]http://msdn.microsoft.com/en-us/library/ms144720.aspx[/url]). This also could solve your problem.Hope that helped.MartinTue, 26 Apr 2011 14:51:41 GMTMartin Cremerhttp://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspxAssuming that I have multiple cubes I am reading from SQL Server Analysis Services.All my cubes have the same names.Is the way I can create a report that spans across multiple cubes or merge all the cubes' calculations.Thanks,RonTue, 26 Apr 2011 09:38:39 GMTejaluronaldleehttp://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspxThis article is excellent.Thank you so much,RonaldleeTue, 26 Apr 2011 09:33:01 GMTejaluronaldleehttp://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspxHi all,I am also facing similar issue when i move the linked server script from Developement enciroment to uat it creates linked server . When i tyrto test the connection it says ""Cannot create an instance of OLE DB provider "MSOLAP" for linked server Cube".Please helpme with the same is it a security issueFri, 17 Jul 2009 00:11:15 GMTramu4ind-1123836http://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspxHi Martin,As i posted here [url=http://www.sqlservercentral.com/Forums/Topic732835-149-1.aspx]Problem with linked server[/url],I have problem with the linked server, it work fine but from time to time I get this error"Cannot create an instance of OLE DB provider "MSOLAP" for linked server "SALES_CUBE". (Microsoft SQL Server, Error: 7302)"restarting the SQL fix the problem' but this times i have to restart SQL on a daily base.Any advice?KupyThu, 11 Jun 2009 05:07:04 GMTkupyhttp://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspxI had it configured with the radio button set to "Be made using the login's current security context" but after setting it to "Be made using this security context" the connection succeeded. After digging into the issue it looks like I have underlying configuration problems with Kerberos and SPN's that I need to work on. ThanksWed, 22 Oct 2008 07:39:39 GMTBen Holcombe-270296http://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspxHi Ben,how did you configure the Security tab of the Linked Server Properties?I attached a screen shot where I erased the login names.In the red area you can assign a remote user (that is the user who logs on to Analysis Services) to a local user (the login used for the SQL Server database).In the blue area you can use a standard login for SSAS. I would only use this for testing purposes because that would mean that everybody, who can connect to SQL Server database, can connect to SSAS as well.In both cases you have to specify the Windows account (as Domain\Username), which should log on to SSAS, in the remote user/login-field.For your problem, I would try the "blue" thing. If you can connect to SSAS, you know your problem is security-related.I hope this was of any help.Take care,MartinFri, 17 Oct 2008 13:25:45 GMTMartin Cremerhttp://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspxAfter more research I am editing my postThis is actually looking to be more like a double hop authentication issue than anykind of 64bit incompatiability. [size="1"]This example is great one and solves several challenges that I am facing. I am having one technical problem when implementing the Linked Server. It appears that there is a problem with the 64-bit version of the Microsoft OLE DB Provider for Analysis Services 9.0 provider when you attempt to query the linked server from a 32 bit instance like a client machine. I can successfully run queries from a 64bit machine but a 32 bit results in an error. [/size]Has anyone experienced this similar behavior and hopefully found a solution?Mon, 06 Oct 2008 13:51:10 GMTBen Holcombe-270296http://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspxGreat article....Sun, 21 Sep 2008 23:20:31 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspxThanks for providing some examples of things that didn't work and why, particularly the part about issues when you went from your local server to a production server. I wish I had a dollar for every time something worked great locally and then fell apart in production. I have spent countless hours trying to chase these down only to find out that I just needed to check the right box. Your explanation will save a lot of people like me many hours of research.I also liked how you worked in some excellent points with regard to the proper way to use a table variable versus a temp table versus a "real" table.Great article.Wed, 17 Sep 2008 07:23:23 GMTRobert Frascahttp://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspxGreat work, Martin!Wed, 17 Sep 2008 06:32:37 GMTredact207http://www.sqlservercentral.com/Forums/Topic570765-1347-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Linked+Server/63867/">Reporting Services: Read Data from SSAS and SQL Server in One Dataset</A>[/B]Wed, 17 Sep 2008 00:44:14 GMTMartin Cremer