Part 2/2 of the series on building Ideal Models for Power View reports is live!
Summary: “In this article, we will first compare the PowerPivot and Tabular models, which will help you choose between these two models for your scenarios, and then we will study the reporting properties in a Tabular Model that you can configure to build an Ideal model for Power View reports.”
So you are SharePoint site admin and your task is to extend Document Library to connect to SSAS Tabular Model so that your Power View reports can connect to Tabular models. we’ll see that in this blog-post.
In this Blog Post, I am assuming you are through step 1- 8 and we’ll see a Tutorial on Step 9: “Extend the Document Libraries to include BISM connections.” so that we can create Power View reports on Tabular Models.
Here are the steps:
1) Open SharePoint PowerPivot site
2) PowerPivot site > Documents > Do you see New Document disabled? Well, we are going to fix that!
3) Switch to PowerPivot Gallery > Library > Click on Library settings:
4) Click on advanced settings
5) Here change the setting of “Allow Management of Content Types?” to YES
And Click on OK
6) Back on Document Library Settings Page, Under Content Types, click on “Add from existing site content types”
7) On “Add Content Types” Page > from Available Site Content Types – Add “BI Semantic Model Connection” to content types to add. and click on OK.
Go to PowerPivot Gallery > Documents > New Document.
Can you see BI Semantic Model connection? Yes? Awesome!
9) Now let’s test it.
Note: I am on my Dev Machine so I am going to test it by just connecting a Tabular Model that’s running on same machine.
Click on OK.
10) can you see the connection? Yes? Click on Power View icon.
11)Can you see the view? yes? Go ahead! Create Awesome Power View reports!
Have you ever had someone sent you a PowerPivot model and asked you to do something with it? And if so – may be, you would have to see what data source(s) the model is using and if applicable, you test the existing connections. If you find yourselves in such a situation, this blog post is for you:
1) Open the excel file and switch to PowerPivot Window
2) Now, switch to Design Tab > Click on Existing Connections:
3) Here you’ll find the list of connections under “PowerPivot Data Connections” > Select the connection you wish to TEST or EDIT > Click on EDIT button
4) Now here you can edit the data source. And if you click on “Test Connection”, you’ll be able to test it too.
5) After you’re done, click on SAVE.
And you’ll now see the “Existing Connections” Box again:
Click CLOSE and you’re done, you have successfully edited or tested the existing connection.
Now, if you are looking to disable the password for the Windows Server 2008 R2 dev. machine which is also a Domain Controller then follow these steps:
1) If you go to “Local security policy- you’ll see the options but it is not going to allow you to change the setting even if you are logged in as domain administrator.
2. So we need an alternate path to edit the password expiration policy.
Go to Start > Administrative Tools > Group Policy Management
3. Here click on “edit” for the default domain policy for the domain of your choice:
4. Go To Policies > Windows Settings > Security Settings > Account Policies > Password Policy
5. Change the Password Policy!
Note that changing your password policy to disable password expiration is a security vulnerability. It’s applicable for your Demo Machine only. Or your Dev Machine. The reason I am documenting it that I do not want to change the password of Windows Server on which I have my Sharepoint BI dev environment Setup. It’s MY Dev Environment and I am NOT sharing it with other folks PLUS I do not anything sensitive on it, So I can afford disabling the password expiration policy.
Step 1 Get the Binaries: SQL Server 2012 RTM, Windows Server 2008 R2, Sharepoint 2010 SP1 x64 Setup a VM: Check the System Requirements and configure the VM. And then Install Windows Server 2008 R2 x64 SP1 (and updates)
Step 2 Add Server Roles: Application Server, File Services, Web Server Change Server Name to a friendly Name
Step 3 At this stage, I cloned the Virtual Machine. And the following steps will be performed on the cloned (copied) virtual Machine. Thanks for the tip that I found here. The benefit of cloning is that you have a Windows Server 2008 R2 VM Template that you can use in Future.
Step 4 On the cloned VM, run dcpromo.exe. This will make your server the Domain Controller and it will install ADDS binaries if they are not installed prior to running dcpromo.exe
Step 5 Install Sharepoint per-requisites Install Sharepoint 2010 SP1 Note: Do NOT run the SharePoint configuration wizard
Step 6 Run SQL server setup to Install “SQL Server PowerPivot for SharePoint”: Post-Installation: Launch the PowerPivot configuration tool to configure the server
Step 7 perform a NEW installation of SQL Server Analysis Services Mode: Tabular
Step 8 perform a NEW installation of SQL Server Analysis Services Mode: Multi-Dimensional
Step 10 At this point, I installed and ran the SQL Server Training Kit 2012. It will list various “Missing” softwares like office, silverlight and also it will help you install sample databases. It’s a nice “shortcut” to make sure that you a “complete” sharepoint BI dev Environment
That’s about it for this post. I’ll refer to this post whenever I have to setup my SharePoint BI Dev Environment. And if you are in the same position, then i hope the Bird’s eye view and the resources listed are helpful to you as well.
1) it’s applicable for your Demo Machine only. Or your Dev Machine. The reason I am documenting it that I do not want to change the password of Windows Server on which I have my Sharepoint BI dev environment Setup. It’s MY Dev Environment and I am NOT sharing it with other folks PLUS I do not anything sensitive on it, So I can afford disabling the password expiration policy.
2) This Blog Post will Show you step by step procedure to disable the password expiration policy IF your server is NOT a Domain Controller. If it’s a domain controller then the steps are different.
I have Sharepoint installed on one of my VM and I needed to open Sharepoint central administration for some reason. BTW, if you are into Sharepoint BI – there’s a little chance that you may run into this error and the error says “Service unavailable. HTTP error 503”. And after quick searches on Internet, figured out how to solve the problem. It turns out that I had changed the password of the Identity. It was my demo machine and I was running sharepoint using the domain’s administrative account. Don’t hit me! I am learning..Kidding. Any-who, Here are the steps:
Go to IIS Manager > Application pools > Locate sharepoint central admin that stopped > right click > Advanced settings > reset password.
Here’s a quick video. I am sorry for poor audio, Next time I’ll use a better tool: