Regular query with data from a cube. How? 1

[TheBugSlayer]

Hi. I always wanted to get into BI, so I studied a bit (a lot actually) but never got a chance to practice. Now that I have forgotten 90% of what I learnt I suddenly asked to produce a report...fine so far..then I found out some of the data in my resultset comes from a dimension...can' t even say it properly.

So my question is, what is the best way to join BI data to a regular query? What is the best way to go about this: export data from BI to a table then join to that table, join straight from the regular query to the dimension? I am lost. Any help is appreciated.

Thanks in advance.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[RiverGuy]

Are you saying some of the data is in SQL Server Analysis Services, and some of the data is in the SQL Server relational database?

Well, depending on the situation, the best approach might be to model the SQL data and add it to the OLAP database. Then you just write an MDX query for the whole thing.

But if you really must join the two sources together, you can look at a linked server/openquery. Most MDX queries are fast, so if you execute an MDX string against SSAS from within SQL Server, you can drop the results into a temporary table or table variable and then join that in SQL.

 

[TheBugSlayer]

Hi RiverGuy. Thanks for your prompt response.

To answer your question, yes, some data is in the relational DB and some in SSAS. Since some of the data is already in SSAS it is very likely that the rest should be added too...but too much red tape. For now the second option seems to be the most viable. I was just wondering if this was possible with OPENQUERY...I am very much looking forward to this challenge and I will certainly be back here next week posing questions.

Any other suggestions are welcome.

Thanks much!

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

Haven' t been this clueless in a long while! Struggling with the connection. Will post by EOD if I don' t get around it before.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[RiverGuy]

If this helps, this is what I have for my linked server to SSAS

EXEC master.dbo.sp_addlinkedserver @server = N'Some_Name_For_Your_Linked_Server', @srvproduct=N'blank', @provider=N'MSOLAP', @datasrc=N'your_ssas_server_name', @catalog=N'your_ssas_database_name'

 

[TheBugSlayer]

Thanks RiverGuy. I have tried adding the linked server both using SSMS and with the SP. Either way, I get this error:

OLE DB provider "MSOLAP" for linked server "OLAP_LINKED_RPTSRV00" returned message "An error was encountered in the transport layer.".
OLE DB provider "MSOLAP" for linked server "OLAP_LINKED_RPTSRV00" returned message "The peer prematurely closed the connection.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "OLAP_LINKED_RPTSRV00".

when I use

SELECT * FROM OpenQuery(OLAP_LINKED_RPTSRV00,
'SELECT 
[Client].[Main].&[XYZ] on rows,
[Measures].[Pulse] on columns
FROM [PulseABC]
WHERE [Calendar].[Date].&[2010-10-21T00:00:00]')

and

OLE DB provider "MSOLAP" for linked server "(null)" returned message "An error was encountered in the transport layer.".
OLE DB provider "MSOLAP" for linked server "(null)" returned message "The peer prematurely closed the connection.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "(null)".

when I use

SELECT a.* FROM OpenRowset('MSOLAP','DATASOURCE=RPTSRV00; Initial Catalog=AuroraAx;',
'SELECT 
[Client].[Main].&[XYZ] on rows, 
[Measures].[Pulse] on columns 
FROM [PulseABC] 
WHERE [Calendar].[Date].&[2010-10-21T00:00:00]') as a

Why is OPENROWSET making a reference to a linked server? I thought it does not require one... Not sure if this was the well documented double-hop situation, I aked the network guys who tell me that Kerberos is on and implemented by default. On my computer, in SSMS I add a linked server to the OLTP server I am connected to, then run (or try to) run the distributed query from the OLTP server...

Do you see anything wrong with my queries or the linked server configuration?

Thanks in advance.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[RiverGuy]

No, but in my situation:

A.) SQL and SSAS are on the same server
B.) I created a mapped login with the linked server from a SQL account to an AD account with cube permissions

So double-hop is not an issue for me.

 

[TheBugSlayer]

I got it. I as connecting to SSMS using my NT login. I changed my SSMS connection to use the same login that is setup in the linked server security properties. So in SSMS, the linked server uses local SQL login to map to NT remote user. On SSAS server I added the NT user.

Now I am thinking I need dedicated users (SQL Server login and NT login) for this process. It' s going to be running based on a schedule hence the need for a permanent datasource (linked server). I need to give the logins only the privileges they need. Now I need to figure those out.

Thanks for the help.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

RiverGuy, I saw your reply right after I posted mine. Now my config looks like yours and that' s why it' s running. Can you advise on the exact role the SQL account should have and which DB role has cube permissions? Does it make sense to create a domain user only for this task? I am thinking the usual admin account may have too many privileges for this task.

Thank you.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[RiverGuy]

The SQL account doesn't really matter. You just have to map it to the AD account in the linked server properties. Although you would need it to have permissions to the tables you are querying with T-SQL. I used a SQL account I use for reporting mapped to an AD account used for reporting against the cube.

 

[TheBugSlayer]

Okay. I like to create account for specific tasks but maybe not this time. I am using SA, now mapped to an AD account- I removed mine from SSAS, it was the only one. It still works. For now it will stay that way. Now I have to make sense of all this data and learn how write MDX queries.

Thank you very much.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[RiverGuy]

Oh yeah, I'd definitely not use SA or a regular user account long term. Anyways, as far as MDX is concerned, the book MDX Solutions Second Edition from Wiley publishing is a very good resource to have.

 

[TheBugSlayer]

Just wanted to let you know I got MDX Solutions Second Edition...Thanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)