Solved

Question about setting up BI SQL/SharePoint

Posted on 2013-11-14
11
287 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
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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Caste datetime 2 25
RDS SQL Server migrate database to new instance 2 9
SqlAdvisor 2016 3 11
SQL Query 2 0
My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now