OLAP PivotTable Extensions Update

OLAP PivotTable Extensions is a really cool free add-in for Excel that’s been around for quite a while now.  It allows you to have some exposure to what’s going on under the covers (Among many other features) while you browse a cube using an Excel PivotTable. This weekend there has been an updated version and you should check out the features and download it now!

http://olappivottableextend.codeplex.com

The new features in this release are:

  • Support for Excel 2013
  • Upgrading to the AS2012 version of ADOMD.NET to now support connections to .bism files. (This upgrade required adding the .NET Framework v3.5 SP1 as a prerequisite. Use a prior version of OLAP PivotTable Extensions if this prerequisite is a problem for your organization.)

SSRS – Using Lookup Function for Multiple Column Join

When SQL Server 2008 R2 was released several new Reporting Services expressions were made available that would help join multiple datasets together.  Those functions were the Lookup Function, LookupSet Function and the MultiLookup Function.  My general recommendation for these function is not to use them when you have two dataset from the same data source.  They’re really intended for connecting datasets together that are not directly related because otherwise I could just handle it in my source query.

My experience with them had been fairly academic until recently when I had a real scenario where the Lookup Function would be beneficial.  I was working with a customer that had a report requirement that needed data from DB2 and Oracle in a single tabular report.  What made this circumstance more difficult was that the join between the DB2 dataset and the Oracle dataset was not a single column but two.

Seems basic enough right?  If we were writing T-SQL we would simply do a join on multiple columns with an AND between them.  Because these are two different data sources in Reporting Services I had to get a little more creative.  If you’ve used the Lookup function in SSRS (template below) expressions then you know it can only accept one field from the source and one from the destination.  So what if you have two columns to join on?

Lookup(source_expression, destination_expression, result_expression, dataset)

The approach I took was to concatenate the two join columns together and then place the concatenated value in the Lookup function.  I also placed a literal string between the two columns to protect myself from coincidental matches.  To make the solution a little cleaner I created a calculated field on each dataset that handled the expression.  So both datasets had a calculated field I called JoinColumn and was defined similar to this:

=Fields!JoinColumn1.Value + ”||” + Fields!JoinColumn2.Value

Notice the double pipes that are used here to protect from coincidental matches.  With these calculated field created on each dataset now my Lookup function would be fairly standard.  Here’s my end result:

Lookup(Fields!JoinColumn.Value, Fields!JoinColumn.Value, Fields!ReturnColumn.Value, “DataSet2”)

I hope this helps as you find practical uses for the Lookup Function in Reporting Services.

Office 2013 Preview – Enabling PowerPivot in Excel

With the Office 2013 announcements today I was excited to download and get my hands on the tools.  Because this is still a customer preview we’re bound to find things here and there that aren’t functioning as expected when experimenting with the Office tools.

The things I had on my list to test first were Power View and PowerPivot from Excel (Yes, Power View is now available in Excel).  In this post I’ll focus on helping you get started with PowerPivot.

With Excel 2013 you no longer have to download PowerPivot as a separate add-in but you may find when exploring the PowerPivot tab that all the available buttons in the Office Ribbon are greyed out.  The problem is that while the add-in maybe installed it is not enabled.  To fix this click File->Options as shown below.

image

Go to the Add-Ins page.

image

Change the Manage property to COM Add-ins then select Go

image

Check Microsoft Office PowerPivot for Excel 2013 then click OK.  You may also notice PowerPivot for Excel as an option here if you had a previous version of PowerPivot installed.

image

You should now be able to start working with PowerPivot.  Hope this helps getting you started!

Building Your First Power View Report

This has been reposted from a guest blog post I wrote on the Microsoft Business Intelligence team blog.

Fulfilling User Needs

Developing a Power View report has been touted as being so simple that within a handful of clicks you can add rather impressive elements, like effective visualizations, to a report. This isn’t always the case with other tools in the market. The goal of this post is to walk you through Power View as if you were designing a report for the first time, and show you how simple it really is to build a Power View report.

Using the Development Environment

Before we start, it’s important to note that you will need SQL Server 2012 and SharePoint 2010 to create a Power View report. Power View also runs on Silverlight 5, which enables you to edit Power View reports in a browser window, as shown below.

clip_image002[4]

The development interface retains the familiar look and feel of Office tools. If you know how to use Excel, picking up Power View for the first time should be a breeze.

Understanding your Data

Understanding your data and what you want to achieve with it is one of the most important aspects of building a Power View report. The better a report designer understands the layout of the data model, the easier it becomes to produce insightful results. Power View is designed to help you produce fast results in an easy to understand format.

When designing PowerPivot reports with images and visualizations, you may want to consider BISM Tabular data sources for Power View reports. Shown below is a report that leverages a data set with images enabled by BISM Tabular data sources.

clip_image004[4]

Building your First Report

In this demonstration of building a basic Power View report I will be using a data set found on Microsoft’s live demo site. To follow along go here to access the blank Power View Report (this will require a Windows Live ID).

Upon launching this live example you will see a blank report in presentation view. To edit this blank report, select Edit Report in the top left of your screen.

clip_image006[4]

You can create report elements by selecting the data you would like to visualize from the data pane on the right side of your screen. It’s helpful to know how your data model is organized because you may find yourself struggling to find the fields you need otherwise.

Next, from the Product table, select Image and then use the Visualizations toolbar to set the field as Tiles. This will place the product images in slide tile view. You may need to stretch the tiles across the width and the length of the report for layout purposes.

clip_image008[4]

To capture the details of each product in the slide tile view, select a single product image and select Card from the Visualizations toolbar dropdown. Add the fields Product Name, Category, Channels, Item Group, SKU and Retail Pricefrom the Product table. To fit all of the information you may need to resize the Card as shown below. Think of the Card as a baseball card that captures all the key information about a product.

clip_image010[4]

Next, we will add regional sales information to the report. To get the regional data to interact with the product image tiles, select a tile and pick Region Map and Region Name data elements from the Region table. From the Sales table selectRevenue. This gives a tabular view of the region sales for the selected product with an image that highlights the part of the country that makes up that region. Move this table to the bottom right of the report to improve the layout of the report.

clip_image011[4]

To complete the regional view of our report, we will put together a bar chart of profitability metrics by product. Begin by selecting one of the tiles and pick the data elements Profitability from the Sales table and Wholesales Price from the Product table. Turn the new table into a chart by selecting Clustered Bar from the Visualizations toolbar. Resize and move the chart to the bottom left of the report. Lastly, name the report Product Sales Report. The final result of this first view should look like this.

clip_image012[4]

Power View reports can have multiple views, which appear as multiple pages to a user. This allows users to easily flip through multiple reports in a single Power View document. To create a new view, select the View context menu on the left side of the design surface and click New View as shown below.

clip_image014[4]

In the new view we will demo one of the most impressive visualizations in Power View –the Scatter chart. To begin, go to the Sales table and select Revenue, Quantity, and # Products, and from the Product table select Category. Once the data elements are selected, choose Scatter Chart from the visualizations toolbar to turn the table into a chart. The result is a nice scatter that shows which product categories generate the most revenue for the business. To take this chart to the next level, we could add animation to it by dragging Month from the Date table to the Play Axis of the chart. Resize the chart to fill the entire report view and hit the play button on the bottom left to view product category performance over time. Lastly, name the report Sales Analysis by Month.

clip_image016[4]

With this report complete, you can view it as a user by selecting Full Screen under the Home tab of the toolbar. This allows you to flip through multiple views of the report and preview the report views by selecting the button in the bottom left of your screen.

clip_image018[4]

Hovering over the views, you get a larger preview on the top report. When you select a report, it appears in full screen mode.

Going through this demo should have given you the idea of how with just a few clicks, you can create impactful, insight-driving reports with Power View.

SQL Lunch #68 – Designing Your First Power View Report

Next week I will be speaking on designing your first Power View report for SQL Lunch.  Be sure to join me for the event and as always “No fluff, just stuff”.

Add To Outlook: Add To Calendar

Date and Time: 7/18/2012 11:30:00 AM CST

Topic: #68-Designing Your First Power View Report
Power View has the capability of creating eye-popping visualizations within SharePoint 2010. It provides intuitive ad-hoc reporting that can be used by a variety of business users to make critical decisions. In this session you will learn how to design your own dazzling reports using Power View.

Identifying Long Running SQL Agent Jobs Script

I’m usually pretty hesitant to share scripts I write because inevitably it will get torn to pieces, but in this case I’d actually like some feedback so I can continue to grow this solution.
This script spawned from Jorge Segarra (@SQLChicken) and I working on a project together that had a SQL Agent job that somehow got hung up for 4 days on the SQL Server when it normally takes less than 10 minutes. Now, Jorge is a superb DBA so he had setup notifications upon SQL Agent failures but we had no way of knowing something was wrong with this particular job because it never actually failed.

So Jorge and I discussed the problem and thought why can’t we get some kind of notification for excessively long running jobs just like we get failures. So with our powers combined we came up with this solution.

Pre-Requisites:

  • Permission to msdb..sysjobhistory, msdb..sysjobs, msdb..sysjobactivity
  • Database mail setup (If you choose to be notified of a long running job)
  • A database to store the stored procedure and log table the script loads. Jorge already had a database called DBAdmin on all his servers so he could store scripts like Brent Ozar’s sp_Blitz script and other things he finds useful as a DBA.

Script:

This first script creates the log table which will only be inserted into when a job exceeds the time limit you deem to be excessive.

CREATE TABLE [dbo].[LongRunningJobs](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[JobName] [sysname] NOT NULL,
	[JobID] [uniqueidentifier] NOT NULL,
	[StartExecutionDate] [datetime] NULL,
	[AvgDurationMin] [int] NULL,
	[DurationLimit] [int] NULL,
	[CurrentDuration] [int] NULL,
	[RowInsertDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LongRunningJobs] 
ADD CONSTRAINT [DF_LongRunningJobs_Date] DEFAULT(getdate())
FOR [RowInsertDate]

This second part requires more explanation and we’ve tried to detail what we did and why we did it with comments throughout the code. A high level description would be that this script looks at the average run time of a job and if a job that is currently running exceeds the average by 150 percent (arbitrary number that you can change) then it alerts the DBA team using database mail. So right away you may see some flaws (runtimes could vary drastically) with our plan but this is v1 so give us feedback!

Here’s the script to create the stored procedure. Our thought was to run this on a SQL Agent job as well every 5 minutes or so.

-- =============================================
-- Author:        Devin Knight and Jorge Segarra
-- Create date: 7/6/2012
-- Description:    Monitors currently running SQL Agent jobs and
-- alerts admins if runtime passes set threshold
-- Updates: 7/11/2012   Changed Method for capturing currently running jobs to use master.dbo.xp_sqlagent_enum_jobs 1, ''
--
-- =============================================
CREATE PROCEDURE [dbo].[usp_LongRunningJobs]
AS
--Set Mail Profile
DECLARE @MailProfile VARCHAR(50)
 
SET @MailProfile = (
        SELECT @@SERVERNAME
        ) --Replace with your mail profile name
 
--Set Email Recipients
DECLARE @MailRecipients VARCHAR(50)
 
SET @MailRecipients = 'DBAGroup@adventureworks.com'
 
--Set limit in minutes (applies to all jobs)
--NOTE: Percentage limit is applied to all jobs where average runtime greater than 5 minutes
--else the time limit is simply average + 10 minutes
DECLARE @JobLimitPercentage FLOAT
 
SET @JobLimitPercentage = 150 --Use whole percentages greater than 100
    -- Create intermediate work tables for currently running jobs
 
DECLARE @currently_running_jobs TABLE (
    job_id UNIQUEIDENTIFIER NOT NULL
    ,last_run_date INT NOT NULL
    ,last_run_time INT NOT NULL
    ,next_run_date INT NOT NULL
    ,next_run_time INT NOT NULL
    ,next_run_schedule_id INT NOT NULL
    ,requested_to_run INT NOT NULL
    ,-- BOOL
    request_source INT NOT NULL
    ,request_source_id SYSNAME COLLATE database_default NULL
    ,running INT NOT NULL
    ,-- BOOL
    current_step INT NOT NULL
    ,current_retry_attempt INT NOT NULL
    ,job_state INT NOT NULL
    ) -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions
 
--Capture Jobs currently working
INSERT INTO @currently_running_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''
 
--Temp table exists check
IF OBJECT_ID('tempdb..##RunningJobs') IS NOT NULL
    DROP TABLE ##RunningJobs
 
CREATE TABLE ##RunningJobs (
    [JobID] [UNIQUEIDENTIFIER] NOT NULL
    ,[JobName] [sysname] NOT NULL
    ,[StartExecutionDate] [DATETIME] NOT NULL
    ,[AvgDurationMin] [INT] NULL
    ,[DurationLimit] [INT] NULL
    ,[CurrentDuration] [INT] NULL
    )
 
INSERT INTO ##RunningJobs (
    JobID
    ,JobName
    ,StartExecutionDate
    ,AvgDurationMin
    ,DurationLimit
    ,CurrentDuration
    )
SELECT jobs.Job_ID AS JobID
    ,jobs.NAME AS JobName
    ,act.start_execution_date AS StartExecutionDate
    ,AVG(FLOOR(run_duration / 100)) AS AvgDurationMin
    ,CASE
        --If job average less than 5 minutes then limit is avg+10 minutes
        WHEN AVG(FLOOR(run_duration / 100)) <= 5
            THEN (AVG(FLOOR(run_duration / 100))) + 10
        --If job average greater than 5 minutes then limit is avg*limit percentage
        ELSE (AVG(FLOOR(run_duration / 100)) * (@JobLimitPercentage / 100))
        END AS DurationLimit
    ,DATEDIFF(MI, act.start_execution_date, GETDATE()) AS [CurrentDuration]
FROM @currently_running_jobs crj
INNER JOIN msdb..sysjobs AS jobs ON crj.job_id = jobs.job_id
INNER JOIN msdb..sysjobactivity AS act ON act.job_id = crj.job_id
    AND act.stop_execution_date IS NULL
    AND act.start_execution_date IS NOT NULL
INNER JOIN msdb..sysjobhistory AS hist ON hist.job_id = crj.job_id
    AND hist.step_id = 0
WHERE crj.job_state = 1
GROUP BY jobs.job_ID
    ,jobs.NAME
    ,act.start_execution_date
    ,DATEDIFF(MI, act.start_execution_date, GETDATE())
HAVING CASE
        WHEN AVG(FLOOR(run_duration / 100)) <= 5
            THEN (AVG(FLOOR(run_duration / 100))) + 10
        ELSE (AVG(FLOOR(run_duration / 100)) * (@JobLimitPercentage / 100))
        END < DATEDIFF(MI, act.start_execution_date, GETDATE())
 
--Checks to see if a long running job has already been identified so you are not alerted multiple times
IF EXISTS (
        SELECT RJ.*
        FROM ##RunningJobs RJ
        WHERE CHECKSUM(RJ.JobID, RJ.StartExecutionDate) NOT IN (
                SELECT CHECKSUM(JobID, StartExecutionDate)
                FROM dbo.LongRunningJobs
                )
        )
    --Send email with results of long-running jobs
    EXEC msdb.dbo.sp_send_dbmail @profile_name = @MailProfile
        ,@recipients = @MailRecipients
        ,@query = 'USE DBAdmin; Select RJ.*
From ##RunningJobs RJ
WHERE CHECKSUM(RJ.JobID,RJ.StartExecutionDate) NOT IN (Select CHECKSUM(JobID,StartExecutionDate) From dbo.LongRunningJobs) '
        ,@body = 'View attachment to view long running jobs'
        ,@subject = 'Long Running SQL Agent Job Alert'
        ,@attach_query_result_as_file = 1;
 
--Populate LongRunningJobs table with jobs exceeding established limits
INSERT INTO [DBAdmin].[dbo].[LongRunningJobs] (
    [JobID]
    ,[JobName]
    ,[StartExecutionDate]
    ,[AvgDurationMin]
    ,[DurationLimit]
    ,[CurrentDuration]
    ) (
    SELECT RJ.* FROM ##RunningJobs RJ WHERE CHECKSUM(RJ.JobID, RJ.StartExecutionDate) NOT IN (
        SELECT CHECKSUM(JobID, StartExecutionDate)
        FROM dbo.LongRunningJobs
        )
    )
    DROP TABLE ##RunningJobs
GO

Again please email me with any changes you find necessary so that I can happily update this script. Expect a similar post by Jorge on this too, he’s a co-author of the solution!

Follow

Get every new post delivered to your Inbox.

Join 36 other followers