Solved

Question about setting up BI SQL/SharePoint

Posted on 2013-11-14
11
293 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

696 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