SQLServerCentral / Discuss Content Posted by Vincent Rainardi / Article Discussions / Article Discussions by Author / The Unpopular SELECT Statement / Latest Posts

RE: The Unpopular SELECT Statement

VincentRainardi — Fri, 30 Jan 2009 17:31:45 GMT

All, thank you for all your comments. Sanjarani, for learning SSAS my book is not the right one. My book is about building a data warehouse and BI, so the coverage on SSAS is very limited. To learn SSAS, it is better to read 2008 books rather than 2005 ones, because of the improvements in SSAS 2008. Irina Gorbach, Edward Melomed and Alexander Berger are reknown expert in SSAS and I'm grateful that they spared their time writing a good SSAS 2008 book (0672330016), which I enjoyed a lot. Another good SSAS book is Sivakumar Harinath and Robert Zare's (0470247983). Although it is in 2005, Teo Lachev's book (0976635305) is extremely structured, and will be a great help to anybody starting SSAS journey.vchintapanti, to automatically process (refresh) the cube when new data is added in the data warehouse, we can either use a ROLAP partition, or use ProActive Caching. http://msdn.microsoft.com/en-us/library/ms174769.aspx explains the considerations.

RE: The Unpopular SELECT Statement

Alan Vogan — Fri, 30 Jan 2009 10:36:20 GMT

Vincent, Nice Article :)And an excellent discussion posting. We often put on our 'Analyst Hat' and dive into things before we've put on our 'Systems' or 'DBA' hat to actually get a project set-up. I know I often find my self going back, saying to myself 'Whoops, forgot to set that up!, Whoops, need SP2 update 6.' etc. etc. etc. That's why we're not brain surgeon's... :w00t:All good notes for you for the next edition ;)peace,Alan

RE: The Unpopular SELECT Statement

vchintapanti — Fri, 30 Jan 2009 10:29:17 GMT

How to AUTOMATE the process and deployment of the CUBE when new data added to the FACT and DIMENTION table.

RE: The Unpopular SELECT Statement

sanjarani — Fri, 30 Jan 2009 09:21:05 GMT

I enjoyed reading this article, thank you. I have been using SQL Server 2005 for a couple of years now, in particular SSIS and SSRS. However, I have not used SSAS so far, because the work that I have been involved in did not require the use of SSAS. If I were to start learning SSAS, would your book be a good starting point or is it too advanced for a beginer? I don't know the MDX language, but am a very confident T-SQL developer.By the way, unlike some of the readers who could not download the database and restore it, I restored the database and viewed and queried the cube as per your instructions with no difficulties.

RE: The Unpopular SELECT Statement

Half Bubble — Fri, 30 Jan 2009 08:50:08 GMT

Who ate the figures?All I get is blue boxes :crying:

RE: The Unpopular SELECT Statement

peter-757102 — Fri, 30 Jan 2009 06:42:49 GMT

A nice basic introduction!I myself have wrestled with AS2005 in practice and seeing how easy others run into problems given all the post here are about just that, I get heavy Deja Vu. Some things are good but from a modeling, querying and maintainability point I never accepted AS2005 as a finished product (not even as beta in fact).All I can say is that if you are interested in digging deeper into multi-dimensional modeling, be prepared to have major setbacks on anything but the most basic examples. Spacial thinking and reasoning in 5 dimensions (so along 5 axis) and more is pretty straightforward if you know multi-dimensional arrays from programming or have done complex queries in SQL within a star diagram.After all you don't need to visualise the dimensions to understand them and you don't need to perform rotations and other complex geometry within that multi-dimensional space to collect meaningful data. That would realy be impossible for all but maybe 2 humans on this planet that can deal with 5 dimensions without computerised visualisation assistance.The often untold truth is that AS works nothing like all examples let you think. Also prepare for the case that a lot of information you read is likely to contain mistakes or has contradictions with other sources of information and instead of help they can just as easily put you on the wrong track.At least this is what I found a few years back when I had a solution that worked well in SQL and I understood in full. The project was to move all the data into AS2005 so reporting would become easyer for the customer as they could use their own tools instead of using our application. The story ended with sticking to SQL for reporting as having user readable reports contradicted with the requirement of querying the same data via MDX to get application interpretable table (2D) results.I won't spoil the exercise, just make sure you understand every step of the way and do heavy testing. Query results can become quite deceiving in that things seem to work, but turn out not to under certain conditions. Logic the way you are used to in SQL does not apply when it comes to MDX!I am bound to get some flak over this by AS folks, but even people I asked that *know* had to guess a lot of the time. They just try it in a data browser and if the answer seems ok, they accept it as working...not good enaugh for me I am afraid.

RE: The Unpopular SELECT Statement

s_bohari — Wed, 20 Aug 2008 09:34:20 GMT

Hi Vincent,I found the solution to my problem on another website. I needed to check that SQL Server 2005 SP2 is installed on the source/target server, if not install it.I tried your solution before installing the SP2, but during execution it failed.Thank you for your help.Kind regards

RE: The Unpopular SELECT Statement

VincentRainardi — Wed, 20 Aug 2008 04:42:36 GMT

Hi, try scripting the AS database on the old AS server to XMLA then run the XMLA script on the new AS Server to create the AS DB. Change the data source if necessary (to point to the new relational engine), process the AS database and browse it.HTH,Vincent

RE: The Unpopular SELECT Statement

s_bohari — Wed, 20 Aug 2008 03:14:52 GMT

Hi guys,I have a similar problem as Steven, when it comes to restoring a OLAP database. I'm trying to restore the database to another computer then the one I toke the backup from. The error message is similar to this one:"The ddl2:MemberKeyUniqueElement at line 97, column 10600 (namespace http://schemas.microsoft.com/analysisservices/2003/engine/2) cannot appear under Load/ObjectDefinition/Dimension/Hierarchies/Hierarchy.Errors in the metadata manager. An error occurred when instantiating a metadata object from the file, '\\?\c:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Sales2.0.db\Dim Date.1.dim.xml'.Errors in the metadata manager. An error occurred when loading the Sales cube, from the file, '\\?\c:\Program Files\Microsoft SQL ServeMSSQL.2\OLAP\Data\Sales2.0.db\Sales.2.cub.xml'. (Microsoft.AnalysisServices)"The dimensions and cube is not the same, obviously.Anyone got a solution to the problem? I might have to add that this orginally was a totally SQL 2008 project (SSMS & BIDS 2008), which I'm running on a SQL 2005 database engine with BIDS 2008.Thankful for all possible help!Regards,

RE: The Unpopular SELECT Statement

neomal — Mon, 25 Feb 2008 22:08:06 GMT

This is a nice article... powered by hands on experience.... it helps to get abasic idea about some data werehousing aspects...Credit for the author

RE: The Unpopular SELECT Statement

Steven Harris-463201 — Fri, 22 Feb 2008 14:27:40 GMT

In actually I didn't. What I did was I ran the create table and insert statement scripts on the database engine side and ran the cube create (xmla file) on the analysis side. Then I needed to redirect the datasource to my server. I then re-processed the cube and everything was fine. Please let me know if you need anything else.

RE: The Unpopular SELECT Statement

kmapes — Fri, 22 Feb 2008 13:10:52 GMT

Steve,A question for you. You mentioned that you were getting the Access Denied message when you tried to restore a database. You said you got past that, but I didn't see how. I'm trying to restore a database through Analysis Services and I'm getting the same Access Denied message. Can you share what you did to get by it?

RE: The Unpopular SELECT Statement

Steven Harris-463201 — Thu, 21 Feb 2008 06:57:50 GMT

Not sure it is worth the effort of pursuing the error. SQL Server 2005 is on a desktop that I made into a 2003 server and is really for testing purposes and "playing around".I did get the cube processed after redirecting the datasource to my server and was able to go through the rest of the tutorial.Thanks,Steve

RE: The Unpopular SELECT Statement

VincentRainardi — Wed, 20 Feb 2008 15:44:33 GMT

Thanks for explaining it Steve. So when you restore Sales.abf to sales, SSMS said "Unable to read from the transport connection: An existing connection was forcible closed by the remote host." Where as when you restore the abf to Sales2, SSMS said "MemberKeyUniqueElement cannot appear under Load/ObjectDefinition/Dimension/Hierarchies/Hierarchy. An error occurred when instantiating a metadata object from the file, \Sales2.0.db\Dim Date.1.dim.xml'. An error occurred when loading the Sales cube, from the file, \Sales2.0.db\Sales.2.cub.xml'. At first I was suspecting that perhaps it was because you were restoring SSAS 2008 backup into SSAS 2005 server, but when I checked, the sales.abf file that I just attached was a SSAS 2005 backup. I also tried restoring this sales.abf to both SSAS 2005 and SSAS 2008 server and had no problem restoring them. So it's not because of version. I once restored an SSAS 2008 backup file into SSAS 2005 server and the error message was something like 'compability' problem, rather than transport error or metadata error. What version of SSAS do you use Steve? If it is 2008, which CTP? Is this server local on your PC or remote on another computer? Which edition is the SSAS, i.e. developer, standard, or enterprise? I think if we want to pursue this avenue (the transport and metadata error), we need to understand the situation of your SQL Server/SSAS environment.But now that you have successfully created the sales DB on SSAS, and have created the SQL Server tables and populated them. You can process the sales DB. Process means populating with data and calculating the aggregates. To do this, you need to repoint the data source on the sales DB to your SQL Server tables. This is done by expand data sources folder, right click on sales data source and choose properties. The connection string would be something like: "Provider=SQLNCLI10.1;Data Source=MySQLServer;Integrated Security=SSPI;Initial Catalog=Sales". Click on the elipsis button (...) on the Connection String. Set the Provider as "SQL Server Native Client", Server name = your SQL Server, authentication: Windows, database name: Sales DB that was created by the DDL.sql. Click on the Test Connection button to verify. Click on the ... button on the Impersonation Info and choose Use the Service Account. Click OK.Now that the data source is pointing to the right tables. Right click the sales DB (not the sales DS, sales DSV or sales cube) and choose Process. Ensure that the Process Options is "Process Full". Click on OK to begin processing the sales DB. It should say "Process Succeeded". Click on Close.Expand the cube folder and right click on the sales cube (not the sales DB, sales DS or sales DSV) and choose browse. You should now be able to browse the cube. Note: please ensure that the tables are fully populated (contains data) before you process the SSAS DB. This can be done by executing the population scripts.Kind regards,Vincent

RE: The Unpopular SELECT Statement

Steven Harris-463201 — Wed, 20 Feb 2008 14:53:41 GMT

OK ... here we go.I no longer get the access denied error. When I try and restore the file onto itself (sales.abf restoring to sales) I get the transporter error again. When I try and restore the file onto a different name (sales.abf restoring to sales1) I get the other error I described.I tried executing the xmla script and it worked. I now have a Sales database on the analysis side. Since I got the DDL and data inserts scripts to work in the database engine side am I ready to go with the rest of your tutorial?Thanks again,Steve

RE: The Unpopular SELECT Statement

VincentRainardi — Wed, 20 Feb 2008 14:34:19 GMT

Hi Steve,there is no security that you need to setup when restoring sales.abf or when opening the analysis services project. When and where did the "access denied" error happen? I'm also trying to understand when and where the other error that you mentioned (The ddl2:MemberKeyUniqueElement at line 97) happened. It looks like an XMLA error. Did this error occur when you try to execute sales.xmla to create the cube?To deploy an analysis services project, open BIDS. Create a new SSAS project. Create a data source pointing to the sales database created by DDL.sql. Create a data source view from this data source. Create a cube by using Create Cube Wizard. Then right click on the solution and choose deploy.Alternatively, you can also create a cube on SSAS server by executing sales.xmla from SQL Server Management Studio. When doing this, don't click on "New Query" but click on "New XMLA Query" button on the tool bar.If you describe where and when the errors occured, I will try to help you further.Kind regards,Vincent

RE: The Unpopular SELECT Statement

Steven Harris-463201 — Wed, 20 Feb 2008 13:48:57 GMT

Vincent,Thanks for sticking with this ... :)Unfortunately that didn't work. I now get an error:The ddl2:MemberKeyUniqueElement at line 97, column 10600 (namespace http://schemas.microsoft.com/analysisservices/2003/engine/2) cannot appear under Load/ObjectDefinition/Dimension/Hierarchies/Hierarchy.Errors in the metadata manager. An error occurred when instantiating a metadata object from the file, '\\?\c:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Sales2.0.db\Dim Date.1.dim.xml'.Errors in the metadata manager. An error occurred when loading the Sales cube, from the file, '\\?\c:\Program Files\Microsoft SQL ServeMSSQL.2\OLAP\Data\Sales2.0.db\Sales.2.cub.xml'. (Microsoft.AnalysisServices)What are the steps to testing the deployment you suggested.Thanks,Steve

RE: The Unpopular SELECT Statement

Steven Harris-463201 — Wed, 20 Feb 2008 13:00:09 GMT

That got me further ... I now get the following error:The following system error occurred: Access Denied. (Microsoft.AnalysisServices)Is there a password or other security option I need to set?Thanks,Steve

RE: The Unpopular SELECT Statement

VincentRainardi — Wed, 20 Feb 2008 12:55:06 GMT

Hi Steve, I just reposted the sales_abf.zip, which seems to be successful this time.Try downloading it. Then on step 3.4 (restore database window on SSMS) restore it as different database name, for example sales2. It seems to me that the error has no relation with the restore process. If restoring as different DB name doesn't work, you can verify that your your SSAS environment works properly by deploying a project from BIDS.Kind regards,Vincent

RE: The Unpopular SELECT Statement

VincentRainardi — Wed, 20 Feb 2008 12:48:12 GMT

Reposting the attachment... (sales_abf.zip)

RE: The Unpopular SELECT Statement

Steven Harris-463201 — Wed, 20 Feb 2008 12:39:31 GMT

Vincent,Hopefully you can help me. When I try and restore the sales.abf file I get the following error:The connection either timed out or was lost. (Microsoft.AnalysisServices)Additional Information:>Unable to read from the transport connection: An existing connection was forcible closed by the remote host. (System)>>An existing connection was forcile closed by the remote host (System)This require me to delete the physical files and then the database from the system.Thanks,Steve

RE: The Unpopular SELECT Statement

VincentRainardi — Wed, 20 Feb 2008 12:17:52 GMT

Hi all, thanks for all your responses.Debbie, I attached sales.abf in zip format. I hope you would be able to restore the multidimensional database from this file into your SSAS server.David, did you mean "An existing connection was forcibly closed by the remote host", rather than "existing condition"? Probably this was caused by the database was not restored/created properly. Would it be possible to try to restore it from the attached zip file. If this doesn't work, try creating a new SSAS project in BI Development Visual Studio than deploy it to your SSAS server. This is to validate if your SSAS server can create and host a database. I found that the SSAS on the November edition of SQL Server 2008 is not stable; is this is the version that you are using? Or are you using 2005?Arthur, did you get the error that you mentioned in the SQL Server Management Studio (SSMS) when you try to do step 4.3, which is "Let’s drag to Drop Totals or Detail Fields Here, drag Month to Drop Row Fields Here, drag Product Category to the Select dimension area and set it to Book"? Or did it happen in BI Development Studio (BIDS) when you load the SSAS project? If this error happens in BIDS, try using SSMS. Like the error that David had, it seems that the error that you had, "Error HRESULT E_FAIL has been returned from a call to a COM component" is not related to SSAS. Perhaps we can try verifying your environment first, by creating a new SSAS project on BIDS and deploy to the SSAs server? Then we can try dragging the attribute to the right hand side area on figure 6.Arther, I just read your 2nd post, which was your response to Steve. So you restored the SSAS project into your BIDS. Step 4.3 of my article (figure 6) is done using SSMS, not using BIDS. Perhaps you want to try browsing the cube (and issuing MDX statements) using SSMS?Kind regards,VincentKind regards,Vincent

RE: The Unpopular SELECT Statement

Steven Harris-463201 — Wed, 20 Feb 2008 12:16:50 GMT

Arthur,Thanks ... that helped. I was able to run the scripts and they created the database and populated the tables on the database engine side. However, I still cannot restore sales.abf file on the analysis side. I get an error "unable to read data from the transport connection: an existing connection was forcibly closed by the remote host." when I try to restore the file. This requires me stop the analysis services, delete the physical directory, start the services and then delete the sales database that was partially created.I not sure what to try. Any thoughts?Thanks,Steve

RE: The Unpopular SELECT Statement

fuller.artful — Wed, 20 Feb 2008 12:05:31 GMT

I had no trouble getting the scripts themselves to work. To do so, I opened Management Studio, then opened the scripts (DDL and Populate) in turn, then executed them. Then I switched to Business Intelligence Studio, began the Restore and then realized that I would have to switch a flag indicating whether to permit browsing to a new directory. Rather than do that, I simply moved the backup file to the directory indicated. Then I proceeded and the database restored fine.It was after that that my problems began. I have proceeded further and am now convinced that my setup of BI is toast. I tried creating a new project and got as far as defining a new data source, when I got an error message. Unfortunately, my master copy is off-site, so all this will have to wait until tomorrow (for me at least).As for you, try my steps above. Make sure that you create the database in Management Studio not Intelligence Studio. Just do File | Open then browse to where you stored the DDL and Populate scripts, then run the DDL first and then the Populate. Then switch to Business Intelligence and restore the backup file.Arthur

RE: The Unpopular SELECT Statement

Steven Harris-463201 — Wed, 20 Feb 2008 11:51:52 GMT

I have the same issues. I cannot even restore the database. Additionally, the scripts provided don't seem to work or I am not running them in the correct place.Have you gotten any further in getting this to run? I would like to see it working.Thanks,Steve

RE: The Unpopular SELECT Statement

JJ B — Wed, 20 Feb 2008 10:18:47 GMT

Awesome intro! Not having any need for data warehousing at my agency, I haven't had a chance to learn much about the topic. However, I have a feeling that I will be getting into it some day. I have been curious. This was a great intro. You explained the basic concepts in a quick and easy to grasp manner. Thanks.

RE: The Unpopular SELECT Statement

fuller.artful — Wed, 20 Feb 2008 09:43:18 GMT

Hi Vincent,I liked reading through your article, and then decided to follow you step by step. I was able to grab and restore the backup file, etc., and to run the SQL statements to create the Sales database. So far, so good. I got the Product Dimension set up, by using the dropdowns not by dragging it. But I have another problem, which I suspect concerns SSAS more than your project. But having nowhere else to turn, I'm turning to you.Unlike in your image, on my monitor in the large pane (Details) it says, "The query could not be processed. The data provider didn't supply any further error information."The moment I try to move anything to where you suggested, I immediately get the error message: "Error H_Result E_Fail has been returned from a call to a COM component."Any suggestions?Thanks in advance,ArthurP.S.A good read, and I'm eager to go through all the steps and make it work with my own stuff.

RE: The Unpopular SELECT Statement

abba-602531 — Wed, 20 Feb 2008 08:19:25 GMT

I was able to download the sales.abf file but every time I tried to restore it in SSAS, I would get an error:An existing condition was forcibly closed by the remote hostDespite the error, the database would appear under the databases dropdown. But I couldn't do anything with the database, not even delete it. If I tried to drill down I would get another error (whose message I can't recall and I don't to install it once again).The only way I could drop the DB was to stop SSAS from Services and delete 2 files and 1 folder: Sales.0, Sales.0.db and master.vmp from C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data.Debbie

RE: The Unpopular SELECT Statement

David Christal — Wed, 20 Feb 2008 07:54:05 GMT

I'm unable to download the cube backup file. The link doesn't seem to work.http://www.sqlservercentral.com/Files/sales.abf/409.abf

The Unpopular SELECT Statement

VincentRainardi — Tue, 19 Feb 2008 21:14:31 GMT

Comments posted to this topic are about the item [B]The Unpopular SELECT Statement[/B]