Solved

Question about setting up BI SQL/SharePoint

Posted on 2013-11-14
11
295 Views
Last Modified: 2016-02-18
I'm trying to watch a video about BI in SQL and SharePoint. I have downloaded and attached the AdventureWorks DB to my SQL 2012 for the purposes of this vid, and this database does show up in SQL Management Studio. Then, when the vid goes to open up the analysis services from SQL Management Studio, it selects analysis services from the drop down rather than database engine, but I don't have the analysis services option in the drop down.

So the simple question is what do I need to do to get that option to show up in the drop down, even if the answer may not be simple? Obviously, there is something that that is either not installed, or not enabled somehow. I have SQL 2012 and SP 2010 Enterprise that has it's databases stored on SQL Server 2012. Both SP and SQL are running on the same server which is also a DC, because I only have one instance of Windows Server for personal dev purposes on a personal domain. The DC itself is Windows Server 2008 R2.

Thanks in advance. Hopefully an easy question.
0
Comment
Question by:BobHavertyComh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39649742
Hi,
which edition of SQL Server 2012? And which edition of SQL Server Management Studio?
The express editions of both do NOT offer Analysis Services integration.

Analysis Services (SSAS) is an additional feature/role which you have to enable during installation.
But you should easily be able to add a SSAS instance by just running SQL Server setup again - adding features/new instance.

Second: for using a SSAS database (which is not a SQL database) you will have to deploy an OLAP database (should be an additional download in the SQL Server demo data on codeplex).

HTH
Rainer
0
 
LVL 12

Expert Comment

by:duttcom
ID: 39649746
You may be right about something not being enabled. I have SQL 2008 runinng on a 2008 server and use SSIS and SSRS; I have the following services installed and running if you want to check that you have the same -
SQL services running on Server 2008
0
 
LVL 9

Author Comment

by:BobHavertyComh
ID: 39650030
Hi Rainer, I have SQL management studio 2012 as well as 2008 running side by side, but I use 2012 and have my SharePoint databases there. I thought that I remembered choosing to include analysis services when i installed 2012 because I think that I remembered that I needed 2012 for Power Pivot and/or Performance Point.

Hi duttcom. I see an analysis service running under windows services and it's location is
C:\Program Files\Microsoft SQL Server\MSAS11.SQL\OLAP\bin\msmdsrv.exe" -s "C:\Program Files\Microsoft SQL Server\MSAS11.SQL\OLAP\Config"

So I think this relates to 2012.

There is some little dumb detail that I missed somewhere.
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 37

Expert Comment

by:ValentinoV
ID: 39650634
Sounds like you haven't selected the "Complete" checkbox during the installation.  This one is hidden a little bit and by default not selected.  Run the setup again, and in the feature selection screen make sure to check that box (it's a sub-option of SQL Server Management Studio - Basic).

The following MSDN page confirms it: Feature Selection

"Management Tools – Basic : This includes the following:
  o SQL Server Management Studio support for the SQL Server Database Engine, SQL Server Express, sqlcmd utility, and the SQL Server PowerShell provider
Management Tools – Complete : This includes the following components in addition to the components in the basic version:
  o SQL Server Management Studio support for Reporting Services, Analysis Services, and Integration Services
  o SQL Server Profiler
  o Database Engine Tuning Advisor
  o SQL Server Utility management"
0
 
LVL 9

Author Comment

by:BobHavertyComh
ID: 39650827
Hi Valentino, I'm pretty sure that I selected the complete version. When I get to the add features section when re-running setup, all of the boxes are checked, but are greyed out,

I see an analysis service running under windows services and it's location is
C:\Program Files\Microsoft SQL Server\MSAS11.SQL\OLAP\bin\msmdsrv.exe" -s "C:\Program Files\Microsoft SQL Server\MSAS11.SQL\OLAP\Config"

Could this have to do with the fact that I never purchased it? I believe that I installed a trial version as I can't afford the actual product, but I don't see any notifications and the like about my trial period expiring.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39650930
Hmm, might be related but don't know for sure.  Did you first have the Express edition which you then upgraded?  I did encounter similar issues as a result of that the solution would be to first "upgrade", then add additional features.  These are the steps:

first: run setup > Maintenance > Edition Upgrade

then: run setup >  Installation > New SQL Server stand-alone > Add features to existing install > Management Tools - Complete

It's weird though that your install indicates it is installed while it clearly isn't.  Ow, as you mentioned you've got both 2008 and 2012 installed, you're looking at the right version of SSMS I hope?
0
 
LVL 9

Author Comment

by:BobHavertyComh
ID: 39651343
I am looking at the right version, I can't add features as they are all checked but greyed out, as if I already have them all installed. The SQL Analysis Services service is shown as running in windows services and has a path that I listed above that indicates it is for 2012. I don't think I installed the express edition first, but that was a while ago. But if I didn't install these things, then how could the service that I referenced above be shown as running in windows services?

I have Windows Server 2008 R2, SharePoint 2010 Enterprise and SQL 2012 all running on the same box which is the DC as well.
0
 
LVL 9

Author Comment

by:BobHavertyComh
ID: 39651382
I also ran
"Microsoft SQL Server 2012 Service Pack 1 Setup Discovery Report" and it shows that Analysis Services is installed. My 2008R2 version is SQL Express
0
 
LVL 9

Author Comment

by:BobHavertyComh
ID: 39651426
I restarted the Analysis Service in Windows Services, and now I see it as an option in Management Studio for 2012 and can open it.

WTF!!!!  

I'll still keep this question open in case someone wants to explain this so I can give someone some points for their efforts.
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 39654529
Good you got it solved, but it does sound a little scary...  I assume you've got all latest service packs installed?  If not, I'd do that just to be sure it isn't some weird bug that will resurface after you reboot the machine...
0
 
LVL 9

Author Closing Comment

by:BobHavertyComh
ID: 39656426
Thanks Valentino. Will do.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question