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
As I visit businesses, consulting clients and training classes, to teach data reporting and analytics; there is a recurring conversation. It is one that I have had for eighteen years. The audiences change and the technology implementations are a little different over time, but the essential conversation is still the same.
This happened again last week as I was trying to explain the unique characteristics of Multidimensional and Tabular storage to a client. I’m developing a training course where I needed to explain the concepts once again – and then it hit me! …these unique data storage and analytic technologies do what we dream about in science fiction stories and achieve capabilities we imagine existing in the distant future. Channel surfacing on television this week, I watched an episode of Timeless, a Star Trek rerun and a Star Wars movie – where time-travel, space-warp travel and teleportation were commonplace realities. Although fictional as they were portrayed, I think these concepts are very real in our technology landscape. Please indulge me as I explain.
We live in a Linear world. We reside in a place, work in a place, store and move stuff from place to place. Centuries ago, if man needed to move something (perhaps just himself) to a distant place, he would walk, ride a horse or take a sailboat. In weeks or months, he would arrive in another place. Today, we get in a car, navigate the streets, perhaps highways and Interstates and then arrive in a different place within minutes or hours. For longer trips, we board a large metal tube, sit very close to several complete strangers as we climb up into the sky; some period of time goes by and then we somehow arrive in a distant place along with our stuff. At 35,000 feet where the air is very thin, a jet airplane can travel many times faster; leaving an altitude where certain physical laws restrict speed, only to re-enter that atmosphere after it has arrived. To someone from the distant past, this experience would seem fictional.
image: nasa.gov
On a daily basis, I sit or stand in front of my computer monitors, and see and speak with people in distant places. We have live conversations about the weather in their time zone or hemisphere. Through some strange but now common process, technology converts our speech, images, directions, documents, effort and thoughts into 8-bit network packets and electrons that travel almost instantly through space; leaving the earth for a short period, to be transported and reassembled somewhere else.
Years ago, when I wanted new stuff, I would drive to the store. If they didn’t have what I wanted or if it was too expensive, I would spend the day driving to different stores. Today, I “go” to the store on my computer and the very next day, my stuff arrives on my doorstep. We are continually inventing ways to bend space and teleport things within the changing confines of reality.
Data storage is much like the real world. We store terabytes and petabytes (numbers we can’t even explain) in a small space. But, to navigate through the linear storage structure of relational databases and files, the data might as well be thousands of miles or kilometers apart. In order to perform time-variance and statistical calculations, program code must access a group of records in one location to aggregate them, and then find a related group of records – perhaps millions of rows apart – to perform another aggregation. The query might need to perform this operation dozens, hundreds or thousands of times; before it can calculate the final results. One of the best examples is a time-series calculation where a query must “visit” records for each time period to perform an aggregation. The effort is compounded when the goal is to compare aggregate values and variances for parallel and relative periods (e.g. “one year ago”, “three years ago”, etc.) and then perform forecasting or regressions.
image: ctdn.com
In relational storage, the query engine must “drive” from place to place, navigating the streets (file groups, files, pages and rows) to find the records. In an analytic structure (like an Analysis Services cube, or SSAS Tabular model or Power Pivot/Power BI in-memory model), the query engine performs a calculation operation and then teleports to a new location to find a different group of related records.
image: space.com
In a multidimensional cube, the storage processing code quite literally folds space to create the cube structure and pre-calculate some of the aggregate values. Rather than leaving all the data in a linear table, it actually moves data segments close together, reshaping the data structure (thus, the term “cube”). This approach required a great deal of complexity in both the software and the resulting data structure. Advances in computer hardware and software techniques helped simplify the approach. The in-memory tabular model exists in linear, yet fragments of compressed space – where the actual distance between adjacent records is much closer than it appears to be. When the query process is done looking up a group of records it warps to the next group of related records using a memory address. Instead of traveling through space, it steps out of it, like the jet airplane leaving the atmosphere, to re-enter in a different location. It takes much less time to run queries because it is not moving through all the rows of linear tables.
By using unique and innovative data storage methods (to reduce storage space and access data faster without moving through data), we can, in effect; perform time-travel, warp space and teleportation.
This post is a teaser for an article I just published to SQL Server Pro Magazine. This is the third and not the final article about using Datazen to build mobile dashboards and business intelligence solutions. The entire article is available here.
(excerpt)
A few months ago, shortly after Microsoft announced their acquisition of the Datazen mobile dashboard product, I wrote two articles titled Getting Started with Datazen and Setting Up a Datazen Server. Datazen is a simple dashboard tool with native apps for all the major mobile device platforms. I mentioned that the tool had shortcomings with Analysis Services as a data source. When writing MDX queries, it was necessary to create calculated members for all of the row members and place them on the columns axis. An update released in August, 2015 improved the MDX query functionality where the work-around is no longer necessary. In this article, I’ll demonstrate a pair of drill-through dashboards using MDX queries and navigation that pass parameters to an MDX query. The is an overview rather than a tutorial. My focus is on queries, parameters and drill-through commands and not so much the dashboard layout. In the next article, I’ll show you how to go one step further and drill-through to an operational report created with Reporting Services.
A Quick Tour
To set the stage, my dataset is the airline on-time performance database from the Bureau of Transportation Statistics…
I’m pleased to share the first two articles in a series for SQL Server Pro Magazine. Here’s a short excerpt from each with a link to the full article:
Getting Started with Datazen, Microsoft’s New Mobile Dashboard Platform (Part 1)
Microsoft Datazen is a new tool from Microsoft built on a mature and time-tested foundation of enterprise services and visualization components from Component Art. Interactive, touch-friendly, analytic dashboards created by data professionals are available to users on desktops and practically all modern mobile device platforms.
Getting Started with Datazen
The story you are about to read is going to sound too good to be true but it is true and it’s awesome.
About two years ago, I was helping a big consulting client evaluate mobile BI dashboard options for their Microsoft BI solution. A friend on the BI platform team at Microsoft asked for my thoughts about a new mobile dashboard tool called Datazen. My initial impression was that the product needed to mature to meet my client’s needs but the interactivity and visual experience was stunning. Half joking (in a Freudian sort of way), I told him that Microsoft should buy the company and finish integrating it with their BI platform. Imagine my delight when I learned last month that it actually happened.
Last month Microsoft announced that they had acquired the DataZen mobile BI platform and were adding it to their enterprise BI product suite. This is very exciting news that rounds out the Microsoft BI capabilities of their entire product portfolio. What’s more, is that they plan to make if free for SQL Server Enterprise customers who have Software Assurance agreements.
Watch for a series of articles about getting started with DataZen in SQL Server Pro Magazine online.
I presented a session at the PASS Global Summit in 2013 showcasing DataZen called “New York, London, Paris Munich; Everybody’s Talking About Mobile BI”. A product team leader in the Microsoft BI Visualization team had introduced me to DataZen and recommended I take a look. This led me to recommend that we evaluate the product for one of our enterprise BI clients and to talk about my experience in the conference session. My response to the Microsoft team was to recommend that they acquire DataZen. Whether I actually had anything to do with this product acquisition or not, it does give me a sense of satisfaction to know that it is now part of the Microsoft family.
So, what, exactly is DataZen and what are its capabilities and challenges? Continue reading →
I’ve just finished a series of four articles for SQL Server Pro Magazine, along with sample projects and hands-on exercises. The series will take you through SSAS Tabular model design from start to finish, using the Adventure Works sample data in SQL Server 2012 or 2014. Here are links to all four articles followed by an excerpt from each.
Starting Your Modeling Career with Analysis Services Tabular Models Part 1
Four-part series
This is the first of a four-part series about getting started with Tabular model design using SQL Server Analysis Services in SQL Server 2012 and 2014. You will learn the basics from start to finish and build a complete solution. A sample project is provided for for each stage of the solution so you can follow-along with each article. Continue reading →
One of the characteristics of a really good, classic movie is that it has a lot of memorable dialog. I could go on for hours quoting one-liners from The Blues Brothers or Princess Bride. Likewise, I think a good book leaves the reader with gems to ponder and to stimulate ideas. Such has been my recent experience reading Rob Collie’s “DAX Formulas for PowerPivot, The Excel Pro’s Guide to Mastering DAX”. Continue reading →
I really enjoy this topic. One of the reasons, I suppose, it’s such a hot topic is that a lot of people believe that there is a perfect ad hoc reporting solution out there, just barely beyond the current set of capabilities in all of the available tools. Continue reading →
There are many free eBooks & resources available from Microsoft and members of the MVP community. This is a collection of several very useful free publications:
Power View Infrastructure Configuration and Installation Link
Introducing Microsoft SQL Server 2012 PDF | EPUB | MOBI
Introducing Microsoft SQL Server 2008 R2 PDF | XPS
The 13th edition of The SolidQ Journal has just been released with several insightful articles and continued series updates. In this edition:
Fernando G. Guerrero: editorial deals with Microsoft giving new wings to an entire industry
Laurent Martin: static relational interval trees, an article based on real customer needs
Gilberto Zampatti: best practices article on how to create and configure SharePoint service applications with PowerShell
Rushabh Mehta: continued series about the SSIS management framework
Paras Doshi: concluding article in the series on how to tune SQL Azure DBAs
Pablo A. Ahumada: how to design a dashboard using PerformancePoint Services.
Salvador Ramos: about how to use Excel in ETL processes
Herbert Albert and Gianluca Hotz: new series about using PowerShell to automate Excel tasks
The SolidQ Journal is a free publication from SolidQ to support the Microsoft SQL Server and Business Intelligence community. Follow #SolidQ on Twitter.
This month’s SolidQ Journal contains a wealth of insights and useful information, including an article from yours truly. Click here to access the journal.
Editorial
Windows 8 and Kinect: Let’s Start Again Fernando G. Guerrero
News & Events
Upcoming Courses
Business Intelligence
Give Us Your Data! Windows Azure Marketplace DataMarket and the Open Government Data Initiative Miguel Lopez
Let’s Get Visual: The Art of Report Design Paul Turley
Building an SSIS Management Framework – Part 1 Rushabh Mehta
Relational
Tuning SQL Azure Databases – Part 1 Paras Doshi
Collaboration
SharePoint Best Practices Creating and Configuring Service Applications with and without) PowerShell – Part 1 Gilberto Zampatti
Database Administration
Pump Up with PowerShell Herbert Albert and Gianluca Hotz
This article is based on my session at TechEd on May 18th, 2011
Report styles vary depending on the needs of the consumer and purpose of the report. With so many capabilities in current reporting toolsets, report designers are often prone to add graphics, color and “chrome” wherever they can. This article presents some ideas, concepts and guidance for the visual aspects of report design.
Although the statistical claims vary greatly from one study or observation to another, a consensus remains that the majority of the population are visual thinkers. Even if the information isn’t specifically graphical in nature, visual thinkers often associate concepts and facts with some sort of imagery. It stands to reason that if a large portion of the population processes information as imagery, as report authors it may be more effective to communicate in the same manner. Is this the best approach in all reporting scenarios?
While working on an analytical reporting project for a division of Boeing, I learned an important lesson. The client represented a business unit that produces aircraft and weaponry for the United States Air Force. The users were technically adept and either accountants or engineers who managed very complex projects dealing with highly-technical components. We were working with Microsoft Consulting Services to propose a replacement for an outdated “green screen” reporting system. As I prototyped replacements for their static, columnar reports, I made it a point to use graphics, color and thematic styles. The client immediately disapproved and pushed back on the report designs. They told us that “serious reports aren’t pretty.” I was reminded that it was important to understand the perspective of the audience and their needs before moving to design. This project was an exception to the rule where the only acceptable medium for a purely analytical reporting solution was a collection of non-graphical reports.
Principles of Report Design
Most all design projects include a balance of three basic principles.
There should be no argument that any business report must have business value and should deliver accurate results that answer key business questions. A seasoned report designer understands the need to ask thorough questions and document specific functional business requirements, which ultimately become report solution deliverables. In formal projects, this is usually the job of the Business Analyst who possesses functional business skills. The report solution developer possesses technical development skills that are used to translate the functional design and business requirements into reports that offer features that enable users to navigate, view, print; drill-up, down, through and otherwise deliver information in some creative fashion. If reports fit the business requirements and function in such a way that users can get access to the data they need, what does artistic form have to do with reporting or solution design – and is it really important?
Styling & Color
Styling and image are more important than many technical experts realize. The role of Report Designer is often assumed by seasoned programmers who have little aptitude or concern for such things. On the other hand, some designers; regardless of their aptitude or skill set are easily prone to obsessive concern to get the design “just right”. Many of us have invested many long hours tweaking properties and details at the last minute before delivering a report, dashboard or application form. Needless to say that some of us have minds that are just wired to think visually while others are not.
Aesthetic form and styling are important elements of design and the degree of importance depends greatly on the audience and business goals of the solution. This is often a delicate balancing act so many report designers simply play it safe by duplicating common designs and using standard palettes and template styles.
Establish standards before report design
Nearly every organization has a standard color palette used to define their brand. Many companies enlist marketing firms and professional graphic designers to help them create a marketing image with standard colors, fonts and graphics. In a competitive marketplace, many organizations have re-branded to modernize their image and help customers easily recognize their offering. This effort often follows decades of reorganizations, acquisitions and market redirection. For many, this seemingly simple effort to present an easy-to-recognize company image can be critical to their survival in the marketplace. Designing application user interfaces and reports aligned to these standards may also be critically important but it’s not enough to just change the background and border colors to match corporate standard colors and slap the logo into report title heading. There’s a lot of art and science behind color selection and figuring out “what goes with what”. Nearly all recognizable companies enlist the services of well-paid experts to craft their brand image.
I recently spent an evening with my wife and girls at a local home improvement store helping them decide on new paint color schemes for rooms in our home. I honestly didn’t have much of an opinion to offer but I did learn that “Aqua Breeze” goes with “Hallowed Hush” when using “Ash White” for the trim. I also learned that “Blackberry Harvest” is compatible with “Raspberry Mousse” but cannot be combined with “Glacier Lake” or “Dragon Fire”. I read that “warm” colors make the room look larger – or smaller… I can’t remember. I’m happy to leave color selection to experts like Martha Stewart. If you are color inept like I am, you may also find a site called ColourLovers.com helpful. Among other things, this is a forum for people who like to mix colors and showcase their coordinated palette choices, as you can see in the following samples.
Aside from thematic styling; color can be an effective way to differentiate values in the context of a report visual; like a chart, graph, gauge or grouped table. It can also be a means to call out exceptions and alerts. The balance of these elements is the essence of an effective report design.
Different styles of reports serve different purposes. For example, operational reports are often used to deliver transactional details in textual, columnar format. By contrast, an analytical dashboard is intended to deliver “the big picture” of the business by surfacing aggregate values and exceptions so business leaders can become immediately aware of trends and concerns so they can take appropriate action. Where necessary, users should be able to drill-down, beyond surface metrics to explore details and understand the cause for alert conditions.
Please Don’t Scream
We live and work in a world filled with increasing noise and clutter. Take a quick look at the following picture and ask yourself what you see. I see a lot of people trying to get my attention by yelling just a little bit louder than their neighbor. Unfortunately this is the same approach used by many report designers to draw attention to important details.
Rather than yelling louder, try reducing the noise so the important message can be delivered without giving your users a headache. Bring important data to the surface and drop less-important elements to the background. In particular, default borders, backgrounds, shadows and 3-D style effects are common place and should be reduced or eliminated if they compete for screen space and the user’s attention.
I’ve gone back and forth in my attempt to use background colors on dashboards and there isn’t one design that works for all. Most professional web sites use white backgrounds simply because all text and images work well against it. However, a school of thought for dashboards that will be used for long periods of time is to use black or a dark background color so the user isn’t forced to process all the bright white background as they look at it.
The Five Second Test
As a rule, use color, shapes and contrasting shades to surface the most important information. For analytic dashboards, use the five-second test to check the effectiveness of the design. Prepare to display the dashboard report, ask the use to look at it for five seconds and then remove it from display. Ask the user to describe the information they saw using only the five-second snapshot they took in their mind. If they can describe the most important information the report was intended to convey, then the design is a job well done. If they remember background colors, borders, lines and other styling elements before they got to the data; you may have some redesign work to do.
The matrix report shown below may contain a lot of useful information in an operational reporting scenario but it fails the five second test.
The sparkline report in the following figure, which might be only a small region of a larger executive dashboard, provides useful trend information at a glance. A click-through feature in this report could enable the user to view more detail and expand on the context of the trend to gain more insight.
Use subtle colors for styling
Stephen Few is well-known for his writing and expertise on this subject. In his book “Information Dashboard Design”, he states that by using subtle variations of color shades between visual data points, the consumer can easily discern different values and thresholds without unnecessary clutter. People can generally recognize 16 different shades of the same color. This leaves a lot of room for contrast between, let’s say, five to eight different shades of the same color. To obtain different “shades” or levels of saturation for the same color, equal proportions of red, green and blue are mixed in any quantity to produce a darker or lighter shade of the same color. The convenient thing about this technique is that it translates well to gray scale. The images below show how shades of the color #A0C5DB convert to gray scale to accommodate black and white printing or color blind users.
Use bold colors for alerts & exceptions
The use of heavy borders, solid black and bold colors – if exceptional – will draw the user’s eye and rise to the surface as important information. This is an appropriate theme for Key Performance Indicator (KPI) alerts, traditionally displayed in red, yellow and green. In most cultures, users relate these colors to traffic signals where green means “everything’s OK”, yellow means “things may be heading in the wrong direction” and red means “stop and take notice – something is wrong here”.
Combine Colors and Shapes
The traffic signal metaphor works well for KPI states as long as the reader is able to discern colors. This might be a challenge if a report were printed in black and white or if the user were color blind. Compare the green, yellow and red indicators in the following example with the gray scale versions in the graphic below.
By combining color with shapes, the problem is resolved as long as we inform the user that icons of a certain shape consistently represent a specific KPI state. For example, the round green, triangular yellow and diamond red shapes in the following example can be discerned by users who understand that circles are good news and diamonds are bad.
Putting It to Use
Designing a report in SQL Server Reporting Services can be easy of you use default styling elements. However, Reporting Services has little support for thematic styles and style sheets to coordinate colors among different design elements. Some day we may have the ability to use style sheets in Reporting Services but until then, you have to do some manual work to design a well-crafted report with consistent styling. I’ve seen many different techniques used to apply style sheet-like themes via information stored in database tables and property expressions. In my opinion, this is a lot of work that may save time and energy on large-scale projects but may be overkill in most cases. My approach is a simple custom function that allows you return different shades of a single color to different report elements and properties in a report. The following code can be stored in a report definition, to be used as a template for new reports; or in a custom .NET assembly referenced by all the reports in a business solution. As an external assembly, the code may be written in C# but as an embedded function, it must be VB.NET, as shown in the example (line continuations are used to format code for the printed page).
‘ Creates colors in different shades within the same color palette, ‘ mixing each RGB value in the same proportion in percentage to either ‘ black or white color scale. ‘ Paul Turley 5/19/2011 ‘ ‘ Enter 3 arguments: ‘ Comma-separated string for the RGB value of the color as "RRR,GGG,BBB" ‘ or hex color as #RRGGBB ‘ Percentage as a whole integer (0 to 100) ‘ "Black" or "White" to indicate the color scale range. ‘ 0 percent will return either black or white.
Function RGBToHexPalette(ByVal PaletteRGB As String, ByVal PalettePct As _ Integer, ByVal PaletteScale As String) As String
Dim sNumString As String() Dim InR As Integer, InG As Integer, InB As Integer Dim OutR As Byte, OutG As Byte, OutB As Byte
If PaletteRGB.SubString(0, 1) = "#" Then InR = CInt("&H" & PaletteRGB.SubString(1, 2)) InG = CInt("&H" & PaletteRGB.SubString(3, 2)) InB = CInt("&H" & PaletteRGB.SubString(5, 2)) Else sNumString = Split(PaletteRGB, ",") InR = CInt(sNumString(0)) InG = CInt(sNumString(1)) InB = CInt(sNumString(2)) End If
A base color may be passed as either a comma-separated string of RGB values or as a standard hexadecimal color value. For example, pure Magenta is expressed as “255,0,255” or “#FF00FF”. The second argument is a percentage of color saturation between that color and either black or white, indicated in the third argument as values “Black” or “White”. If the function were called with the value “255,128,255” or “#FF80FF” (a light Pink Magenta) with “50” for the second argument and “Black” for the third, a dark version of the color value would be returned. The saturation level of that color is half-way between the base color and pure black.
Here’s a simple example of the function applied to several report element background and foreground color properties. I’ve used the SolidQ corporate standard colors #A0C5DB and #CBDFEC to create various shades. The example below shows the Color property of a line chart series color set using the RGBToHexPalette function. In this case the chart line will be a shade half way between the base color and white.
The final report applies various shades of these two colors to several elements; including the report header, table header, group header and page footer background and text. Custom colors are also applied to all of the field text, chart lines and bullet graph ranges and pointers. All of the colors in the following report example are set from expressions using the RGBToHexPalette function based on one of the two base colors mentioned before.
Too Much or Too Little?
Graphical report design is a balancing act between the simplicity of no-frills design and the extremities of over-done graphics and colors. This balance changes for reports of different styles and purposes. Advertisements and billboards are bold and artistic in an effort to grab the viewer’s attention to make an impression for a brief moment. Reports and user interfaces that a business user will utilize day in and day out should put fit and function well above form to serve the need without distraction and unnecessary clutter.
What’s in a Metaphor?
Graphical report elements can mimic tangible measuring instruments and devices in the real world. At times, users relate to these metaphors because they seem familiar to their experience with real devices. However, these components are often chosen purely based on their “cool” factor and can be a very ineffective use of screen real estate. Reconsider using glitzy report items that show more fluff than substance. Many of the industry experts, like Tufte and Few, argue that there is no place for such glamorous visual symbolism in reports and dashboards. I think there’s a time and place for everything and there are exceptional situations where these may be an appropriate choice. Consider whether users will find them of value after they have been using the same report after several initial uses.
3-D Effects
Three dimensional charts are usually only effective when designed with the data points that will be displayed in the final delivery. As an advertising or presentation medium, this may work but when the data changes, they can quickly become useless. As a case in point, consider the chart on the right, below. What were the sales totals for France, UK, Australia and Germany for 2001 and 2002?
In a clustered bar chart like this one, if the bars in any row are larger than those in front, they are hidden from view and the data become unreadable.
Conclusion
There is much to be considered when designing visual reports. Above all, be mindful of the business value offered to the user. If visual elements, colors, styling features and graphics support these objectives then the design may be sound. Otherwise, too much visual clutter can distract the user from the core message and the most important information on the report or dashboard. In summary, consider the following guidelines as a checklist for visual report design:
Use screen space effectively, leave white space to make the content readable
Reduce the noise – bring the most important information to the surface
Keep it simple but don’t insult the user’s intelligence by over-simplifying
For the remainder of this article, I will demonstrate query and report design techniques by progressively building a report, adding steps and features. The examples will focus on the capabilities of the query and results, rather than the visual design of the report. This report could easily be enhanced with groups, sparklines, gauges a geographic map or other visuals. I trust that you can find the most effectively way to visualize the results on your own. We’ll start with a simple query and then layer additional features.
1. Start by creating a new report in either a Business Intelligence Development Studio (BIDS) report project or the newest version of Report Builder. Report Builder 2.0 can be used for SSRS 2008 and Report Builder 3.0 or newer may be used for newer product versions and is backward compatible with SSRS 2008.
2. Add a data source that uses SQL Server Analysis Services and connect to a database server containing the Adventure Works sample SSAS database.
3. Add a new embedded dataset to the report and open the Query Builder.
4. Click the left-most toolbar button, labeled “Edit as Text”. This will allow you to type the query rather than using the graphical query builder.
5. Enter the following query script and then execute the query using the ! button on the toolbar.
select {[Measures].[Internet Sales Amount]} on columns
, NON EMPTY [Date].[Date].members on rows
from [Adventure Works]
;
Figure 2 shows the Query Designer with the MDX query script and execution results.
Figure 2
To get more business value from the query, we’ll design it to show a specified range of dates. This is done by adding the LASTPERIODS function to return 30 days of results, up to and including a specified date.
6. Replace the expression “[Date].[Date].members” with the function name and a set of parentheses. The LASTPERIODS function takes two arguments separated by a comma; the number of periods and a member reference. The easiest way to add this date member reference is to use the metadata pane on the left.
7. Expand the “Date” dimension and then the “Date” attribute hierarchy.
8. Expand “Members” and then find “January 31, 2003”.
9. Drag and drop this member to the right of the comma so it looks like this query script:
select {[Measures].[Internet Sales Amount]} on columns
, NON EMPTY LASTPERIODS( 30, [Date].[Date].&[20030131] ) on rows
from [Adventure Works]
;
Execute the query to check the results. The results grid should include 30 dates ending with January 31, like you see in Figure 3.
Figure 3
Adding a Calculated Member
Next, we’ll add a calculated member that will return the Internet Sales Amount for a period twelve months prior to the current date period. This value will be returned as a member named “TrendValue”. In the following example, the “with” clause defines this member. The earlier date is returned by the ParallelPeriod function which takes as arguments a hierarchy level, the number of prior periods (12 months), and a reference to the current member of the Calendar hierarchy. This expression will be processed for every Date member on the rows axis, to return a corresponding trend value.
10.Add the “with member” expression shown in the following example to apply the ParallelPeriod function as described.
11.Modify the “on columns” expression to return the TrendValue measure as shown.
12.Compare your query script to the following example and use the Execute (!) button on the toolbar to test the results.
with
member Measures.TrendValue as ([Measures].[Internet Sales Amount]
, ParallelPeriod([Date].[Calendar].[Month], 12
, [Date].[Calendar].CurrentMember))
select
{[Measures].[Internet Sales Amount], Measures.TrendValue} on columns
, NON EMPTY LASTPERIODS(-30, [Date].[Calendar].[Date].&[20030131]) on rows
from
[Adventure Works]
;
Take a look at the results returned in the Query Designer. The first row shows that the Internet Sales Amount for the 31st of January, 2003 was about $17,468.54. The TrendValue is the Internet Sales Amount for the 31st of January, 2002; which was about $21,762.16.
Figure 4
The next step will make only a slight modification but will enable the query to be more flexible when we start using parameters in the next section. In the previous examples, the Internet Sales Amount was hard-coded as the first measure. In this version, we will pass the measure name as a string and return the value as a member called “Selected Measure”. Later, we’ll pass the measure name using a parameter so the report will analyze any measure the user chooses.
13.Modify the query script, adding another “member” clause for the Selected Measure, as shown in the example.
14.Execute the query and check the results.
with
member Measures.[Selected Measure]
as [Measures].[Internet Sales Amount]
member Measures.TrendValue as ([Measures].[Internet Sales Amount]
, ParallelPeriod([Date].[Calendar].[Month], 12
, [Date].[Calendar].CurrentMember))
Select
{[Measures].[Selected Measure], Measures.TrendValue} on columns
, NON EMPTY LASTPERIODS(-30, [Date].[Calendar].[Date].&[20030131])
on rows
from
[Adventure Works]
;
The results in Figure 5 show that the column name has changed.
Figure 5
At this point, the report designer has enough information to generate the field metadata and in subsequent query changes this metadata will remain unchanged. This is an important thing to understand because when the complexity of a query reaches a certain point, it can be more difficult to go back and make changes to things like field names. For this reason, it’s important to establish this baseline and lock-down the fields returned by a dataset. It’s also a good idea to save a copy of this query in a separate script file for reasons that will soon be more obvious.
Before moving on to the next step in the query design, I’d like to build a simple report interface. Add a line chart to the report body. Add both of the measures as chart series values. Setup the chart to be grouped by the Date field as a category group. In a line chart the category represents the horizontal axis and values are plotted along the vertical axis scale.
15.Add a line chart to your report
16.Drag the two measures to the Values list in the Chart Data pane
17.Drag the Date field to the Category Groups list
Your chart report should resemble Figure 6.
Figure 6
Here’s a common MDX report flaw that is often overlooked in design and often gets past unit testing. Most dimension members return string values. This isn’t always the case but it’s more common than in relational solutions. In our case, the Date attribute returns string values and not actual date values, such as “January 31, 2003” and “February 1, 2003”. These members are already sorted in the right order in the query result set but if the category group were sorted on these field values “February 1…” would appear before “January 31…”. By default, the report designer adds a sort expression when a group is created. As a rule, you should always check the groups for any data region and remove the sort expressions when designing reports with MDX datasets.
18.Preview the report and take note of the date order on the horizontal axis. We expect the dates to be in the wrong order.
19.Correct the automatic sorting issue by modify the category Date group
20.Delete the sort expression for the group.
21.Preview the report again and verify that the dates are sorted correctly.
You can adjust the design to your liking. I’ve modified the horizontal axis properties and set the Interval property to 1. This will show every date on the axis. Since there are so many, the text will rotate 90 degrees to make room for all the date members. I’ve also remove the axis labels, set the chart title and formatted the vertical axis scale to clean things up. The report looks like this in preview:
When an MDX query is created using the graphic query designer, parameters can be added automatically along with the datasets needed to drive the parameter list values. In our case, when writing the query manually, it’s necessary to do this work yourself. One convenient option is to use the graphical query designer to build the field metadata and the parameter lists and then modify the query script using manual techniques. This will work as long as the field order, names and hierarchies don’t get changed.
In our case, we will add and setup the parameters manually. Let’s go back to the last version of the query and look for opportunities to extend the capability of the query and report with some parameters. The query has three values hard-coded into the logic. We can make this query much more flexible by making the following changes:
Alias the Internet Sales Amount as an aliased member named Selected Measure. The ability to change out the measure at will by using this placeholder provides a great deal of flexibility. This means that one query could be used to measure practically anything in the database.
On the Rows axis, the first date for the range is hard-coded. It would be preferable to allow a report user to pass in any date of their choosing and then to decide how many dates will follow the first date member in the specified range.
Let’s implement these changes. Start by adding the following three parameters to the report. Just use the default for any other property values.
1. Add the following parameters to the report. In the Report Data pane, right-click the Parameters node and choose the menu option to add a parameter.
2. Enter the appropriate name and properties for each new parameter and then close and save the each parameter with the properties shown in the table.
You’ll open and close the Parameter Properties dialog for each of these parameters:
Parameter Name
Prompt
Data Type
Default Value
MeasureName
Measure Name
Text
Internet Sales Amount
LagMonths
Lag Months
Integer
12
FirstDate
First Date
Date/Time
1/31/2003
My example for the FirstDate parameter default value uses a US formatted date. If your machine is configured for a different locale, use a date format appropriate for your region.
Passing Parameters
Since we’re building the dataset query manually, the parameters will be referenced right in the query script and there is no need to map report parameters to query parameters like you normally would if you were using a common T-SQL or MDX query. There are a few different approaches used to customize a query using parameters. Under some conditions you can simply embed a query parameter reference, prefixed with the ‘@’ symbol, into the query script but the query parser is a bit restrictive about the components of a query that can be parameterized. If your mission is to use parameters more creatively than these restrictions will allow, you can use an expression to convert the entire query into a string value. In exchange for this effort, you will have a query that can be used to control aspects of the report that would otherwise require starting over with a different report design. The trick with the technique is to write a query first to return all of the members and measures. Executing this query builds the report’s field metadata. After that, you can use code and expressions to customize the query as long as it continues to return results consistent with the metadata schema (in other words, all of the fields are returned using the same field structure.)
Building a Dynamic Query
To serve our purposes, the entire query will be converted to a string using a Visual Basic.NET expression.
3. In the Report Data pane, double-click the name of the dataset.
4. In the Dataset Properties dialog, click the Expression Builder button next to the Query box.
The Expression Builder button has an “fx” on it, which stands for “Expression Builder”. No, really, I think it actually stands for “Functions and Expressions” or something like that. Anyway, click that button.
5. Modify the query expression so it becomes a series of concatenated strings. You can use the ampersand (&) to combine each literal string or expression.
Every element of the query is a literal string except for the parameter references. In the following example I’ve shortened some of the strings into separate lines so they fit on the printed page. You’re welcome to simply this if you chose to but you can copy it verbatim as a working starting point.
& ” {[Measures].[Selected Measure], Measures.TrendValue} on columns ”
& ” , NON EMPTY LASTPERIODS(-30, [Date].[Calendar].[Date].&[”
& Format(Parameters!FirstDate.Value, “yyyyMMdd”) & “] ) on rows ”
& “from ”
& ” [Adventure Works];”
Handling Date Parameters
Working with date parameters and dimensions can be a bit tricky. To be successful, it’s important to understand some of the common unique differences between working with date values in SQL and the way that date values are represented in an OLAP dimension. The fact is that there is no hard-and-fast standard for all date attributes but there are some common approaches used by most experienced SSAS practitioners. In the Adventure Works sample cube, the Date attribute’s key value is an integer converted to a string using the format: yyyyMMdd. For example, the 9th of January, 2011 would be 20110109. Since the FirstDate parameter is a trued Date/Time data type, it would be converted to the intended format using the Format function using the appropriate format mask.
Parameterizing Measures
We have one more enhancement to make and then we’ll be done. Since we’re already focusing on the query design, let’s make this change and wrap it up.
6. Replace the literal measure name in the Selected Measure calculated member expression with a reference to the MeasureName parameter, as you see in the following example:
=”with member Measures.[Selected Measure] as [Measures].[”
& ” {[Measures].[Selected Measure], Measures.TrendValue} on columns ”
& ” , NON EMPTY LASTPERIODS(-30, [Date].[Calendar].[Date].&[”
& Format(Parameters!FirstDate.Value, “yyyyMMdd”) & “] ) on rows ”
& “from ”
& ” [Adventure Works];”
7. Close and save the expression and the Dataset Properties dialog.
Returning metadata with a Dynamic Management View
This is one of those little known secrets that, with some creativity, can be used to take your solutions to a completely new level. Data Management Views are used to return sets of metadata or information about database objects such as object names, attribute values and member sets. In addition to the actual data stored in the cube, DMV queries can return information about just about any system information and useful metadata. This example will only touch the tip of the iceberg.
Data Management View queries are parsed by the Analysis services data provider but the query execution is handled by a different set of objects than the OLAP storage engine. As such, the syntax is standard MDX. It’s a blend of SQL but it’s not really pure MDX and the rules are a little different.
8. Create a new dataset using the same data source. Let’s name it MeasureList. Since the DMV query isn’t MDX, you must use the Expression Builder, rather that the query builder, to enter the query script.
9. In the Dataset dialog, click the Expression Builder (fx) button and enter the following text:
Select MEASURE_NAME from $System.MDSCHEMA_MEASURES WHERE CUBE_NAME = ‘Adventure Works’
ORDER BY MEASURE_NAME
;
10.Close and save the Expression Builder and then click the Refresh Fields button.
11.Click the OK button to close the Dataset Properties dialog.
Figure 8
12.Edit the MeasureName parameter and set it to use the new dataset for the Available Values and then set the Default Value to Internet Sales Amount.
13.Close and save the parameter properties.
We should be ready to rock and roll so let’s make sure everything works now. Preview the report and it should look similar to my example.
Figure 9
The styling of your report may be a little different than mine. I’ve dressed up this report just a little by applying formatting to the vertical axis label and changed the series line colors and borders. This is typically the point where I’ll go through the design and make adjustments to make everything look good and consistent with my client’s company themes and standards.
Summary
I’ve shown you several techniques that should get you started building more dynamic and flexible report designs that use MDX to consume data from Analysis Services cubes databases and cubes. By parameterizing things like the measure and field names, function arguments and key property values, you can add a tremendous amount of flexibility to your reports. With just a little guidance, your business users can use a dynamic report to gain valuable insights and get answers to many important business questions without having to design additional reports.
Check out www.reportsurfer.com to see live examples of several unique report designs. As I said, this was just a starting point but you now have the tools to apply these techniques to your own unique problems and build flexible reports to meet your own business needs. Happy Reporting!
On the Microsoft Developer Network (MSDN) forum for Reporting Services and other online discussion resources, I see many report developers asking how to build analytical reports using relational data sources. Some of the most fundamental capabilities often require complex and convoluted coding techniques. Sometimes these features require the report developer to populate temporary tables, inefficient and complicated subqueries and work-around techniques to derive calculated values and simple report features.
You probably know that OLAP cubes make this kind of reporting work much easier to manage and the data much easier to query and report on. To take that step – the transition from relational storage to SQL Server Analysis Services – also means taking a big step from the familiar T-SQL query language to the Multi-dimensional Expression query language. MDX is not a difficult language to use once you get the basic concepts down but it’s quite a bit different than SQL and it takes some re-learning to get to that point. For many, at first MDX seems strange and foreign until arriving at a moment of clarity when the mechanics of the language begin to make practical sense. Understanding concepts like “sets”, “tuples”, “members”, “hierarchies” and “levels” will help you appreciate the elegance of this simple language and the power of the OLAP query engine.
This article will show you many of the essential report and MDX query design techniques, beyond the fundamentals, related to OLAP reporting. I’m not going to teach you all about MDX in this short article so I expect that you already know some of the basics. However, I will show some examples that may help newcomers get started if you’re willing to venture out of your comfort zone. You’ll see how to use techniques in Reporting Services that will let you take advantage of the power of MDX and Analysis Services cubes. I assume that you’ve had some experience with, and understand the fundamentals of report design. I won’t demonstrate every step but I will provide enough direction to build a report if you already know the basics. You should know what to do when I provide directions like “create a new dataset with the provided query script” or “create a new parameter and use the dataset for the available values.” If you don’t have prior report design experience, there are several good books available on Reporting Services that teach essential report design. I encourage you to pick one up and learn report design basics before you read this article.
Just to make sure we’re on the same page; let’s briefly discuss the kinds of things that SSAS and MDX reports are typically used for. Analytical reporting is not about showing transactional details like you would on an invoice or ledger. It’s about grouping and aggregating data to analyze status, progress and trends so business leaders and analysts know what’s going on in their business at a relatively high level.
Consider the following common challenges and business scenarios:
When analyzing sales progress over periods of time, business users want to see totals consistently for each period in the time series – even those that don’t have any data. For example, if there were no sales on a particular day, they still need to have a placeholder for that day to show that there were no sales.
For a given period, business users need to see the aggregate sales value for that period alongside the aggregate value for a prior period for comparison. For example, a report should show the total sales for each day for the past 30 days; and for each of those days’ total sales, it should also show and compare the total sales for the same day of the previous year.
An OLAP database, consisting of cubes and dimensions, makes it much easier to efficiently resolve these challenges without the need for writing complex queries that scan and aggregate large volumes of transactions.
Figure 1
Using the Adventure Works Sample Data
To use these examples, you’ll need to have the Adventure Works Analysis Services database which you can find at codeplex.com; Microsoft’s community for sample projects and code sharing. Search for “Adventure Works SQL Server Sample Databases” and then download and install the correct version. I have tested these examples with the data for SQL Server 2008 and SQL Server 2008 R2. After installing the relational data warehouse database and the Analysis Services project, open the Enterprise Analysis services project in Business Intelligence Development Studio and then deploy and process the database on your server.
MDX Query Builder
When a dataset is created and SQL Server Analysis Services is selected as the data source, the query design experience changes. The Build Query button on the dataset dialog now opens the MDX query builder. The graphical query builder is a useful tool for designing basic queries by dragging-and-dropping objects into the grid and various landing zones. Beyond this, the graphical designer doesn’t provide the flexibility to make manual modifications to the query script. Using the graphical designer, you can easily build an entire query, complete with multiple hierarchies on the rows axis and multiple measures and complete parameter filtering logic. I often use the graphical query builder to prototype an MDX query and sometimes use the query as a starting point. For highly-parameterized queries, I usually start over and write the query from scratch as you will see in the following examples.
The query builder uses a layering pattern to build MDX queries using sub select statements for each filtering slicer or parameter. This technique is an effective way to handle different filtering logic but queries can easily become overly complex, difficult to decipher and maintain. We won’t be using the graphical query builder. Use the left-most toolbar button “Edit as text” to switch to generic text mode.
MDX Reporting Essentials
Reporting Services can be used quite effectively for reporting on SSAS cubes and in most cases reports may be designed in the same manner as relational queries. However, you should be aware of the following common considerations:
Reporting Services must flatten MDX results before the data can be consumed. As a rule, place all measures on the columns axis and the initial set of dimension members on the rows axis.
Include empty members
One essential characteristic of trend analysis is that all periods should be represented, even if no values exist. For example, all time periods should appear on a trend line chart even if no activity is reported for a day or other time period. Include all dimension members by using the NON EMPTY directive before dimension references on an axis (usually on the rows axis.)
Allow the cube to aggregate and roll-up measures
By default the report designer applies the SUM function to numeric fields referenced in a report group. Some measures defined in the cube may not additive. For example, a measure that performs an average, inventory count or other calculated value cannot be summed across levels of a dimension hierarchy. For these measures, use the AGGREGATE function in place of the SUM function for the report items used for group totals. Consult Books Online to understand the restrictions and properties used to control the behavior of the AGGREGATE function in Reporting Services.