February 20, 2008 at 2:53 pm
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
February 20, 2008 at 3:44 pm
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
February 21, 2008 at 6:57 am
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
February 22, 2008 at 1:10 pm
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?
February 22, 2008 at 2:27 pm
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.
February 25, 2008 at 10:08 pm
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
August 20, 2008 at 3:14 am
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,
August 20, 2008 at 4:42 am
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
August 20, 2008 at 9:34 am
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
January 30, 2009 at 6:42 am
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.
January 30, 2009 at 8:50 am
Who ate the figures?
All I get is blue boxes :crying:
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
January 30, 2009 at 9:21 am
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.
January 30, 2009 at 10:29 am
How to AUTOMATE the process and deployment of the CUBE when new data added to the FACT and DIMENTION table.
January 30, 2009 at 10:36 am
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
January 30, 2009 at 5:31 pm
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.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply