1 day course PowerPivot for Business Professionals

About the Course

  • Are you an Excel power user and working as a business analyst, financial controller, …
  • Are you looking for a tool to create your own reports without help from IT …
  • Do you want to combine data from different sources …
  • Do you want to add your own calculations and analysis to existing reports

… well then PowerPivot is the tool you need!

PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within the application users already know and love: Microsoft Excel. It’s the user-friendly way to perform data analysis using familiar Excel features you already know, such as PivotTable and, PivotChart views, and slicers. It’s the fast way to generate rich and interactive analysis tools. It’s the right way to achieve deeper business insight and shorter decision cycles.

In this 1 day training we will give you a jumpstart with PowerPivot for Excel. At the end of the day you will be able to combine data from different sources and turn this data into powerful reports and analysis tools without help from your IT department.

The course is bulk loaded with demos and hand-on labs. Students receive a text book and printed course material. More info on PowerPivot can be found on http://www.powerpivot.com/

Topics

  • Introduction to Self Service BI and PowerPivot
  • Importing Data
  • Enriching Data
  • Self-Service Analysis
  • Self-Service Reporting
  • Writing Data Analysis Expressions (DAX)

When: Wed. November 9th 2011, 9h-17h
Price: 385 EUR excl. VAT
Contact: info@kohera.be
Where: Kohera offices, Veldkant 31, Kontich, Belgium

Treat your SQL Server instances as patients in an ER

Just think for a minute, monitoring your SQL Server instances does not differ a lot from monitoring patients in an ER room. When we are dealing with a performance issue, one of the first things we do is check CPU pressure (heart rate), Memory pressure (blood pressure) and I/O pressure.

Just as doctors do, we use a number of quick tools (scripts) to have an idea what the problem might be. Based on those findings, we decide to do some tests (blood analysis/perfmon monitoring).

For over a year now, I have this idea to connect a heart rate monitor to an ill SQL Server. It’s a matter of interfacing but in theory it should be possible.

Now just imagine you have a stack of life monitors near your desk. A nice alternative is a slick layout on a tablet pc connecting to an instance. All I need now are scrubs!

photo by brykmantra under CC

Mr Denny presenting at SQL Server Days 2011

I’m really pleased that we could attract Denny Cherry (WebsiteTwitter) to speak at SQL Server Days 2011.

Mr Denny will be speaking about:

Optimizing SQL Server in a virtual environment
In this session we’ll look over some of the things which you should be looking at within your virtual environment to ensure that you are getting the performance out of it that you should be. This will include how to look for CPU performance issues at the host level. We will also be discussing the Memory Balloon drivers and what they actually do, and how you should be configuring them, and why. We’ll discuss some of the memory sharing technologies which are built into vSphere and Hyper-V and how they relate to SQL Server. Then we will finish up with some storage configuration options to look at.

Indexing Internals
In this session we’ll dig into the internal structors of indexes. We will explore the differences between clustered and non-clustered indexes, what’s layed out within each page of the indexes and how the SQL Server uses the data within the indexes to find rows quickly.

Table indexing for the .Net developer
In this session we will be looking at the best and worse practices for indexing tables within your SQL Server 2008 databases. We will also be looking into the new indexing features that are available in SQL Server 2008 (and SQL Server 2005) and how you the .NET developer can make the best use of them to get your code running its best.
Learn why indexes need to be created
Learn how indexes can help your databases performance
Learn how indexes can hurt your databases performance

Check out the agenda of SQL Server Days 2011 for more details.

SBM MVP Community Roadshow

SQL Server experts are often called in to support Microsoft business products as many of the products use SQL Server as a backend. To name a few: SharePoint, Axapta, Dynamics and Windows Small Business Server all use SQL Server.

Since Windows Small Business Server is a popular product and hence so is the question to support it, it’s always nice to get free training whenever it’s possible. The people behind the curtains of SBS Migration which are on the SBM MVP Community Roadshow take a halt in Belgium, November 29th.

For more details, check out the SBM MVP Community Roadshow website.

Locking during debugging stored procedure in Visual Studio 2005

I had this weird situation for almost two days whilst debugging a stored procedure in Visual Studio 2005. Just after a few minutes, over and over again at the same line, I got an error popping up:

Failed to retrieve data for this request.
Lock request time out period exceeded.
So obviously it had something to do with locking which is not unusal but it shouldn’t give me an error from within Visual Studio right?
I’m working on a standalone instance here, so no interferance from other applications or people with open connections in SSMS.
When I looked at the Activity Monitor in SSMS, I could see a LCK_M_S key lock caused by the query below which is not part of my stored procedure (It almost looks like Visual Studio is executing this query).
SELECT
db_name() AS [Database_Name],
udf.name AS [Name],
SCHEMA_NAME(udf.schema_id) AS [Schema],
udf.object_id AS [ID],
CAST(
 case
  when udf.is_ms_shipped = 1 then 1
  when (
    select
      major_id
    from
      sys.extended_properties
    where
      major_id = udf.object_id and
      minor_id = 0 and
      class = 1 and
      name = N''microsoft_database_tools_support'')
    is not null then 1
  else 0
end
       AS bit) AS [IsSystemObject],
CAST(CASE WHEN ISNULL(smudf.definition, ssmudf.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
(case when ''FN'' = udf.type then 1 when ''FS'' = udf.type then 1 when ''IF'' = udf.type then 3 when ''TF'' = udf.type then 2 when ''FT'' = udf.type then 2 else 0 end) AS [FunctionType],usrt.name AS [DataType],sret_param.name AS [DataTypeSchema],
ISNULL(baset.name, N'''') AS [SystemType],
CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar'') AND ret_param.max_length <> -1 THEN ret_param.max_length/2 ELSE ret_param.max_length END AS int) AS [Length],
CAST(ret_param.precision AS int) AS [NumericPrecision],
CAST(ret_param.scale AS int) AS [NumericScale],
case when amudf.object_id is null then N'''' else asmbludf.name end AS [AssemblyName],
case when amudf.object_id is null then N'''' else amudf.assembly_class end AS [ClassName],
case when amudf.object_id is null then N'''' else amudf.assembly_method end AS [MethodName],
CASE WHEN udf.type IN (''FN'',''IF'',''TF'') THEN 1 WHEN udf.type IN (''FS'',''FT'') THEN 2 ELSE 1 END AS [ImplementationType]
FROM
sys.all_objects AS udf
LEFT OUTER JOIN sys.sql_modules AS smudf ON smudf.object_id = udf.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmudf ON ssmudf.object_id = udf.object_id
LEFT OUTER JOIN sys.all_parameters AS ret_param ON ret_param.object_id = udf.object_id and ret_param.is_output = 1
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = ret_param.user_type_id
LEFT OUTER JOIN sys.schemas AS sret_param ON sret_param.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = ret_param.system_type_id and baset.user_type_id = baset.system_type_id
LEFT OUTER JOIN sys.assembly_modules AS amudf ON amudf.object_id = udf.object_id
LEFT OUTER JOIN sys.assemblies AS asmbludf ON asmbludf.assembly_id = amudf.assembly_id
WHERE
(udf.type in (''TF'', ''FN'', ''IF'', ''FS'', ''FT''))
ORDER BY
[Database_Name] ASC,[Schema] ASC,[Name] ASC

The query started to show up in the Activity Monitor as soon as I expanded the stored procedure node from the Server Explorer pane where you connect to a database. I guess Visual Studio 2005 uses the info from the resultset to build the nodes in the Server Explorer/Database connection.

I started experimenting with the Visual Studio settings including the time out setting but I didn’t make any progress until I decided to switch to Visual Studio 2010.
The query responsable for the locking did no longer appear.

Denali – Import Text Editor settings from Visual Studio

Up until SQL Server 2008 R2, you had to customize the font and background colors manually in SSMS.

SQL Server Denali lets you import your Visual Studio settings such as the custom colors in the Text Editor.
The fact that the Denali SSMS splash screen shows Powered By Visual Studio probably has a lot to do with that.

In Visual Studio go to Options, Environment, Import and Export Settings and copy the location where your settings are stored with the .vssettings extension.

Visual Studio Export Settings

 

Next open SSMS, go to Options, Environment, Import and Export Settings and locate your .vssettings file you copied from Visual Studio using the team settings file option.

SSMS Import Settings

Dive into the Summer with TechNet

Dive into the Summer with TechNet

What about some light reading on virtualizing SQL Server during the holidays whilst sipping a nice cocktail?

Sure, your wife might be rolling her eyes when she sees you reading a Hyper-V whitepaper but hey, nowadays virtualized SQL Servers are everywhere so virtualization has become a technology that needs to be mastered by SQL Server experts as well and not only by system administrators.

TechNet has started its summer campaign with the first part on virtualization. You won”t have to spend the entire afternoon by the swimming pool reading white papers. There”s material for the ones having only half an hour to spend,half a day and a full day (be careful on the cocktails!)

A good document to start with is Hyper-V Getting Started Guide followed by Hyper-V: Using Hyper-V and Failover Clustering and Virtualizing SQL Workloads 

Read why Hyper-V is the Best Virtualization Solution for SQL Server Whitepaper which covers the advantages over VMware such as performance, high availability, management, monitoring and costs.

Read all about SQL Server in a virtualized environment here.

Happy holidays!

Be careful with cloned VMs of SQL Server production servers

Imagine this,

You”re handed a new clean and crisp full functioning copy of a SQL Server production server VM which you can use to experiment with.

Of course the clone has a new computer name. You might want to execute a select @@servername to see what the actual name is which is used by SQL Server itself.
Unless the original server has been dropped from table sysservers en replaced with the new server name, you will still see the server name of the production server.

Now, be very carefull when executing statements on the cloned VM where @@servername is used in the code. Sometimes the @@servername variable is used to dynamically build file paths…

Use the following statements to change the server name:

sp_dropserver <”old_nameinstancename”>

GO

sp_addserver <”new_nameinstancename”>, local

GO

Also check: Rename a computer that hosts a stand-alone instance of SQL Server

 

Setup SSRS in SharePoint Integrated Mode

This post is about configuring SQL Server Reporting Services in SharePoint Integrated mode the quick and clean way with no fuzz in 40 steps.

I”m not discussing the default values, I will only talk about the values which need to be changed or selected in order to get SSRS set up in the least amount of steps.


The scenario:
  • SharePoint 2010 Enterprise Edition + named instance SQL Server 2008 Express Edition which is deployed during the SharePoint setup. Name of the SQL Server instance: SHAREPOINT.
  • Separate default SQL Server 2008 R2 instance (Enterprise Edition).
  • SSRS currently installed in native mode on the SQL Server 2008 R2 default instance.
  • AdventureWorksDW2008R2 hosted on the SQL Server 2008 R2 default instance.

The procedure contains 5 parts:

A. SSRS Configuration {SQL Server}
B. Web application {SharePoint}
C. Site collection {SharePoint}
D. SSRS integration {SharePoint}
E. Content types {SharePoint}
Let”s start!


A. SSRS Configuration {SQL Server}
  1. Log on to your SharePoint 2010 box.
  2. Fire up the SQL Server Reporting Services Configuration Manager.
  3. Connect to the Report Server instance. In my case this is the default SQL Server 2008 R2 instance (MSSQLSERVER)
  4. The first thing you will notice is the Report Server Status. Check the Report Server Mode. It should say Native (we start from native mode in this scenario).
  5. Click Database in the left pane.Click Change Database. Select Create a new report server database.
    Enter the proper credentials.
  6. Enter a database name and select SharePoint Integrated mode.
  7. Enter the credentials and do not forget the <domain name><username> (.<username> will do as well).
  8. Choose Report Manager URL in the left pane and click the apply button. This will configure the Report Manager virtual directory.
  9. Backup the encryption keys.

B. Web application {SharePoint}
  1. Open the SharePoint Central Administration website.
  2. Choose Application Management > Manage web applications.
  3. Click the New button in the ribbon. This will bring up the Create new web application popup.
  4. Keep the default values to keep things simple except for:
    1. Choose a name for the new IIS Website. I will choose SSRSDemo in this example.
    2. Choose a name for the application pool. I will again choose SSRSDemo.
    3. Make sure you connect to the right database instance. In my example I will connect to the named SQL Server 2008 Express Edition instance named SHAREPOINT.
    4. Choose a database name or keep the default value with the GUID suffix. I will call the database WSS_Content_SSRSDemo.
    5. Hold your horses before you hit the OK button when this information pops up:

The Microsoft SharePoint Foundation Web application has been created. 

If this is the first time that you have used this application pool with a SharePoint Web application, you must wait until the Internet Information Services (IIS) Web site has been created on all servers. By default, no new SharePoint site collections are created with the Web application. If you have just created a Forms Based Authentication (FBA) Web application, then before creating a new site collection, you will need to perform some additional configuration steps. 

Learn about how to configure a Web application for FBA. 

Once you are finished, to create a new site collection, go to the Create Site Collection page. 


5. Click Create Site Collection page to move on to C. Site collection {SharePoint}


C. Site collection {SharePoint}
  1. Enter a title for the site collection. SSRSDemo for example.
  2. Choose a template. I will choose Business Intelligence Center because I want to store my SSRS reports in a BI related environment.
  3. Enter a username for Primary Collection Administrator. Make sure you enter the full domain name. e.g.: <domain name><username> (.username  won”t work here).
D. SSRS integration {SharePoint}

  1. Go to the SharePoint Central Administration website > General Application Settings > Reporting Services > Reporting Services Integration.
  2. Enter the Report Server Web Service URL which you can find the SQL Server Reporting Services Configuration Manager.
  3. Choose the Authentication Mode. I will choose Windows Authentication. Entering .<username> will do.
  4. Go to the SharePoint Central Administration website > General Application Settings > Reporting Services > Add a Report Server to the Integration.
  5. The server name should already be provided and enter the name of the SQL Server instance which hosts the report server database.
  6. Enter the credentials.

E. Upload a report {SharePoint}
  1. Open your new top level web site we created in C. Site collection {SharePoint}
  2. In case you forgot the URL of the web site, go to SharePoint Central Administration > Web Application and look for the URL.
  3. Go to All site Content > Documents.
  4. In the ribbon, go to Library Tools > Library.
  5. Click Library Settings.
  6. Click Advanced Settings.
  7. In Content Types, check Yes for Allow Management of content types and click OK.
  8. In the columns section, click Add from existing site columns.
  9. In the Select site columns from drop-down box,select Report Server Content Types.
  10. Select all available site content types and click the Add button followed by OK.
  11. Go back to All site content > Documents.
  12. Click the Add Document link.
  13. Browse for a Report provided by the AdventureWorksDW200R2 samples from CodePlex.
  14. Enter a title for the report.
There is also the option to create a report yourself if you don”t have the reports from the sample reports. It only takes a few seconds to create a report using the Report Builder (see screenshot below).
 



You have now finished the complete setup. Click the report you have uploaded to view the result.

 

My mind to your mind