Question about setting up BI SQL/SharePoint

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.
Who is Participating?
ValentinoVConnect With a Mentor BI ConsultantCommented:
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...
Rainer JeschorCommented:
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).

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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

BobHavertyComhAuthor Commented:
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.
ValentinoVBI ConsultantCommented:
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"
BobHavertyComhAuthor Commented:
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.
ValentinoVBI ConsultantCommented:
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?
BobHavertyComhAuthor Commented:
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.
BobHavertyComhAuthor Commented:
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
BobHavertyComhAuthor Commented:
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.


I'll still keep this question open in case someone wants to explain this so I can give someone some points for their efforts.
BobHavertyComhAuthor Commented:
Thanks Valentino. Will do.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.