sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009
I’ve seen a lot of interesting sessions and gatherings at industry conferences but this is one of the best ideas so far:
Introducing… The Unconference! …the anti-conference within a conference
Aside from the outstanding value I get from going to hear leading experts share their experience in structured sessions, some of the greatest learning comes from side conversations and informal encounters with people between the sessions. For me, a lot of those conversations happen when I bump into someone whose blog I’ve followed or book I’ve read with just a few minutes to spare between sessions. Unless we chat off-hours, we’re always rushed to get to the next scheduled event. And then to chat with a session speaker after their session to try to get an answer to that one critical question (you know, the main reason you went to the conference)… The way that usually goes is that at the end of a good session, everyone with questions runs up to the front of the room and lines-up to talk to the presenter as he or she packs-up and tries to get out of the way of the next presenter preparing for their session. You’re lucky to get 2 minutes of time with the expert before the next person in line.
The Unconference gives you two hours with the experts and no rules. No rules! This is an outstanding opportunity to spend some quality time with people who have real-world answers and deep insights in their respective fields of interest. For a lot of us, the most important reason for coming is to get quality face-time with the right people
This event is led by Denise McInerney, Data Engineer at Intuit, the agenda is created at the start of the evening. Anyone who wishes to initiate a discussion on a topic can claim a time and a space. Attendees are encouraged to share thoughts in open discussions with community-proposed topics.
If you’re attending the PASS Business Analytics Conference in San Jose, please join us on Friday morning for a special expert panel discussion on self-service BI. It’s sponsored by SQL Sentry and they’re even footing the bill for breakfast! According to Kevin Kline who also officiated the event last year, “I have plenty of people tell me that our panel discussions are one of, if not the most favorite and practical of the event”.
Title: Lessons Learned in Self-Service BI; Friday, May 9, 6:45am – 7:45am, Room 230A
Abstract: For years, when it comes to self-service BI the automatic assumption was the Microsoft Excel was the tool of first and last resort. Now, after many years of enriching the features of the data stack, Microsoft offers many different ways and tools to perform business analytics. Some approaches require heavy involvement from DBAs, SharePoint administrators, and other data and business specialists. Other approaches enable end-users to process their own analytics much more quickly and with less interaction from the IT organization. Which is most effective? Which is the easiest to rollout and maintain? There are pros and cons to each possible approach, as well as hidden and repeating patterns, that are hard to foresee unless you’ve actually been through multiple implementations.
Panel: SQL Sentry’s panel of renowned data analytics and business intelligence experts will discuss a variety of real-world obstacles and accelerators to a strong self-service implementation of business intelligence and analytics that is both useful, effective, maintainable, and inexpensive. This year’s panel includes: Chris Webb (b | t ), Jen Stirrup (b | t ), Paul Turley (b), and Stacia Misner ( b |t ).
You can see the registration page at https://sqlsentrypassbaconference.eventbrite.com or by clicking on the image below. Two quick reminders. One, I know it’s ridiculously early. But the content is always great and you get a high-quality hot breakfast. Two, I strongly encourage you to register since seating is limited and it always fills up.
If you have any questions you’d like to ask a panel like this, feel free to plug them in here. I’ve got plenty of questions queued up already. But I’m always open to more. I hope to see you there!
As I continue to explore the many facets of DAX functions, I’m finding more creative ways to add business value and simplify user experiences. In this post, I will demonstrate how to use slicers to pass values into a calculated measure in an SSAS Tabular model to dynamically change the results, parameterizing the date interval, number of periods and the type of aggregation used in the measure.
The DAX language can be both remarkably easy and deceptively complicated to solve business problems. I’ve spent many late nights working on a problem that just seemed like I was just one step away from solving. When I began using DAX calculations with Power Pivot and SSAS Tabular models for reporting, I thought that it wasn’t possible to parameterize an expression and control the results dynamically like we’ve done with shell dimensions and scoped MDX calculations in a cube. In fact, some of my constituents who are expert multidimensional SSAS practitioners think of DAX as a toy version of MDX for its apparent lack of flexibility and dynamic logic. Using any technology to solve complex problems requires us to work with it rather than trying to force it to behave like another tool. I do have to admit that I was a bit proud of myself for working out a technique to use slicers as parameters in this way but I’m certain that optimizations and enhancements can be made to this most basic example. Thanks to Hrvoje Piasevoli and Darren Gosbell for their ideas.
The example below shows a pivot chart in Excel based on tabular model. The Accumulated Departure Delay measure visualizes the average delay for five selected US airlines for each month in 2013. In this case, for each month, the measure calculates the 3 month rolling average:
The conventional standard in most model designs is to add a measure for every requirement, which can make our models large and cumbersome to navigate. For example, if the business wants to have measurements for “3 month rolling average”, “6 month rolling average”, “7 day accumulated sum” and “14 day accumulated sum”, we would define a separate measure for each of these requirements. Using a dynamic calculation, only one measure is defined and slicers are used to modify its behavior.
Any combination of Period, DateInterval and Aggregate slicer values can be used to dynamically change the results. For example, selecting Period=15, DateInterval=Day and Aggregate=Sum would display the sum of departure delay values for the past 15 days. Since the X axis of this chart uses a Day-Month-Year hierarchy, I can double-click the month of October to make Excel show each date in that month and the rolling accumulation for each day.
Note that all of the calculation logic could be handled in a single calculated measure but while developing a solution, I usually break the problem down into bite-size pieces. It might make sense to leave these as separate measures, perhaps hidden from users, or it might make sense to combine all of the logic into a single, more complex measure expression.
To simplify, I’ll leave the Aggregate slicer out of the scenario and just hard-code the measure to perform sums – and then I’ll change this later. Here’s a pivot table with the Calendar hierarchy on rows and three different measures displayed as values. These measures are Date Range Start, Sum of Dep Delay and Accumulated Departure Delay:
I want to aggregate groups of daily values using a date range starting with a date that is X number of intervals (e.g. days, months, quarters or years) before the current reporting date. In the model, I’ve imported three tables without adding an relationships. These are shown in the bottom right side of the diagram.
Here are the values in these tables:
To resolve the first numeric Period value selected from the slicer, I can used the MIN function. If no values are selected, the first row in the table is returned. If multiple values are selected, the first one is returned and if one is selected, that’s the one returned.
Since the DateInterval column is a text data type, I can’t use MIN but the FIRSTNONBLANK function will essentially do the same thing. Note that there are two required arguments but I’m just passing the value True() since we don’t want to filter any rows. Here’s the script for the Date Range Start measure:
With the dynamic start date sorted out, the actual measure calculation is fairly simple. The Accumulated Departure Delay measure applies the [Sum of Dep Delay] measure filtered using the DATESBETWEEN function:
Accumulated Departure Delay :=
CALCULATE (
[Sum of Dep Delay],
DATESBETWEEN (
‘Date'[Date],
[Date Range Start],
MAX ( ‘Date'[Date] )
)
)
At this point, the pivot table example will work. After connecting a new pivot table to the model, I create two slicers based on the Period and DateInterval attributes and connected them to the pivot table.
Adding the Aggregate element is a variation of the same theme. I’ve added another intermediary measure named Aggregate Dep Delay and then used it in-place of the Sum of Dep Delay measure in the script for the Accumulated Departure Delay measure.
Finally, I add another slicer based on the Aggregate table allowing the name of an aggregate function (e.g. “Sum”, “Average”, “Min” or “Max”) to be selected and used to dynamically change the calculated result. Referring back to the first screen capture, a pivot chart is used with my Calendar hierarchy (consisting of Year-Month-Day attributes) which enables drill-down on the X axis, a filtered set of Airline Carriers is on the legend and the Accumulated Departure Delay measure is added to the chart values.
The 2014 PASS Business Analytics Conference is just a few weeks away and you know you want to go to see top speakers give over 70 sessions on how to use all the new BI tools and technologies that could make you the most important person in your organization. This year the PASS BAC is May 7-9 in San Jose, California. You can save $150 by using this discount code provided by the Oregon SQL PASS Chapter: BA4K8P. Enter this when prompted on the registration page.
Aside from the great content, the greatest value I get from these conferences is networking with community leaders, authors, speakers, attendees and product developers. Please stop me and say Hi
We’re on our way home from Lisbon, Portugal after speaking at SQL Saturday #267. Having had the pleasure to attend and speak at quite a few SQL Saturday events over the years, I’ve never seen a group of event organizers work so tirelessly with as much passion and dedication. Thanks to Niko Neugebauer, Vitor Pombeiro, Paulo Matos, Quilson Antunes, Murilo Miranda, André Batista and Paulo Borges for the late nights and long hours you all invested to make this a very special SQL Saturday. The event was a great success; as well as a special day of sightseeing the day afterward for all the speakers. After recruiting an impressive list of speakers from all over the globe, these volunteers went well beyond the call of duty to chauffer speakers from the airport to hotels, the event venues and around the city. It was quite a treat. Thank you!
If you are attending the preconference session on Building a Complete BI Solution on April 11 in Lisbon at SQL Saturday #267, I will provide all of the project and data files that I will be using at the beginning of the session. You are welcome to follow-along with the finished project files but this is not a requirement to attend the session. You are also welcome to simply attend and take notes and then you can download the samples later.
We will be using a fairly large volume of data for a laptop machine which increases the system requirement but you can load a subset of the data files if you prefer. There are two different demo solutions and the system requirements are different for each.
system requirements
participation options:
watch & take notes
download workshop files & work with them later
follow along with all or part of the demonstrations, using the finished solutions*
Personal BI solution
Office 2013/365 ProPlus edition
Power Query
Power Map
Enterprise BI solution
SQL Server 2012 Developer edition
all features installed
SSAS tabular instance
SSRS in native mode
20 – 40 GB of disk space
(whether you load part or all data)
* please use provided solutions rather than building as you go. we can’t provide support or troubleshooting for your development environment.