# Friday, June 04, 2010

If so then my team would like to know as we are looking for another business intelligence consultant.

Full details on our careers web site: SQL / BI Consultant (722749).

You can either post your details directly on the site or send me your CV at [jsnape at microsoft.com] and I’ll enter it for you (disclosure: if you send it via me I will get a bounty when you are hired).

Good luck.

This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Friday, June 04, 2010 12:18:29 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0] Trackback
# Tuesday, February 24, 2009

One of the big problems I have with some new customers is the knowledge gap between where they are and where they need to be in order to be successful. Given that people can read much faster than they can receive presented information it makes sense to have a reading list.

I’ve blogged previously some Analysis Services resources but new and better content is appearing all the time. In addition to that article here are my current reading recommendations for anyone planning a BI or Analysis Services project. Know this lot backwards and you should have a good head start.

Everyone

SQL Performance Tuning with Waits and Queues

Analysis Services Performance Guide

Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services

The Data Loading Performance Guide

Report Server Catalog Best Practices

Reporting Services Performance Optimizations

New Data Warehouse Scalability Features in SQL Server 2008

Scaling Up Your Data Warehouse with SQL Server 2008

Architects and Developers

OLAP Design Best Practices for Analysis Services 2005

Best Practices for Data Warehousing with SQL Server 2008

Analysis Services Processing Best Practices

Top 10 SQL Server Integration Services Best Practices

Top 10 Best Practices for Building a Large Scale Relational Data Warehouse

Operations and Support

Storage Top 10 Best Practices

SQL Server Best Practices

IIS Performance Tuning

Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarios

In fact pretty much anything on the sqlcat website is pure gold.

This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Tuesday, February 24, 2009 10:15:39 AM (GMT Standard Time, UTC+00:00)  #    Comments [0] Trackback
# Saturday, July 29, 2006

It is possible to use report models as a report data source from within Business Intelligence Development Studio (BIDS) although you have to jump through a few hoops to make it happen.

The connection string to use is:

server=<report server url>;datasource=<path to report model>;

e.g.

server=http://localhost/reportserver;datasource=/Report Models/Adventure Works DW;

Note that the connection string has two components that must be specified and neither component uses quotes around the values. You can then use the Report Model query designer.

This is all you need for SQL Server models but those using Analysis Services cubes require a bit of hacking because the Visual Studio version of the report model designer doesn't automatically specify a perspective and the query will fail without one. There isn't a user interface mechanism for doing it so you need to manually add a custom property to the query definition. The easiest way of doing this is to design a report using Report Builder and extracting the definition from the query that it generates. The bit to look out for in the query XML is:

<CustomProperties>
  <CustomProperty Name="qd:PerspectiveID">
    <Value xsi:type="xsd:string">
      http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling/udmmodeling:
        Perspective_Cube_Adventure_Works</Value>
 </CustomProperty>
</CustomProperties>

Note that the <Value> field should not have any newlines in it and the query XML is actually embedded and not part of the RDL schema to the text above needs escaping (< becomes &lt; etc). The best way to create these reports is with the report wizard as the query text is displayed on its own before being put into the RDL. Then you can merge the perspective without having to escape it.

Using this mechanism you can edit any report created by Report Builder. Just open the report in BIDS and replace the connection with a Report Model data source. One thing you can't easily do is round trip from reports created in BIDS to Report Builder because much of the metadata that Report Builder needs is missing. Still, it's useful to be able to create a large number of basic reports using the builder and then finish those that need it in BIDS.

This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Saturday, July 29, 2006 4:34:01 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0] Trackback
# Sunday, July 17, 2005

If you are developing Reporting Services extension assemblies that require <appSettings> elements in application configuration file (e.g. <add key="SqlConnectionString" value="Integrated Security=SSPI;Data Source=(local);" />) then you will need to modify a couple of files:

  • To design reports successfully, add the configuration to the VisualStudio app.config file
    C:\Program Files\Microsoft Visual Studio .NET 2003\Common7\IDE\devenv.exe.config
  • To deploy successfully, add the configuration to the ReportServer web.config file
    C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\web.config
This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Sunday, July 17, 2005 2:30:43 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0] Trackback
# Saturday, March 19, 2005

After spending an untold number of hours trying to get a Reporting Services Extension to work I thought I should pass on the knowledge to others… Basically, it’s all about security and the rssrvpolicy.config file. This file is a little confusing especially as the default installation uses a weird indent. This article on code access security pretty much covers everything except where to put your new code group definition.

<CodeGroup class="FirstMatchCodeGroup" PermissionSetName="Nothing">
    <
CodeGroup Name="Report_Expressions_Default_Permissions"/>
    
<CodeGroup class="FirstMatchCodeGroup">
        <
IMembershipCondition Zone="MyComputer" />
        <
CodeGroup Name="SharePoint_Server_Strong_Name"/>
 
        <
CodeGroup Name="YOUR_CODE_GROUP_HERE"/>

    </CodeGroup>
</
CodeGroup>

I have removed has much as possible from the file to illustrate where your new definition goes. See the nesting? The outer one says match the first inner one or set permissions to nothing. Then a code group Report_Expressions_Default_Permissions sets up the default expression permissions. The next code group has a membership condition of Zone="MyComputer" with a bunch of full trust Microsoft code groups. Finally, the last one is for SharePoint. Your new code group goes directly after this one at the same level.

Hope that saves someone some time.

This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Saturday, March 19, 2005 7:23:12 PM (GMT Standard Time, UTC+00:00)  #    Comments [0] Trackback
# Sunday, March 13, 2005
This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Sunday, March 13, 2005 10:50:42 AM (GMT Standard Time, UTC+00:00)  #    Comments [0] Trackback