SSRS in SharePoint mode: Data alert & Subscribe are grayed out or disabled.

Environment: SQL Server 2012 reporting services 2012 w/ SharePoint 2010 Enterprise.

Problem:

For SSRS reports hosted on SharePoint, the Data Alerts & Subscribe are grayed out or disabled.

data alert subscribe ssrs sharepoint 2012

What do you have to do?

You need to upload a SSRS and for the data sources, you’ll have to store the credentials or no credentials.

It’s not ideal for user-level security (e.g. via Windows Authentication) setup on the data source side but the requirement of data alerts and email subscription dictate that you need to stored the credentials or not have credential requirements.

If the New Data Alert option is grayed, the report data source is configured to use integrated security credentials or prompt for credentials. To make the New Data Alert option available, you must update the data source to use stored credentials or no credentials. – http://technet.microsoft.com/en-us/library/gg492251%28v=sql.110%29.aspx

Real-world story:
what we ended up doing at a client’s was to create a SharePoint library for “report subscriptions” which is hidden from end-users. We added a service account to the data source & we store the credentials of the service in the report used for report subscription. And IT “controls” who receives the email. So after a user submits a request to get emails, IT goes in the security database & see’s if a user is fit to receive the email or not. So not all users may get approval to receive the email. That was a solution that we had to take to stay compliant.

How to solve common Data Quality Problems using Data Quality Services? (Part 1)

New Journal Article: First article of 2, where you will be able to see how you can use SQL Server 2012′s DQS to solve common data quality problems. http://bit.ly/172Kh5L

Topics covered:
- Data standardization
- Identifying and correcting unrealistic or invalid values
- Validation and correcting records using Regular Expressions

Read Here: How to solve common Data Quality Problems using Data Quality Services (Part 1)

How to Solve: SQL Server 2012 Data Quality Services Does not allow you to export the cleansing or matching results to an Excel file if you are using 64-bit version of Excel

While I was working with DQS, I got an error that didn’t allow me to export the cleansing results to an excel file. I searched to find that it was a known issue for the 64 bit version of Excel. So based on what I found, this is how I was able to solve the error:

1. Downloaded and Installed the SQL Server 2012 SP1

Downloaded and Installed the SQL Server 2012 SP1

2. Ran the DQSInstaller.exe -upgrade command in the command prompt

Ran the DQSInsaller.exe -upgrade command in the command prompt

That’s about it. And I was able to export the cleansing results to an 64 bit version of the excel file.

Screenshot 1:

export the cleansing results to an 64 bit version of the excel file.

Screenshot 2:

How to Solve: Excel Data Mining add-in disappeared.

Introduction:

In this blog-post, we’ll see what you can do when the Excel data mining add-in disappears.

Steps:

1. What happened?

So I have installed the Excel Data Mining add-in. 

sql server 2012 data mining excel addin

But I do not see the Data Mining Tab in Excel:

excel sql server data mining tab missing

2) So Now what?

I searched and found this.  and got it working for the software versions (Excel 2010, SQL Server 2012) that I had and so I am documenting it here.

3) Logged in as Administrator > Office button > Options > Add-Ins > Do you see the Data Mining add-in Disabled?

sql server 2012 data mining excel addin disabled excel options

4) Select Disabled Items in the Manage > click GO

excel options enable a disabled item data mining5) Click on the data Mining add-in and enable it. > Click close > Click OK

6) Re-open Excel. Can you see it now? Yes? Yay!

excel sql server data mining tab enabled yay

That’s about it for this post.

Conclusion:

In this blog-post, we saw how to enable the data mining excel add-in.

Steps I followed to Install SQL Server 2012 Master Data Services (MDS) on my Demo Machine:

In this blog-post, we’ll see the step by step guide to install SQL Server 2012 Master Data Services (MDS) after you have installed SQL Server 2012 on your machine. I’ve had installed it on my demo machine and you can build one for yourself using the resources listed here: Bird’s Eye view of SharePoint BI Dev Environment Setup process (SQL Server 2012, Sharepoint 2010)

As I mentioned, these are post-sql-server-installation tasks and so I assume you have your BI environment using SQL Server 2012 already up. The official note can be found here: Install Master Data Services

For the purpose of this blog-post, we’ll break the steps into three main sections:

1. Create MDS Database

2. Create Master Data Management Web Application

3. Associate MDS database w/ web Application

So, let’s get started:

Got to: Start > SQL Server 2012 > Master Data Services > Master Data services Configuration

1. Create MDS Database

1a: click on Create Database:

SQL Server 2012 Master Data Services MDS create database

1b: Enter the instance name and credentials:

SQL Server 2012 Master Data Services MDS database credentials

1c: specify the MDS database name and collation:

SQL Server 2012 Master Data Services MDS specify database name

1d: Specify the Administrator Account and review the summary. After it installs successfully – you’ll see a message:

SQL Server 2012 Master Data Services MDS successfully installed

2. Create Master Data management Web Application

2a. Switch to Master Data Services configurations > web configuration > select Create new website

SQL Server 2012 Master Data Services MDS create new website2b. Specify configuration details for the website:

Here’s what I entered:

Website Name: MDS

Port: 81

Host Header: Left it blank

Entered username and password

SQL Server 2012 Master Data Services MDS website configuration

2c. Click on OK and in the web configuration dialog box, click on Create Application

3. Associate MDS database w/ web Application

3a. Under the Associate Application with Database click on Select

SQL server 2012 master data services MDS assoicate database

3b. Select the database that will get associated to the application selected.

* I selected the database that I had created in Step 1

elect the database that will get associated to the application selected.

3c. After successful installation, you’ll see a message:

SQL server 2012 master data services MDS

The web application is then launched in the browser, here you’ll see getting started guide, videos and MDS Samples. Go Explore!

Conclusion

In the blog post, we saw the three post installation tasks for Master Data Services w/ SQL Server 2012.

Again, the official reference is here: Install Master Data Services 

Passed couple of SQL Server 2012 Business Intelligence Beta Exams!

I passed couple of SQL Server 2012 business intelligence beta exams! The exams are:

71-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
71-466: Implementing Data Models and Reports with Microsoft SQL Server 2012

 

Manage ALL relationship’s between tables while building PowerPivot Model

One of the task while building a PowerPivot Model is to create/Edit/Delete Relationships between tables. With SQL Server 2012 release of PowerPivot for Excel, we now have a Diagram view and this makes our task of defining relationships easier! Great addition. Any-who when you view your model in Diagram Model, This is how it looks:

powerpivot sql server 2012 diagram view

To Edit a Relationship, You can click on the Relationship represented by the arrow and it would pop up an “Edit Relationship” Dialog Box.

But what if you wanted to Manage ALL relationships of this Model? You can do this!

GO TO Design Tab > Click On Manage Relationships.

manage relationships powerpivot 2012

Now here you can manage (create/edit/delete) relationships among tables. Very easy!

manage relationships dialog box powerpivot 2012

That’s about it for this post. Your feedback is welcome!

 

Ability to create Hans-Rosling styled BI dashboard with PowerView is great!

I got a chance to play with power-view which seems like great feature of SQL Server 2012! And I liked it that we can create Hans Rosling styled BI dashboard’s with Powerview. For those who do not know about Hans Rosling presentations, check these collection: All Hans Roslings Ted Videos. 

motion charts hans rosling bubble chart with play axis

And by studying modules in SQL server Training kit 2012, I was able to create a similar Dashboard, It was fun!

Now, Listing resources for you, If you are Interested to learn about Powerview:

Check out this SolidQ Journal article: http://www.solidq.com/sqj/Pages/Business_Intelligence/An-introduction-to-PowerView-in-SQL-Server-2012.aspx

Also, SQL server Training kit 2012 is a great resource.

And if you do not like setup’s and want to quickly get hands-on – Check this out: http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx

And also Microsoft Virtual Academy for video tutorials about SQL Server 2012 BI:
1. breakthrough-insights-using-microsoft-sql-server-2012-analysis-services
2. breakthrough-insights-using-microsoft-sql-server-2012-reporting-services

That’s about it for this post. Your feedback is welcome!

And Let’s connect! I Look forward to Interacting with you on any of these people networks:

paras doshi blog on facebookparas doshi twitterparas doshi google plus