SQL Saturday #486 Richmond – A Window Into Your Data

 

sqlsat486_web

Thanks for attending my session on window functions in TSQL. I hope you learned something you can take back and use in your projects or at your work. You will find an link to the session and code I used below. If you have any questions about the session post them in comments and I will try to get you the answers.

Questions

  1. Can an OVER clause be used in the WHERE clause?
    • No. The OVER clause can only be used in SELECT and ORDER BY clauses.
  2. Some follow up on ROWS and RANGE with context to CURRENT ROW.
    • We had a lot of discussion around this. In our examples below, RANGE aggregated all the data that fit into the ORDER BY clause. ROWS only referenced the row it was in. So, RANGE looks at everything that meets the criteria established by the PARTITION BY and ORDER BY clauses. ROWS is bound to the physical row.
    • Code examples:
      • OVER (PARTITION BY CustomerName ORDER BY OrderDate RANGE CURRENT ROW)
        • Summed two rows of data for the customer with the date. Both rows had the same date.
      • OVER (PARTITION BY CustomerName ORDER BY OrderDate ROWS CURRENT ROW)
        • Each row only contained the data for the row it was in.

Slides, Code, and Follow Up Posts

The presentation can be found here: A Window into Your Data

The code was put into a Word document that you can get here: TSQL Window Function Code

This session is also backed by an existing blog series I have written.

T-SQL Window Functions – Part 1- The OVER() Clause

T-SQL Window Functions – Part 2- Ranking Functions

T-SQL Window Functions – Part 3: Aggregate Functions

T-SQL Window Functions – Part 4- Analytic Functions

Microsoft Resources:

Excel Tip #30: Excel Services Visual Limitations – Displaying Images

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Introducing Brian Wright – Guest Blogger

Brian Wright

Today, I am happy to announce that Brian will be joining DataOnWheels as a guest blogger. I have worked with Brian over the past couple of years and his Excel visualization skills are great. I look forward to his contributions to the Excel Tips series and other BI related topics. Thanks Brian.

Hello Data on Wheels Readers! Let’s start this blog post by letting you know a few things about myself. First, I am not a professional writer, blogger, or ever social guru, but I am passionate about what I do. I love data visualization. Watching boring data come to life in a visual report or dashboard is my “thing”. Secondly, when things don’t work the way I think they should, I become obsessed in finding out a way around it.

Images Are Not Displayed in Excel Services

That is what leads us to this blog post today. In the limited environment I work within, Excel Services is used quite often in our BI suite of tools. When I realized that the ever so important images I was adding in my Excel workbooks would not show on Excel Services, my obsession kicked in.

Here is the trick or hack. (Using the word hack makes me look much cooler in my kid’s eyes). Wherever you want your picture within your workbook, simply add a chart. Yes, you read correct, simply add a chart.

clip_image002

Using Charts to Display Images

The trick here is not to link the chart to any type of data at all. Just leave it blank. Right Click on the blank chart and navigate to “Format Chart Area”. Navigate to the fill area and select “Pattern or Texture Fill”.

Next, click on the File Button and select your image. Your image will now show as a background image in your chart. Save and then voila!

clip_image004

Once Excel Services displays your workbook, you will be pleasantly surprised to see your image right where you want it!

Minnesota BI User Group – Powering Up HDInsight with Power BI (December 2015)

On Wednesday, December 16, I presented on this topic at the Minnesota BI User Group.  This session is based on five blog posts that I created in August 2015.

You can find the presentation here: Powering Up HDInsight with Power BI (pdf).

The details can be found in the blog posts noted below:

HDInsight-Series-Featured-Pic_thumb

Setting Up and HDInsight Cluster (No Scripts Required)

Exploring the Microsoft Azure HDInsight Query Console (No Scripting Required)

Uploading Files to an HDInsight Cluster (No Scripting Required)

Using Power BI with HDInsight Part 1: Power Query and Files

Using Power BI with HDInsight Part 2: Power BI Desktop and Hive

My goals for this series

1. Document using Power BI with HDInsight

2. Prove that you can set up a HDInsight Cluster with no scripts

Other References from the Session

Azure: http://azure.microsoft.com/en-us/

Cloud Berry: http://www.cloudberrylab.com/free-microsoft-azure-explorer.aspx

 

Thanks for attending my session.

Five Years, A Quiet Quarter, A Look Ahead to 2016

Five Years of Blogging

From DataOnWheels Logo Original to data-on-wheels-1

My blogging story started on December 7, 2010. I have now had a blog for over 5 years. I want to thank all of you who have read my blog and interacted with me through it. You have seen me change the theme once and do a number of series. Here are some highlights from the past five years:

Top 5 Posts All Time

  1. Adding Top 10 Charts to Power View Which Honor Filters
  2. Simple batch script to generate XMLA and deploy SSAS DB
  3. T-SQL Window Functions – Part 1- The OVER() Clause
  4. Exploring Excel 2013 for BI Tip #14- Sparklines and Pivot Tables
  5. O, There’s the Data- Using OData in SSIS

Top Series All Time

The Excel BI Tips series has changed it name a couple of times. However, this tip series still rings true even today even as Microsoft invests in other tools. Look for some more Power BI content this year, but this series will continue to have updates. Also, look to see some Excel 2016 topics added to the list as that release becomes available. Here are the top ten tips from the series:

Tributes

A tribute is an expression of gratitude or praise. A couple of years ago, I started a series about individuals who have impacted my career. I do this as a tribute to my father-in-law, Ed Jankowski who passed away in December 2009. Check out my original post about him and his impact on me being in software development today.

Some Stats

I want to thank everyone again for taking time to check out my blog. Here are some stats that I thought were cool and decided to brag about here:

  • 2011 daily average: 9 – 2015 daily average: 162
  • 156 posts
  • 135,000 views
  • Best ever views in a day: 584

Thanks again for checking out my “help” library. As I noted in one of my posts, I blog to not forget and to pass along what I have learned. The key for me is that I do it when I can about topics that interest me.

A Quiet Quarter

The last statement holds true here. I have had a very quiet end of the year. I had blogs which followed up sessions, a practice that I intend to continue, and one BI Tip. November and December were quiet as my job and family took precedence as Pragmatic Works closed out the year strong and we had holiday activities at home including getting my two kids in college home. Well, the dust has settled so I am getting a few more posts published now. Look for the Minnesota SQL Server User Group and Minnesota BI User Group follow up posts this week.

Looking ahead to 2016

After a busy year last year, I am looking forward to having some new opportunities to write about Azure, SQL Server 2016, and other technologies I have not even seen yet. Are you excited for what is coming? Let’s have a great year working with data and analytics.

Boston BI User Group Meeting–Dashboard Design with Microsoft: Power BI vs Datazen (10/13/15)

Boston BI User Group

Thanks for joining Anthony Martin (@SQLMartini) and I at the Boston BI User Group Meeting in October. During the session, we demo’d and built dashboards in Power BI Desktop and Datazen Publisher.

Power BI

image

www.powerbi.com

Couple of thoughts from our demo:

  • Power BI is a way to get data, model data, and visualize it
  • Power BI Desktop allows you to work with data on your PC
  • Power BI is experiencing a lot of change – for example over 40 changes were applied in September 2015
  • Power BI has an open API that allows you to customize the experience

Datazen

image

www.datazen.com

Couple of thoughts from our demo:

  • Design first scenario – make it look good, then shape data to match
  • Datazen publisher allows us to create dashboard for many different profiles
  • Datazen handles custom shapes

Additional Training from Pragmatic Works

Questions from the Session

Can you use links in Datazen to support drillthrough?

Yes. You can find more information here: Drill-throughs to Other Dashboards or Custom URLs.

Power BI API Development

You have the ability customize Power BI. Check out the contest winners to get some ideas of what is possible.

image

Details about the solutions can be found on the Power BI blog: http://community.powerbi.com/t5/Best-Visual-Contest/con-p/best_visual_contest/tab/entries.

You can find more about custom visuals here: https://powerbi.microsoft.com/en-us/custom-visuals.

Thanks again for joining us.

Thoughts on data, business analytics, and the SQL Server community

%d bloggers like this: