Unsuccessful connection from Excel Data Mining Addin via IIS to MS SQL 2012 Analysis Service with attached AdventureWorksDW2012
This connectivity issue has been a days-long struggle for me. I have referred to several sources for a solution.
I want to connect from the data mining add-in in excel 2010 to a MS SQL 2012 analysis service that is not on the same domain. I would be very happy if I could get a simple canned solution working.
Here's what I did:
1. I installed MS SQL2012 with Analysis server on Win2008 server
2. I successfully attached adventureworks data warehouse and ran through a few lesson solutions to load models:
I am able to verify these objects in SQL server mgmt studio -> analysis server -> analysis server tutorial -> etc.
3. I configured HTTP Access to Analysis Services via IIS (anonymous):
4. I installed the data mining excel addin on a separate PC:
5. In excel -> data mining -> connection, I added my URL and 'test connection' successfully:
I click on any item under Excel -> Data Mining (manage models, brows, query, add mining structure, etc.) and receive:
No default database is available for the selected Analysis Services server connection.
Excel -> Data tab -> From other sources -> http://name.domain.com:XXXX/OLAP/msmdpump.dll
Unable to obtain list of tables from the data source.
What may be wrong:
A. as i mentioned, the two systems are not on the same domain, so something could be wrong with the IIS application pool. I don't suspect this, but there's just no solid indication that its working, since the success indicated above does not result in a success.
B. I added local users and roles.. so many different ways that I lost track. The login that I use to connect from excel is added in SQL Management Studio -> Database Engine -> AdventureWorksDW2012->Security as well as SQL Management Studio -> Analysis -> Analysis Services Tutorial -> Roles -> Membership; it is a windows login on the win2008 server.
C. The analysis server may simply somehow point to the (empty) default 'master' database without regard to my login/role i am using from excel. I don't suspect this, though the errors say otherwise.
D. I may have either forgot to do something, or I did something and removed it. I have done days-worth of work, so at this point, I am no longer sure.
I'm very frustrated with piecing everything together after several days without success and would appreciate any step-by-step guidance or troubleshooting assistance.
Thanks in advance.
good news is that I got it working with the sample data provided. i'll have to report back at a reasonable hour of the day to provide detail
- Edited by Nothing happens Friday, October 25, 2013 3:57 AM notification of working solution
I don't have answer to your question.
If you cannot get it resolved, you can try using Predixion Insight (available from https://www.predixionsoftware.com/predixion/Products/GetPredixionEnterpriseInsight.aspx, choose "In the cloud" option). Predixion Insight has all the features of Microsoft Data Mining add-ins and a lot more. You don't even need to have SQL Server to use cloud version of the Predixion because it works with Predixion Cloud, which can create the models and return predictions. If you do have SQL Server installed in the same domain, you can also install Predixion Insight for your computer.
Tatyana Yakushev [PredixionSoftware.com]
Download Predixion Insight 3.0 - World class predictive platform for big data