Microsoft BI PFE

The Kerberos, SharePoint, Reporting Services and Internet Explorer case

2013-02-15T15:23:00Z

Hello everyone,

It’s been a while since I did my last post and this week I want to share with you one experience using integrated authentication with SharePoint 2010, Reporting Services 2008 R2 and Kerberos in a multi server environment.

Since I already did all the work to write the situation in another SQL Server blog, please refer to http://blogs.msdn.com/b/blogdoezequiel/archive/2013/02/11/the-kerberos-sharepoint-reporting-services-and-internet-explorer-case.aspx.

It’s a very interesting case with a real life example that you can easily apply some troubleshooting techniques in your environment and learn how Kerberos works and where to start looking for authentication problems.

Hope to see you soon!

Alex

Analysis Services: Removing Instance Level Admin Rights from Developers….

2013-02-15T10:47:00Z

One of popular questions in these days: Is it possible to remove admin rights from developers but they can still run backup/restore operations as well as profiler trace? I know, from developer’s perspective, this is like removing their freedom for things they developed but on the other hand, from Management perspective, admin rights should only belong to Database/BI Operational team. It is a very long running debate

Short Answer: YES

Here is my scenario and steps to limit permission:

In this scenario, we have two users : UserA and UserC

UserA: Instance Admin (represents DBA Permission)

UserC: Database Level Admin (Represents Developer who has admin rights for specific database)

Two databases: Adventure Works and Contoso Database

UserC hasFull Control over Contoso Database

Now, userC can run Backup/Restore commands againts Contoso Database.However there is one issue, We have identified. There is no problem on backup command through GUI however if you try to restore database through GUI, userC gets following error message “The ‘DOMAIN\username’ does not have permission to call the Discover method”

I suspect that this might be potential defect on GUI in SSMS by sending extra discover commands to Analysis Services. However, you can still restore database by using XMLA script.

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<File>C:\Program Files\Microsoft SQL Server\MSAS10_50.OLAP\OLAP\Backup\Contoso Retail.abf</File>
<DatabaseName>Contoso Retail</DatabaseName>
<AllowOverwrite>true</AllowOverwrite>
</Restore>

and it works completely well.

You can find required permissions for backup/restore operation in following link:

http://msdn.microsoft.com/en-us/library/ms174874(v=sql.105).aspx

Another feature your developers may want to use is Profiler Trace. Developers can run profiler trace but they can only see profiler trace events againts databases,they have admin rights on.

if you give full permission to user for specific database, user can run profiler trace and see all events related with that database only. For instance, UserC had admin rights for Contoso Database. UserC can run profiler trace against AS instance but userC can only see profiler trace events for Contoso Database. If UserA logs on to Contoso, this will appear on UserC trace. But if User logs on to Another Database Let’s call it AdventureWorks, in this case, This session doesn’t appear in UserC trace.

This is screenshot UserA is accessing Adventure Works

This is screenshot from Trace, UserC is running

Now if usera access Contoso Retail database, this will appear on trace that userC is running on. Screenshot attached

if you have any questions, feel free to contact me.

Managing Server Side Analysis Services Profiler Trace

2013-02-12T23:08:00Z

I have been asked how to run Analysis Services Profiler Trace as Server side Trace. There are few ways of running trace on server like astrace tool. In this post, I am going to show you how you can create server side trace using xmla and stop trace.

First Step is to create XMLA for your profiler trace events. Here are steps:

-Open SQL Server Profiler Trace

-Connect to Analysis Services Instance you would like to capture events for.

-Choose your events

-Click Run and then stop trace in case you don’t want to add any overhead of running GUI.

-Now we have events in hand we want to collect.

-Next step is to export these events as XMLA file.

- Click File->Export->Script Trace Definition->For Analysis Services 2005-SQL11->Save as Profiler.XMLA

-Now we have generated script to create trace on server side. With few modification, you can specify rollover information. Quick note, although you choose rollover options in Profiler trace GUI, when you export Trace events as XMLA script, it doesn’t save rollover info to xmla file. You need to manually attributes.

-Let’s open XMLA in Management Studio first:

-Connect your Analysis Services instance through Management Studio.

-Click XMLA button shown below and drag and drop your xmla file into this window.

-You will see similar Trace definition like below:

Here are text version of attributes in case you may want to copy directly from this posting:

<ID>DBA Performance Trace</ID>

<Name>DBA Performance Trace</Name>

<LogFileName>C:\OLAP_TRACE.trc</LogFileName>

-You can add File location, rollover options just after Name attribute like below

-Delete following tag (first line of your script) from script as you don’t need to specify <?xml> tag in your xmla command. Delete following line from your script then execute script

-Once you execute script, Server Side trace is created. There are two ways of checking running traces:

1- DMVs : select * from $system.discover_traces

2-Discover Commands:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
    <RequestType>DISCOVER_TRACES</RequestType>
    <Restrictions>
        <RestrictionList>
        </RestrictionList>
    </Restrictions>
    <Properties>
        <PropertyList>
        </PropertyList>
    </Properties>
</Discover>

-Now ,we created trace and monitor. Now it is time to stop our server side trace.

Following Script stops Profiler Trace

<TraceID>DBA Performance Trace</TraceID>

</Object>

</Delete>

if we look at running traces on server again, we will see that our server side trace is stopped now. You can go to path and review your trace file. Only trace you will see running is Flight Recorder for Analysis Services.

You can also create SQL Server Agent job and add Job Step which executes XMLA Command to manage server side trace. I have seen some customers creating these jobs to manager server side tracing for Analysis Services.

If you have any questions feel free to contact me

Thanks

Kagan Arca

Reporting Services SharePoint Integrated Mode Disaster Recovery Options….

2013-01-30T22:00:14Z

I was working with one of my customer around Reporting Services SharePoint Integrated Mode Disaster Recovery options. We recently released following document which explains DR solutions perfectly. It is great document.

Planning Disaster Recovery for Microsoft SQL Server Reporting Services in SharePoint Integrated Mode

http://technet.microsoft.com/en-us/library/jj856260.aspx

Thanks

Kagan Arca

Hardware Sizing a Tabular Solution (SQL Server Analysis Services)

2013-01-20T15:40:00Z

Hello everyone,

This month we released a new document about hardware sizing in SQL Server 2012 Analysis Services Tabular mode. This document provides guidance for estimating the hardware requirements needed to support processing and query workloads for an Analysis Services tabular solution.

Check more information here.

See you next time!

Business Intelligence Academy for you…

2013-01-13T19:10:34Z

It is pleasure to announce that we have another great offering in our premier catalogue. BI Academy is targeting any technical contributor to Business Intelligence solution, who wants to skill up on Microsoft Business Intelligence components. BI Academy is not only set of classroom trainings. We regularly catch up with attendees to give opportunity to ask their questions. BI Academy is mentoring program which involves classroom, virtual trainings as well as mentoring sessions.If you are interested in following mentoring program, please contact premier services or your Technical Account Manager.

BI Academy Training
BI Academy is divided in seven modules, which provides different levels of expertise and knowledge. Each module is a two days class with hands-on training. It follows a roadmap from the fundamentals to deeper areas inside the BI stack, like performance and tuning techniques. Learn how to integrate SQL Server™ BI technologies with SharePoint® Server so that you can use them to provide one central location for placing all your enterprise-wide BI content and tools. Technologies covered:

SQL Server Engine
Analysis Services (SSAS) Tabular and Multi-Dimensional
Reporting Services (SSRS)
Integration Services (SSIS)
Data Quality Services
Master Data Services
Excel tools and PowerPivot
SharePoint Server 2010
PerformancePoint and Excel Services

Module 1
BI Fundamentals
Overview of the fundamental concepts in a BI solution by introducing the different multi-dimensional models.
Module 2
Tabular Modelling with SQL Server 2012
Learn how to install, develop and configure SSAS Tabular Solutions with SQL Server 2012 and take advantage of xVelocity technology.
Module 3
SSAS Performance, Tuning & Optimization
Review and improve the performance of a SSAS solution and learn how to troubleshoot the most common scenarios.
Module 4
Building and loading data warehouses with SSIS & T-SQL
Review how to improve the performance of a BI solution and review of how to troubleshoot the most common scenarios, including DW Design, SSIS Development and how to Load data into a Data Warehouse.
Module 5
Enterprise Information Management
Take advantage of Data Quality Services (DQS) and Master Data Services (MDS) to build a Knowledge Driven solution. Learn how to operate with data quality issues and how you can achieve this using real case scenarios.
Module 6
Data Consumption & Visualization with SharePoint Integration
After session 3 where we saw how to create different ways to explore data or to aggregate that data on a custom view, this session has the main pur-pose of showing how to turn those visualizations available in SharePoint. In resume, this session show how a BI Solution could be integrated with a SharePoint infrastructure.
Module 7
Data Consumption & Visualization with SSRS, PowerPivot and Power View
Presentation of the capabilities of the Data Visualization & Data Consumption Layers, Reporting Services Features & Development, Self-Service BI with Report Builder and Self-Service BI with PowerPivot for Excel.

MDX Fundamentals & DAX Fundamentals Sessions

2012-12-05T12:40:39Z

I am pleased to announce, our new two courses are available in January. Here are course details:

MDX Fundamentals: In this course, we will be covering MDX Fundamentals. You will learn how to multi dimension query language works and how to think to write MDX to query data. As you may imagine, Writing MDX is different than writing SQL. In this course on first day, we will teach how to build query algorithm and write query. We will also show how to navigate through hierarchies with MDX. We will be also covering common and useful MDX functions. In second day, We will be covering more advance functions. We will also write calculation scripts for sample cube. This course is two days course. If you would like to understand what’s MDX and how to write MDX queries, this is perfect opportunity. Also, in this course, we have less slides, more hands – on as this course aims to increase hands-on experience on MDX. If you are interested in this course, please contact your account manager or contact me for further details.

DAX Fundamentals: In this course, we will be covering DAX functions for Tabular Mode and PowerPivot. You will learn how to transform your MDX Queries or Multi- Dimensional Structure into Tabular Mode. This course covers most of DAX functions as well as Defining Calculated Columns, Defining Measures, building PowerPivot Model. This course is also full of hands- on sessions.This is 1 day course. If you are interested in this course, please contact me or contact with your account managers.

We are also working on online version of these courses. We will keep you updated.

Kagan Arca

Funny Case in Reporting Services

2012-11-22T21:07:00Z

Hello everyone,

This week I had a funny case about Reporting Services scale-out. If you are aware of this subject you now that you can use multiple instances of SQL Server Reporting Services using the same Report Server database.

I had two Report Servers in different servers and a Report Server remote database in a SQL Server cluster:

· Server A – Instance1

· Server B – Instance2

· Server Z – SQL Server Cluster

So, the goal was to remove SSRS Instance2 from scale-out keeping only SSRS Instance1.

If we look into Reporting Services Configuration Manager we could see the two instances in the scale-out configuration section:

First step would be remove to Instance2 from scale-out and then uninstall Reporting Services bits from Server B.

But something weird happened. First the person responsible for this operation removed Instance2 using Reporting Services Configuration Manager where the following warning appeared before removing the instance from scale-out.

This warning is normal and after we click OK, we can see that Instance2 was removed from scale-out giving the information of the installation ID that was removed.

Now comes the strange part. If you look into Keys table in Report Server database or using Reporting Services Configuration Manager in Instance1, we can see that Instance2 record still exists!

If we check the status column in scale-out section we see that Instace2 is in “Waiting to Join” state. Basically the operation done by the user simple removed the symmetric key information for Instance2 but did not removed from scale-out.

So what can we do? If you try to add again Instance2 (This option does not make any sense!) you can see that it complains that SSRS instance no longer exists in the server.

Next, the logic step would be to use the rskeymgmt.exe tool with option “-r <installationID>” to remove the symmetric key information for a specific report server instance, thereby removing the report server from a scale-out deployment. The <installationID> is a GUID value that can be found in the RSReportserver.config file or in the Keys table in Report Server database.

But something went wrong. Tool return an error indicating that Report Server Windows services for instance MSSQLServer was not found. If we look closely MSSQLServer is the default named instance for SQL Server.

How can we solve this issue? Let’s recap our environemt: One server with SSRS Instance1 and another server with SSRS Instance2. Since we are running this tool in the server with Instance1 (and Instance2 was part of Instance1 scale-out) we need to specify the “–i <Instance ID>“option while running rskeymgmt.exe.

So we ran rsmgmtkey.exe with options –r and –i like the following picture specifying Instance2 installation ID and Instance1 as scale-out instance.

And here you go! Now everything is in place and Instance2 is finally off the scale-out topology.

Hope this solution helps you!

Alexandre Mendeiros Premier Field Engineer – Portugal

Reference

rskeymgmt Utility (SSRS)

http://msdn.microsoft.com/en-us/library/ms162822.aspx

Scale-out Deployment (Native Mode Report Server)

http://msdn.microsoft.com/en-us/library/ms181357.aspx

Add and Remove Encryption Keys for Scale-Out Deployment

http://msdn.microsoft.com/en-us/library/ms155931(v=sql.110).aspx

Microsoft Business Intelligence at a Glance Poster

2012-11-21T00:23:12Z

You might want to download Microsoft BI Poster and stick it to your desk may be ? :) Here is link:

http://www.microsoft.com/en-us/download/details.aspx?id=35586

Enjoy!

What happens to MDS and DQS after SQL Server 2012 Service Pack 1 Installation?

2012-11-20T23:49:00Z

We released SQL Server 2012 Service Pack 1 recently. You can download SQL 2012 Service Pack 1 from following link:

http://www.microsoft.com/en-us/download/details.aspx?id=35575

Generally, it is really good idea to follow latest service packs. Service Packs includes important updates and fixes.I decided to install Service Pack 1 to my DQS and MDS Environment. Before applying any patches, I generally check versions. Here we go:

Then I run SQL Server Service Pack 1 installation:

After successful installation, when you try to access MDS and DQS, you will get following error messages:

in MDS: The client version is incompatible with the database version. Ask your administrator to upgrade the client components, the database components, or both. Run the Master Data Services Configuration Manager on the server for more information.

inDQS: A new version of .NET or Data Quality Services was installed on this machine. In order to continue to work with DQS please run 'DqsInstaller.exe -upgrade'.

This is because, you have to run following steps after installing service pack 1:

Run DQSInstaller.exe –upgrade. If you installed the default instance of SQL Server, the DQSInstaller.exe file will be available at C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn.Double-click the DQSInstaller.exe file.
In Master Data Services Configuration Manager, click Select Database, select existing MDS database, and then click Upgrade.

After running upgrade for my instance, everything started to work as expected.

For MDS, when I try to open MDS Configuration Manager, it automatically gives following error message:

"The database requires an upgrade. You cannot change system settings until the database is upgraded"

As you see in screen below, Upgrade Database button becomes enabled.

I run Upgrade wizard to upgrade database.

After running upgrade wizard, everything started to work fine. When I check MDS Version, it was showing 11.1.0.0.

After running these two steps, All should be back to normal.

There is also WIKI Page for DQS : http://social.technet.microsoft.com/wiki/contents/articles/8442.upgrade-dqs-installing-cumulative-updates-or-hotfix-patches-on-data-quality-services-en-us.aspx

I hope this helps!

Master Data Services 2012: SVC Handler Mapping Error

2012-11-20T22:47:00Z

Exciting news...

2012-11-14T22:45:00Z

It is pleasure to announce our new blog site which will cover technical information as well as news, updates, events about Business Intelligence Premier Field Engineering. Our expert team is from all around world and it is great opportunity for us to share our experience on the field with you. In this blog, you will find more content around, best practices, recommendations, customer learnings, workshops, events. You will also have a chance to ask questions to our experts from all around world. Here are some technologies we are planning to cover:

Sharepoint
Reporting Services (SSRS)
Integration Services (SSIS)
Analysis Services (SSAS) / PowerPivot
Data Quality Services (DQS)
Master Data Services (MDS)
SQL Server
Data Warehousing
StreamInsight

In addition to exciting technical topics, we will also give you updates about:

Microsoft Premier Catalog Update For Business Intelligence
Microsoft SQL/BI Operational Days
Community Events

We also want to talk about :

Recommendations
Customer Scenarios / Lessons Learned From Large Implementations
Performance Tuning and Optimisation Techniques
Operational Implementations
Troubleshooting/ Monitoring Techniques

I know, all sounds interesting and we are looking forward to sharing our experiencewith you but we also want to hear from you! Your feedback is always valuable for us to address your needs.

Stay tuned! We will have some exciting news around initiative we are working on Readiness Program model for you. More news soon to come!