SQL Server Performance Forum – Threads Archive

Silly?? Question about cube connectivity

Hi, It just occured to me (maybe a silly question) that if I have my cube on the server and then I am allowing clients to connect to the cube, then how does it work given that I am not even using any ODBC connection. So basically how does the connection from a client to a cube on the server really work? I hope the question is clear.

What programme are you giving the clients to browse the cubes? Basically, at some point it will need to know the server location of the cubes – whichever programme it is. Even in Analysis Manager you have to register the AS server. And in Excel, using pivot table services, when you do "Get External Data", you have to point it to the AS server at some point. I think this information is then stored somewhere within the Excel sheet itself, kind of like a DSN-less connection. Tom Pullen
DBA, Oxfam GB

Hi Tom, Thanks for the reply. Yes via the front-end I do have to specify the name of the server and then select which cube but that’s about it… I have a feeling that you DO understand the reason for my question. Though I have been playing, actually more like STRUGGLING with this OLAP and BI stuff <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />, but it was only recently that it just occured to me that how is teh connection actually made when I don’t use any ODBC…. My front-end is Microsoft Data Analyzer and Excel. Have you used either of these front-ends. Regards.

Yes, we use Excel. I don’t really have much to do with it though, my most excellent developer set up the PTS Cubes in the Excel sheets. When he gets in to work, maybe he can enlighten us! I’m pretty sure it’s just a kind of property of the excel sheet that’s saved within it somewhere. Tom Pullen
DBA, Oxfam GB

Thanks. I would be really intersted in finding (learning) about how this "invisible" connection is actually made without using ODBC…. Maybe it simply grants rights to the defined users on the directory where aggregations are saved???

Ah no – if you’re talking about privileges and authentication .. that’s a different story! You need to set up your domain groups and grant them relevant permissions to the databases, cubes, etc. In Analysis Manager.. Right Click the db, Manage Roles… The rest, as they say, is a piece of cake. Tom Pullen
DBA, Oxfam GB

Joozh, Excel uses OLEDB for OLAP and you can choose between a non specific version and version 8.0, I have always used the one called Microsoft OLE DB provider for OLAP services 8.0. Please note that this is Excel XP. XP has a far superior version of pivot table services to Excel 2000 and on our machines here, people who need to use the OLAP reports have Office 2000 installed for their normal work and Excel XP installed on top. (N.B. it has to be on top as fixing or reinstalling Office 2000 messes up Excel XP) The connection is controlled by the PivotCache object with properties such as CommandText Connection and assorted methods e.g. Refresh The Connection is the connection string such as OLEDB<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />rovider=MSOLAP.2<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ersist Security Info=True;Data Source=SERVERNAME;Initial Catalog=OLAPDATABASE;Client Cache Size=25;Auto Synch Period=10000 where you’d replace SERVERNAME & <s>DATABASENAME</s> (actually I meant OLAPDATABASE!) with the relevant info I think that CommandText may be the cubename although I can’t quite remember You can see this information in Excel, using VBA by doing something like Sub PTinfo() MsgBox ActiveSheet.PivotTables(1).PivotCache.CommandText MsgBox ActiveSheet.PivotTables(1).PivotCache.Connection End Sub Which will pop up message boxes with the information about the cubes. I have used this reasonably effectively for management of our reports and also by using the replace function I can change the servers that the reports point at thereby relatively easily rolling out the changed reports from Development to Live. Hope this helps, Regards, Robert.

As you can see, not only is he excellent, he’s also obedient! Tom Pullen
DBA, Oxfam GB

Hi Tom, Hi Robert, I’ll only agree with the 1st part i.e. he IS really good! No comments from my side on the 2nd part since that’s between you guys <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Let me explain why I raised this silly?? question in the first place: I was thinking that since the cube users (clients) actually do not access the data stored in the datamart (in SQL server) and instead, i thinkthey actually access the data from the C

rogram FilesMicrosoft Analysis ServicesData… folder where Analysis Services saves the aggregations etc. after a cube hasbeen refreshed. So maybe there issome ‘invisible’ machanism whereby the authorized users (defined via the right click, manger roles etuff) are reading data actually from this folder. Am Imaking any sense? Ifnot, just ignore this post sinceI fear that I am unnecessarily making the topic complex. Anyway, thanks to bothofyou for your replies and to Robert, for the impressive reply <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Regards.

Yes.. there is a mechanism – and it works. Try it and only worry if it doesn’t work! Tom Pullen
DBA, Oxfam GB

Joozh, There are 3 different types of cube you can build ROLAP
HOLAP
MOLAP MOLAP writes aggregations to the Analysis Server (I think into the folder structure as you are interested in) and when the cube is queried it largely looks at this data if the aggregations have been calculated (If there isn’t an aggregation) the main data is then queried (I am not sure if this is the SQL database or just a data file on the Analysis Server. ROLAP (and I think it is this way round) only points the cubes at the SQL database and you actually query the mart SQL tables (hopefully a star schema) HOLAP does a combination of the two. I think what you are wanting to do is build your cubes as MOLAP when you design them. If you are wanting people to access the data directly in the mart then you are wanting to use ROLAP. I don’t know if ROLAP provides realtime data (we only have MOLAP cubes here) MOLAP should be quicker to query as the data is partially preaggregated. But they need processing before the data is refreshed. BOL (or is it BOLAP?!) should elaborate slightly on my rather hashed together description The permissions are all managed by the cube logins and it should be transparent to you. If MOLAP is used and a user has access to the cubes then they are being given access to the aggregated data. If ROLAP is used and the user has access to the cubes then I think you are giving them access to the subset of SQL data from your datamart. Is this what you are asking? Regards, Robert. PS And Raulie will hopefully add his value to this (and correct my misconceptions)

Hello and sorry for missing out on this topic I have been busy lately with all this cube stuff in the office so I haven’t been active on the site much. Basically the answer is strait forward like the others mentioned your clients connect through OLE DB for OLAP data provider. Data Analyzer is an off the shelf product so Im assuming it’s using the same. We use home grown front end check out this link to learn more about our solution. http://www.microsoft.com/resources/casestudies/casestudy.asp?CaseStudyID=14752 Raulie
Hewlett-Packard

Everyone thanks for the replies and assistance. I guess the answer to my question was OLE DB – which does not require the ODBC connection that we are mostly used to (i think) and that’s why I got confused…. So thanks again everyone. Raulie: Just went through the case study and your solution looks really nice and powerful. Regards.

]]>