Musings on Data

A blog for data, apis, and all things Power BI by Lukasz Pawlowski.

  • Musings on Data

    #VisualizeChange - Comments in Words

    • 0 Comments
    Recently a retweet from @ olivier_matrat came through my twitter feed and it struck a chord. It was the #VisualizeChange challenge part of this year's World Humanitarian Summit. Submissions are due 8/31, so there's still time for folks to work on it. Not sure if I'll actually enter into the contest, but I did do enough work on it to produce the below, hope you like it. I also did some work to build an excel workbook that translates the raw table of data provided for the competition into something more usable. If you'd like to use the workbook, I've provided a link below where you can download it. To use it, you'll either need Excel 2013 with Power Query or Power BI Desktop (free) to pull in the data, or other tools that can read Excel files...
  • Musings on Data

    Visualizing London's open data in Power BI

    • 0 Comments
    Today we're visiting London, UK. In previous posts, I've looked at data from Seattle and Chicago . Now we're headed across the pond to see data from the UK. Specifically, the Greater London Authority has a myriad of data available at London Datastore . The datasets are generally covered by the UK Open Government Licence (OGL) but some have specific attributions and licenses requirements. So, lets get this out of the way - the data used in this blog post "Contains public sector information licensed under the Open Government Licence v2.0." This treasure trove of information invites a lot of visualizations. But where should I begin? Glad you asked! Turns out there's a catalog of all the data feeds available from the City of London in a handy...
  • Musings on Data

    Finding the Latest Date in Power BI Desktop

    • 0 Comments
    One common question I get is how to do date filtering in Power BI. In a previous post , I showed how to make filters that show if a values occurred in the last 30 days, in the last month, in the last 12 months, or in the current year. This post show how to show the latest date. You can read on below, or grab the data and MaxDateExample.pbix files to see it in action yourself. If you don't have Power BI Desktop yet, you can get it here . Step 1: Import your data set. In this case I have a simple table called Table1 with a column called Date. Just click Get Data and select the excel workbooks (Book1.xlsx). Select Table1 and press the Load button. The data will load and you'll see fields at right on the screen; you can drag them to...
  • Musings on Data

    Don't miss out - See Power BI in action at the Ignite Expo Hall

    • 0 Comments
    Folks, Spread the word. Dawn, our amazing event coordinator, has put together an 'A' list of quick demos to help you get up to speed on Power BI. Here's the list of presenters so far. Please tweet, share, repeat, tell your friends, and be sure to stop by to see these great sessions. You'll find us in front of the large touch screen PPI under the blue Cloud sign. Can't wait! Day Time Demo Title Presenter Mon, 4th 12:00pm 5 minutes to wow, Power BI demo Sanjay Soni Mon, 4th 12:30pm How I bought a car with the help of Power BI Lukasz Pawlowski Mon, 4th 1:00pm You'll be surprised with this topic, and so will we :) TBD Mon, 4th 6:15pm Datazen Mobile Business...
  • Musings on Data

    Visualizing City of Chicago owned properties with Power BI Desktop

    • 0 Comments
    Update 8.9.2015: Title updated with new product name The week after next, I’ll be in Chicago to present at Microsoft’s Ignite conference. I thought it would be fun to look at some data for the City of Chicago to see what I could do with it. Revenue is important to any city. Developing enduring sources of revenue, whether through direct means like leasing city owned property or other ingenious ideas, is something every city planner needs to consider when thinking about where to spend precious resources. Let’s see how the City of Chicago Data Portal ( https://data.cityofchicago.org/ ) helps us. I’ll use Microsoft’s Power BI Designer to do the analysis ( free download ). Before I get too far into it, a...
  • Musings on Data

    Power BI Dynamic Date Filtering

    • 0 Comments
    One question I get from time to time is how to filter to the last week's worth of data automatically in Power BI. You might want the last 7 days, this week, this month, etc. You can make it fully dynamic by combining the technique Matt Masson describes here to create a date table and add to it some Power Pivot DAX functions to classify each date into the date range you'd like. Download a sample here OneDrive personal doesn't load the Power View sheet so download and open the example in Excel on your desktop. EDIT 7.27.2015 - I was asked for the formulas since some folks don't have Power View. These should work in Power BI Desktop , but I've not tested them there. Here you go: Power Query query: //let // CreateDateTable ...
  • Musings on Data

    Seattle Building Permits visualized in Power BI with Socrata OData and data.seattle.gov

    • 2 Comments
    Living in Seattle, it’s quite fun to keep track of what’s happening through data. The Open Data initiative from the City of Seattle is a treasure trove of information that you can explore here: http://data.seattle.gov . This is all power by Socrata , as are many other open data initiatives for Cities and organizations. I wanted to explore that data using Power BI, specifically Power BI designer. In a future post, I’ll show how to monitor insights using the new Power BI Dashboards feature, but that’s for later. I wanted to look at Building Permit data – since there’s a lot of construction happening in and around town. I was able to find the “Building Permits : Current” data set which lists “Building...
  • Musings on Data

    Power BI Designer Preview February 2015 update now available

    • 0 Comments
    Power BI Designer Preview is a standalone tool for building reports for Power BI. I'm using it myself whenever I can to build reports - it has all the new visualizations that the Power BI Preview service ( www.powerbi.com ) has, but in a streamlined client tool. It includes all the Power Query capabilities that are available as an add-in to Excel 2013. If that's not enough, it has the modeling capabilities of Power Pivot, but not all of these are enabled yet in the UI. I has both a 32bit and 64bit version - so you can get the best experience for your computer. Key thing is it's free to download and use. While it's still an early preview, it's also amazing how much you can do in Power BI Designer. Here's where you can download the Power BI...
  • Musings on Data

    Year over Year in DAX - SAMEPERIODLASTYEAR needs a filter scope

    • 0 Comments
    This week I'm working to create a measure to count the total commits in a repository. I thought it was working until I looked at the grand total and found something was off. For the case of Grand Total, the value was mysterious. These were the measures I was using. Total commits YoY% old :=DIVIDE([Total commits]-[Total Commits Same Period Last year],[Total Commits Same Period Last year]) Total commits same period last year :=CALCULATE([Total commits], SAMEPERIODLASTYEAR(DateTable[DATE])) I looked into the reason for this. It's because the Grand Total case does not include any filters. When this happens, [Total commits] gets calculated for "All time" which works out to 2015 and earlier. Then SAMEPERIODLASTYEAR is calculated...
  • Musings on Data

    Power BI and GitHub

    • 0 Comments
    Scott Hanselman has a great blog post showing how he got up and running with Power BI using the GitHub content pack. Great example of how quickly folks can start getting insights on their data in the web or on mobile devices. http://www.hanselman.com/blog/TheNETCoreCLRIsNowOpenSourceSoIRanTheGitHubRepoThroughAzurePowerBI.aspx HTH, Lukasz
  • Musings on Data

    Power BI - Excel sheet data needs Excel Tables

    • 0 Comments
    I've not written a blog post in far too long. Now with the preview of Power BI as a free service (try it here: www.powerbi.com ), I wanted to reinvigorate my blog with a few new posts. As a member of the "customer success team", I get to see what questions people are asking and offering solutions. In the last day or two I've responded to 3 or 4 customers all trying to get the data in the Excel files to be uploaded to Power BI correctly. The most common issue I've seen is that the data in the Excel worksheet is not in an Excel Table. The solution is really easy: on each worksheet, select the data range for your and then press "CTRL + T" to make the data into an Excel Table. You can also use the Table option in the Insert ribbon in excel...
  • Musings on Data

    SQL 2008 R2 RTM! Time to look at some new Execution Log Reports

    • 0 Comments
    Wow.  We just RTM’ed SQL Server 2008 R2.  Check out the official web site SQL Server 2008 R2 Website . Now that the official release is on its way out the door, I thought I’d share some reports that show off some of the new capabilities.  In SQL 2008 R2 there is a new ExecutionLog3 view in the Report Server database.  For those familiar with SSRS, the ExecutionLog table, and then ExecutionLog and ExecutionLog2 views were used in previous versions to show which reports ran on the report server, who ran them, and to provide a lot of information about the execution.  With SQL Server 2008 R2 the ExecutionLog content is expanded again.  The newest version of the log is ExecutionLog3.  You will find it by connecting...
  • Musings on Data

    Charts in a Tablix cell don’t work for me… but I’ve see these nice demos where they do…

    • 0 Comments
    Problem: You may have learned about sparklines or you want to add a chart to your Tablix.  You either copy/paste your working(!!!) chart into the tablix and then you preview and you get: "The chart 'Chart1' has a detail member with inner members.  Detail members can only contain static inner members." Solution: In a Tablix you cannot put dynamic member in a Detail row.  You have to put it into a Group header or Group Footer row. Steps: Insert a table into your report. Set the DataSetName for the Tablix (either in the Properties pane or by dragging a field into the tablix). In Row Groups , right click the (Details1) group.  Select Add Group –> Parent Group… In the Tablix group dialog, Select your Group by: clause...
  • Musings on Data

    High Availability – Frequently Asked Questions about Failover Clustering and Reporting Services

    • 0 Comments
    In SSRS high availability is achieved through a Scale-out deployment that is placed behind a Network Load Balancer (NLB).  The NLB is then responsible for routing requests to Report Server nodes that are responding to requests.  High Availability Documentation Reference : http://msdn.microsoft.com/en-us/library/bb522745.aspx However, many customers are interested in using Windows Failover Clustering with SSRS, since this is how they deploy their SQL Servers.  This article discusses Windows Failover Clustering in regards to SSRS. Windows offers built-in Failover Clustering capability.  SSRS does not natively support Windows Failover Clustering.  The use of Failover Clustering with SSRS service is possible using standard...
  • Musings on Data

    Building a robust setup experience with Reporting Services

    • 0 Comments
    This post is intended for ISVs or Product Development organizations who are planning to integrate Reporting Services into their product.   Over the years I have seen a number of products do this and some get it right and some encounter issues.   The part of the problem I’d like to focus on is recommendations for building your product’s setup/installation program when you know you have a dependency on Reporting Services. When building your setup program, you most likely want your installation to be easy for your users (painless) and result in a successful, working installation for the customer.   This is the right focus to have for the customer, and is achievable.   However, there is risk involved in putting...
  • Musings on Data

    InternalCatalogException, ASSERT, or NullReferenceException when using a Custom Security Extension

    • 1 Comments
    We have seen multiple customers with issues when customizing the Security Extension Sample ( 2005 , 2008 ) that is available on http://www.codeplex.com/ . This is the sample that demonstrates how to create a Forms Authentication solution with SQL Server Reporting Services. There are two scenarios which are the primary sources of the problems: Scenario 1: “Anonymous” access Many custom security extension authors want to allow ‘anonymous’ access – where users do not need to login, or to allow all users some basic level of permission without needing to login explicitly. In order to do this, they change the sample code’s implementation of AuthenticationExtension.GetUserInfo to return a NULL for out IIdentity userIdentity. However, this approach...
  • Musings on Data

    IIS Troubleshooting for SQL Server 2005 Reporting Services

    • 1 Comments
    Over the years, we have seen a number of IIS related issues that cause trouble for users who deploy SQL Server 2005 Reporting Services. We have tried to resolve these in the MSDN Forums on a case by case basis. Now we have compiled the common issues with IIS into a single KB Article along with explanation of why these occur and what can be done to resolve them. Kudos to James Wu on the SSRS team for slogging through the issues and compiling them into this document. How to troubleshoot IIS configuration issues in SQL Server 2005 Reporting Services Take care and good luck, -Lukasz
  • Musings on Data

    Several blogs I would keep on my reading list...

    • 0 Comments
    Recently I have had little time to devote to blogging, but I did want to pass along a couple of references that I tend to provide to customers, partners, and Microsoft internal folks as they dig deep into SSRS. Robert Bruckner has a great blog which offers many tips on SSRS, especially focusing on report processing. The topic I tell customers to reference most often is how to use the ExecutionLog2 View for analyzing and optimizing reports . Dean Kalanquin has just started a new blog. He's a old time SQL guy who can craft any SQL Query you can imagine. He's started a blog and has created a post on how to diagnose and trouble shoot subscriptions which I think would be very helpful, especially to those who have read my previous posts on subscriptions...
  • Musings on Data

    SQL PASS Community Summit 2008 Presentation Links

    • 1 Comments
    Denny Lee and I presented on Wednesday at the SQL PASS Community Summit 2008 on Building SSRS 2008 Large Scale Solutions. The slide deck is available for conference attendees at from the conference site . However, the format is PDF and during the transformation from PowerPoint to PDF many of the links were lost. I have compiled all the links from the presentation deck below for everyone's reference: SQLCAT.com http://blogs.msdn.com/sqlcat http://blogs.msdn.com/mssqlisv http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx Planning for Scalability and Performance with Reporting Services Upgrading Reporting Services (SQL Books Online) Configuring a Report Server Scale-Out Deployment Building and Deploying Large Scale SQL Server Reporting...
  • Musings on Data

    SQL Server 2005 Notification Services Components Package RC1 Availability

    • 2 Comments
    As part of this blog, I comment about Notification Services. I wanted to provide you the following information about the availability of a pre-release SQL Server 2005 Notification Services components package. If you have questions about SQL Server 2005 Notification Services, visit the SQL Server Notification Services Forum on MSDN . You can help improve the final version by submitting bugs to the Connect Feedback Center . Take care and good luck, -Lukasz ---------------------- SQL Server 2005 Notification Services Components Package Availability The SQL Server 2005 Notification Services component package made available in the February 2007 release of the Microsoft Feature Pack for SQL Server 2005 is being updated to include Notification Services...
  • Musings on Data

    SQL 2008 RTM - Where is Report Builder 2.0?

    • 1 Comments
    We've just released SQL Server 2008 (it feels great by the way!) and there have already been many e-mails asking us where to find Report Builder 2.0. You can find an update on the release plan for Report Builder 2.0 on the main Reporting Services Blog: http://blogs.msdn.com/sqlrsteamblog/archive/2008/08/07/report-builder-2-0-release-plan-update.aspx Take care and good luck, -Lukasz
  • Musings on Data

    Reporting Services HTTP 401 (Unauthorized) - Host Headers require your attention

    • 6 Comments
    In a previous post , I talked about issues with repeated login prompts when trying to access either Report Server or Report Manager using your browser. Recently, during an upgrade of our internal test server we identified a new type of HTTP 401 (Unauthorized) message. This post provides a description of the problem and the solution. Symptoms: When all of the following symptoms apply, it could be this issue: A. When accessing Report Manager or Report Server you use a URL that looks like http(s)://<foo>/reports. The name <foo> is NOT the computer name of the computer on which Report Server and Report Manager are deployed. However, DNS or the machine's hosts or lmhosts files (WINDOWS\system32\drivers\etc) are configured to send requests...
  • Musings on Data

    SQL Server 2008 Reporting Services Scalability

    • 1 Comments
    Much has been said up to now about the scalability work the Reporting Services team has done in the SQL 2008 version. However, until now, there were no numbers to back up the architecture discussions. The SQL Server Customer Advisory Team (SQLCAT) just released a Technical Note titled Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned . This is a must read for anyone looking to compare the two products. It also shows that an investment, even now, in SQL 2005 Reporting Services provides a roadmap to higher scalability in the future. Previously, I discussed an approach for Scale Testing Reporting Services that is very similar to the approach used in the Technical Note. Some key notes from the Technical Note: 1) " Reporting...
  • Musings on Data

    Solving the Reporting Services Login issue in the February CTP of SQL Server 2008

    • 1 Comments
    I’ve seen customers run into authentication issues using the SQL Server 2008 February CTP of Reporting Services (you should all give it a try!). This post provides solutions and a detailed discussion of the underlying causes. The material in this post is applicable to all SSRS deployments, beyond the scope of solving the specific issue. Symptom: A) When you access report server or report manager, you get a login prompt multiple times and eventually you get a blank screen. B) When you try to publish reports/models from Business Intelligence Development studio, you get access denied errors. Solutions: 1) Remove RSWindowsNegotiate and ensure RSWindowsNTLM is specified in the file rsreportserver.config 2) OR, change the report server service account...
  • Musings on Data

    Finding Reporting Services Content faster

    • 1 Comments
    Hi everyone - I've been away from my blog for a little while. However, now again I have some time to start blogging again. I get tons of customer questions over the course of a week. I’d like to share with you how I make my life easier by using Internet Explorer’s built-in search box to help me find things faster. Other browsers provide similar capabilities, so you should be able to get it working there as well. The first issue I'd like to address is an issue that has come up a number of times in a number of ways. I first addressed it here . But since then SQL Server 2008 has made a lot of progress and I find I need to find the latest documentation, quickly. How to Add a SSRS Search Provider to Internet Explorer 7: 1. Find the Search...
Page 1 of 3 (53 items) 123