New Journal Article Published – Title: “Building an Ideal Tabular Model for Power View reports”

Standard

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.”

Read here: SolidQ Journal: Building an Ideal Tabular Model for Power View reports by Paras Doshi

Blog-Post about Part 1 is here: SolidQ Journal: Building Ideal PowerPivot Model for Power View reports

 

If you have any feedback or comments, please drop a comment or contact me.

SharePoint PowerPivot Site: How to extend document library to connect to Tabular Model for some Power View magic!

Standard

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.

Note: Creating a SharePoint Business Intelligence Dev Environment is a different thing, please refer: Bird’s Eye view of SharePoint BI Dev Environment Setup process and official reference http://msdn.microsoft.com/en-us/library/hh231687%28v=sql.110%29.aspx

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!

Power Pivot Site New Document Disabled

3) Switch to PowerPivot Gallery > Library > Click on Library settings:

Power Pivot Site Gallery Library Settings

4) Click on advanced settings

Power Pivot Document library 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”

Power Pivot document library setting BISM

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.

BI semantic model connection content type

8) Now,

Go to PowerPivot Gallery > Documents > New Document.

Can you see BI Semantic Model connection? Yes? Awesome!

create a new BI semantic model connection

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.

BISM connecting to a Tabular Model for Power View

Click on OK.

10) can you see the connection? Yes? Click on Power View icon.

can you see the BISM connection

11)Can you see the view? yes? Go ahead! Create Awesome Power View reports!

sample power view report

That’s about it for this post.

PowerPivot Model: How to TEST or EDIT existing connections?

Standard

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:

existing connection design tab of powerpivot model

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.

edit data source connection power pivot model

5) After you’re done, click on SAVE.

And you’ll now see the “Existing Connections” Box again:

powerpivot select a connection to a data source that contains the data you want to import

Click CLOSE and you’re done, you have successfully edited or tested the existing connection.

 

 

How to Solve Error: After Reboot, SharePoint sites says “Service Unavailable”

Standard

I had to reboot my Dev Machine on which I have my SQL Server 2012 Business Intelligence Setup. After reboot, It turns out that when I wanted to open the SharePoint Site (for Power View related work) – it gives me error:

“Service Unavailable

HTTP Error 503. The Service is unavailable”

Now, I did quick searches found this and this

Turns out, for my scenario – the default application pool had stopped:

default application pool stopped

So this is what I did to solve the error:

1) Verified that the account and password for the App Pool is correct

2) Started it.

This is how you do it:

Start > IIS Manager > Application pools > Locate Default Application Pool that stopped > Verify that Account and Password are correct. > Start it.

After doing that, the SharePoint site started working again!

Related articles:

How to Disable password expiration for Windows Server 2008 R2 (domain controller)?

Standard

I have written about how to disable password expiration for Windows Server 2008 R2 if it is NOT a domain controller. You can Find that post here: http://parasdoshi.com/2012/04/19/how-to-disable-the-password-expiration-policy-in-windows-server-2008-r2-demo-machine/

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.

windows server 2008 r2 disable password expiration local security policy

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:

windows server 2008 r2 disable password expiration group policy management

4. Go To Policies > Windows Settings > Security Settings > Account Policies > Password Policy

windows server 2008 r2 disable password expiration group policy management editor

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.

That’s about it for this post. Happy Tweaking!

Bird’s Eye view of SharePoint BI Dev Environment Setup process (SQL Server 2012, Sharepoint 2010)

Standard

 

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”:
SQL Server PowerPivot For Sharepoint
Post-Installation: Launch the PowerPivot configuration tool to configure the server
SQL Server PowerPivot For Sharepoint post installation
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 9
Verifying Reporting Services Installed in Sharepoint Mode. Tutorial here.
Also Extend the Document Libraries to include BISM connections.[Read Follow up Blog Post: How to extend document library to connect to Tabular Model to create Power View reports ]
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
sql server training kit 2012 check dependencies

Resources that I refer to for configuration Details:
1) SQL Shorts Video to configure SharePoint BI environment
2) Getting up and running with SQL Server Denali for Business Intelligence (Crescent and PowerPivot in CTP3)
3) Deployment Checklist: Reporting Services, Power View, and PowerPivot for SharePoint

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.

How to disable the password expiration policy in Windows Server 2008 R2 (Demo Machine) ?

Standard

Before we begin, Please note that:

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.

UPDATE: Find a blog post about disabling password expiration policy for Domain Controller: http://parasdoshi.com/2012/06/19/how-to-disable-password-expiration-for-windows-server-2008-r2-domain-controller/

Now, Let’s begin:

1. Go to Start, Administrative Tools, Local Security Policy

2. Under Security Settings, Select Account Policies

3. Click on Password Policy.

windows server disable password expiration policy

4. Change the Value of Maximum Password Age to “0”

windows server disable password expiration policy

5. Click on Apply.

That’s about it. You have disabled the password expiration on your Windows Server Demo machine (which is not a domain controller)

If you are searching for a resource that could help you disable the password expiration policy on windows Server (Domain Controller) then please refer: http://forums.iis.net/t/1148164.aspx

UPDATE: Find a blog post about disabling password expiration policy for Domain Controller: http://parasdoshi.com/2012/06/19/how-to-disable-password-expiration-for-windows-server-2008-r2-domain-controller/

How I solved sharepoint’s service unavailable error, HTTP 503?

Standard

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: