Paul Turley's SQL Server BI Blog

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

Menu

Skip to content
  • Home
  • My Books
  • Presentations
  • Best Practice Resources: Blogs & Books from the Experts
  • Video Tutorials
  • COVID-19 Daily Updates Report
  • Visualizations
  • About/Bio
  • Paul’s Bio
  • Note to SPAMers

Monthly Archives: November 2015

Complete DAX Function Reference

November 25, 2015 by Paul Turley

10

The DAX function documentation in MSDN can be found on separate pages,  organized by function categories and then details for every function.  This is useful but you can’t view all of the functions in one searchable page.  If you don’t know exactly what you’re looking for, it can be a bit of a hunting expedition.  I find that sometimes it is convenient to have all of the documentation in one list, making it easier to find the function I’m looking for.

I’ve organized the complete set of 2016 DAX functions for SSAS Tabular, Power Pivot and Power BI in two different views; grouped by function category and in one single list.  Using Excel Online, the lists can be sorted, searched and filtered.  I hope this is useful.

Complete DAX Function Reference (ungrouped list)

Complete DAX Function Reference (grouped by category)

DAX Function Reference on MSDN

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in BI Projects, DAX, Power BI, PowerPivot, SQL Syndication, SQLServerPedia, SSAS Design, Tabular Models. Tagged DAX functions, DAX reference, DAX syntax, Power BI DAX, Power Pivot DAX, Tabular DAX

SQL Server 2005 End of Support and Business Intelligence

November 24, 2015 by Paul Turley

0

How does the official end of support for SQL Server 2005 affect companies using Reporting Services, Analysis Services and other BI features?

I was recently interviewed for a CIO Magazine article by freelance writer Andy Patrizio about what the end of support means for CIOs.  Thanks to the SQL Server marketing team for including Data Platform MVPs to represent the product.  A link to the article is included at the end of this post.  By one  estimate, there are 13 to 15 million Windows Server 2003 machines currently in use and one-in-six have some edition of SQL Server 2005 installed.  That’s at least two million SQL Server 2005 instances that will no longer receive support or patching as of April, 2016.  The rules of HIPAA and PCI will require these machines to be upgraded to remain in compliance, not to mention to be safe from malicious attacks and other security threats.  Many of these legacy systems may be supporting old, special-purpose apps that just don’t scream for an upgrade.  Production databases will benefit from the innumerable enhancements made to the product in the past ten years.  For app-support databases, these can be consolidated and moved to centralized servers to replace all those old dusty machines in the custodial closet.  Licensing costs apply to hardware and not to individual databases so consolidating old databases can usually save money up front.

My consulting clients often cite cost and stability as their primary reasons to stay on older database platforms.  In the past, a common rule of thumb was is to remain one version behind and to skip every-other version, upgrading the last version only when a new version is released.  This practice might make sense for Quicken, desktop Windows or for database software ten years ago but it doesn’t make much sense today when the advantages outweigh the risks.  Sure, wait for the first service pack and by all means, do what you must to manage licensing costs, but mission-critical data systems are the heartbeat of modern business and need to perform and function.

There are many good reasons to upgrade from SQL Server 2005.  Thinking specifically about BI and reporting capabilities, here are a few:

The BI platform began to mature in 2005 and really became fully-baked in the versions that followed.  Reporting Services was introduced for SQL Server 2000.  SSRS 2005 was more of the same but the best features like advanced charts, sparklines, gauges, KPI indicators and maps were added in 2008 R2.  The product stabilized and drifted for the next few versions and now we’re going to see big improvements again in SQL Server 2016.  If you don’t plan to move to SQL Server 2016 any time soon, at least upgrade SSRS to SQL Server 2008 R2.   The report models and Report Builder 1.0 designer introduced in SQL Server 2005 didn’t last and have since been deprecated and replaced with more capable technologies.  If you’re using this tool, stop and move on.  It’s actually not supported now and simply won’t work in the future.

Analysis Services got a big architectural makeover in SQL Server 2005 but it took another three years to fully stabilize the product.  SSIS also started in SQL Server 2005 but it took another full version to stabilize and mature.  Outside of the BI tooling, the whole SQL Server relational engine is far more stable, secure, high performing and fault-tolerant.

The bottom-line is that you absolutely must upgrade your data platform at some point.  The reasons are so obvious and self-evident that it almost doesn’t warrant further discussion.  The question is not “if” but “when” and how often to upgrade.  Moving to the most recent version of SQL Server may or may not be right for your business.  An analysis of your specific requirements and the return you will get on your investment should help you decide whether you move to THE MOST RECENT version or A MORE RECENT version of SQL Server.  Whatever the case, you need to have a plan and you should review, revise and execute that plan every few years.  An end-of-support event like this is a good motivator and chance to put that plan into motion.

Read the full article in CIO Magazine

What end of support for SQL Server 2005 means for CIOs: http://www.cio.com/article/3007062/servers/what-end-of-support-for-sql-server-2005-means-for-cios.html

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in MVP Community, SolidQ, SQL Syndication, SQLServerPedia.

The Resurrection of Reporting Services & The Maturing of Power BI

November 11, 2015 by Paul Turley

13

Spending the past two weeks at the annual PASS Global Summit and the Microsoft MVP Summit, I’ve consumed a literal firehouse of information about the Microsoft BI platform.  I’ve participated in the PASS Summit for twelve years and the MVP Summit for seven years thus far and in that time, I don’t recall as much innovative change and product momentum as we have seen lately.  The pace of significant additions to the business intelligence offering is truly astounding.  Attending both of these events, I often learn about product feature investments on different levels which include those that are publically announced and those that are part of the roadmap and things that the product teams are working on or seriously considering in the near and longer-term future.

At both of these events, we saw a lot of very exciting functionality and heard some very bold statements about what the BI product teams are working on.  Citing the product team official blog: “Our goal is simple – we want to put the power of data in the hands of every business and person on the planet. It is our objective to serve over a billion users with the Microsoft business intelligence (BI) platform.”  To reach that goal, there are several very specific efforts in motion.  The first objective is to harmonize all of the disparate report types that are currently surfaced in different tools like Reporting Services, Excel, Datazen and Power BI.  This objective is to standardize reporting content types across Microsoft on-premises, cloud and hybrid systems.  These include paginated reports, interactive reports, mobile reports and analytical reports & charts.  There will be two different places for users to get to all their report content: Power BI dashboards and the new Reporting Services Portal.  Both of these “portals” will serve up a variety of reports and visual content whether hosted on-premises or in the cloud.  Additionally, users will have access to visual reports on mobile devices for all the platforms.  Some details are still in flux but the direction and roadmap are quite clear.

Power BI: Past, Present & Future

Power BI was front and center in many sessions and discussions and the pace of new feature delivery is dizzying.  The online Power BI service is updated weekly and the Power BI Desktop application is updated every month.  What was crystal clear is that Power BI is getting tremendous attention from the product teams and leadership as the go-to visualization, modeling and data mash-up tool for many important scenarios.  To-date, Power BI has been primarily marketed as a self-service analysis tool in the same way that Power Query, Power Pivot and Power View work in Excel.  However, it is quickly becoming an enterprise product with the ability to integrate, automate and use it in a variety of managed business scenarios.

Consider each of these possible options:

  • Power BI Desktop running on a user’s computer with data connected from many sources; across the Internet and on-premises.
  • Power BI datasets, reports and dashboards published to the secure cloud subscription services, shared with different permissions for different groups of organization users.
  • Power BI dashboards containing PBI report visuals, on-prem SSRS report visuals, mobile reports & KPIs, and Excel visuals.
  • On prem Reporting Services Portal containing Power BI reports & dashboards, SSRS reports, mobile dashboards & KPIs, and Excel visuals.
  • Power BI reports and dashboards in the cloud connected to live, on-prem SSAS tabular, SSAS multidimensional, or relational data sources using DirectQuery.

Although we can’t say exactly how or when, we will have the ability to publish Power BI Desktop Reports on-premises.  This is very welcome news.  We will also have the ability to pin on-premises reports and other content to a Power BI dashboard in the cloud.  Some of that capability was just added to SQL Server 2016 CTP 3.0.

Custom Visuals – This is one of several examples of how Microsoft is embracing open source code and community development.  In addition to the dozens of D3-based visuals that community developers have contributed, the product team is releasing one new visual per week.  Any custom visual in the gallery can be downloaded, added to a Power BI Desktop report and published to the service.  This is quickly making most common and special-purpose BI visuals available to everyone in one tool.

image

DirectQuery – connections to live data sources use query folding to optimize performance and real-time results from SQL Server, Azure SQL Database and Azure SQL Data Warehouse.  This also includes connectivity to MDX/multidimensional SSAS in SQL Server 2016!

Real-time dashboards & reports – Options for real-time data interaction include DirectQuery and direct connect through the Analysis Service connecter.  On-prem data can also be managed with the Personal Gateway.  According to team leadership at PASS, gateway enhancements for personal and enterprise applications should be announced soon.

Pinning SSRS Reports – A Reporting Services 2016 server can be registered to integrate with a Power BI subscription using the Reporting Services Configuration Manager.  This allows SSRS report visuals to be pinned to a Power BI dashboard.  I just tried this in the 2016 CTP 3.0 and it works quite well.  When viewing a report in Report Manager, a toolbar icon appears.  This prompts the report user to select a visual on the SSRS report and then for a dashboard in their Power BI subscription.

image

image

image

There is SO MUCH more coming!!  The product team members are so excited to share what they’re working on and lines between what they “eluded” to in their PASS session presentations and what they told us and demonstrated at the MVP Summit under strict NDA were very fine.  Just keep watching and you will see an astounding release velocity of new and impressive features in the next few months.

New Reporting Services

In SQL Server 2016, Reporting Services is getting a significant face lift on several fronts.  The HTML renderer has been completely rewritten to emit pure HTML 5 to produce consistent output in every modern browser on every device.  This capability is in the current CTP today.

Report Manager is replaced with a modern report portal that will host content from SSRS, mobile dashboards (formerly known as Datazen), Excel and eventually Power BI on premises reports.  This raises questions about the availability of Power BI on-prem and the only comment I can make based on public information is that it is planned for some time in the future.

Report parameters can be moved around and organized with greater precision in the parameter bar at the top of the report.

The printing capability in SSRS will use a “plug-in free” PDF renderer rather than the old ActiveX control that will no longer require a plug-in download or special permissions to run.

Two new chart types are added (treemap and sunburst) and changes are planned to modernize the default look of report visuals.

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in BI Industry, BI Projects, Business Analytics, Power BI, Self-service BI, SQL Server, SQL Syndication, SQLServerPedia, SSRS Design.

Post navigation

Blog Stats

  • 1,385,222 hits

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 5,075 other subscribers

Recent Posts

  • Doing Power BI the Right Way: 4. Power Query in Dataflows or Power BI Desktop
  • A First Look at Gen 2 Composite Models with Live Power BI Datasets
  • Power BI: The Week in Graphics
  • Doing Power BI the Right Way: 5. Data Modeling Essentials & Best Practices (2 of 2)
  • Doing Power BI the Right Way: 5. Data Modeling Essentials & Best Practices (1 of 2)
  • Composite Models Gen 2 and DirectQuery over Power BI Datasets
  • Power BI Expert Resources
  • Doing Power BI the Right Way: 7. Planning for separation – data models and reports
  • Doing Power BI the Right Way: 6. Validating data model results – Part 2
  • Doing Power BI the Right Way: 6. Validating data model results – Part 1

Category Cloud

BI Industry BI Projects Business Analytics Microsoft BI Platform MVP Community PASS Power BI PowerPivot Power View Self-service BI SolidQ SQL Server SQLServerPedia SQL Server Pro Magazine SQL Syndication SSAS Administration SSAS Design SSRS Administration SSRS Design Tabular Models

Archives

  • January 2021 (3)
  • December 2020 (4)
  • November 2020 (1)
  • October 2020 (2)
  • September 2020 (1)
  • August 2020 (1)
  • July 2020 (4)
  • May 2020 (1)
  • April 2020 (3)
  • March 2020 (3)
  • February 2020 (1)
  • January 2020 (1)
  • December 2019 (2)
  • November 2019 (5)
  • October 2019 (1)
  • September 2019 (1)
  • August 2019 (2)
  • July 2019 (5)
  • May 2019 (1)
  • April 2019 (3)
  • March 2019 (1)
  • February 2019 (2)
  • December 2018 (3)
  • November 2018 (1)
  • October 2018 (1)
  • September 2018 (3)
  • July 2018 (5)
  • May 2018 (2)
  • April 2018 (2)
  • March 2018 (3)
  • February 2018 (3)
  • January 2018 (3)
  • December 2017 (3)
  • November 2017 (4)
  • October 2017 (1)
  • September 2017 (2)
  • August 2017 (1)
  • July 2017 (1)
  • June 2017 (4)
  • April 2017 (8)
  • March 2017 (1)
  • February 2017 (2)
  • January 2017 (8)
  • December 2016 (4)
  • November 2016 (3)
  • October 2016 (2)
  • September 2016 (1)
  • July 2016 (3)
  • June 2016 (3)
  • May 2016 (3)
  • March 2016 (6)
  • February 2016 (5)
  • January 2016 (2)
  • December 2015 (4)
  • November 2015 (3)
  • October 2015 (2)
  • September 2015 (2)
  • August 2015 (3)
  • July 2015 (6)
  • June 2015 (1)
  • May 2015 (5)
  • April 2015 (4)
  • March 2015 (1)
  • February 2015 (2)
  • January 2015 (4)
  • December 2014 (3)
  • November 2014 (1)
  • October 2014 (4)
  • September 2014 (1)
  • August 2014 (2)
  • July 2014 (5)
  • June 2014 (4)
  • May 2014 (2)
  • April 2014 (6)
  • March 2014 (3)
  • February 2014 (7)
  • January 2014 (5)
  • December 2013 (2)
  • November 2013 (1)
  • October 2013 (1)
  • September 2013 (2)
  • July 2013 (4)
  • June 2013 (5)
  • April 2013 (1)
  • March 2013 (4)
  • February 2013 (3)
  • January 2013 (1)
  • December 2012 (4)
  • November 2012 (4)
  • October 2012 (3)
  • September 2012 (3)
  • August 2012 (2)
  • July 2012 (2)
  • June 2012 (2)
  • May 2012 (3)
  • March 2012 (2)
  • February 2012 (3)
  • December 2011 (1)
  • November 2011 (3)
  • October 2011 (11)
  • September 2011 (7)
  • August 2011 (4)
  • July 2011 (2)
  • June 2011 (4)
  • May 2011 (5)
  • April 2011 (5)
  • March 2011 (4)
  • February 2011 (2)
  • January 2011 (4)
  • December 2010 (4)
  • November 2010 (4)
  • October 2010 (1)
  • September 2010 (1)
  • August 2010 (2)
  • June 2010 (1)
  • May 2010 (2)
  • April 2010 (1)
  • March 2010 (19)
  • December 2009 (1)
  • June 2009 (1)

Tag Cloud

" & Workspace and Database Recovery Techniques Aaron Nelson Ad-hoc reporting Add columns Add controls Albert Ferrari Alternate row colors Analysis Services Operations Guide Apple Are There Rules for Tabular Model Design? Article Assemblies Azure Azure Reporting Azure SQL Database BARC Survey best practices BI BI Center of Excellence BI COE BI Conference Bill Gates Birds-of-a-Feather BI Roles and Team Composition BISM BI Survey 10 Blogging Breakcrumb links Browser settings Build career Business Intelligence Business Intelligence for Visual Studio 2012 Business scorecard Can I Use Reporting Services with Tabular & PowerPivot Models? Checkbox in report Checkbox list Check mark Chris Webb Cloud computing Column chart Community Conditional formatting Conference presentation Conference review Conference session Conference Session Topics Cortana Power BI Integration Custom code Custom coding reports Custom Functions Dashboard design Dashboard standards Database Lifecycle Management Data Modeling 101 for Tabular Models Data Quality Services Dataset filter nulls Datazen Datazen control selection Date parameters DAX DAX: Essential Concepts DAX: Some of the Most Interesting Functions DAX: Some of the Most Useful Functions DAX functions DAX reference DAX syntax Demo scenario Denali CTP3 DevTeach DLM Do I Write MDX or DAX Queries to Report on Tabular Data? Do We Need to Have SharePoint to Use Tabular Models? Drill-down Drill-through Drillthrough Dynamic column visibility Dynamics CRM Dynamics reporting Embedded formatting ENterprise SSAS Errors Estimating BI European PASS Filter by user Formula Firewall Funnel charts Garner Magic Quadrant Microsoft BI Getting Started with DAX Calculations Global Summit Live Feeds Greenbar report Grocery shopping demo Hans Rosling Happy Birthday Power BI Hide columns Hitachi Consulting How Do You Design a Tabular Model for a Large Volume of Data? How Do You Secure a Tabular Model? How to Deploy and Manage a Tabular Model SSAS Database How to Promote a Business-created PowerPivot Model to an IT-managed SSAS Tabular Model HTML text integrated mode Interview Interviews Isn’t a Tabular Model Just Another Name for a Cube? James Phillips Julie Koesmarno King of Spain KPI indicator Licensing Login prompt Manually starting subscription Map Visualization Marco RUsso Master-detail report Master Data Management MDM MDX datasets MDX queries Microsoft Architecture Journal Microsoft humour Microsoft MVP Microsoft news Mobile Reporting Mobile Reports MVP community MVP Deep Dives 2 MVPs support the community MVP Summit navigation Nested tables Null filter Olivier Matrat Olympia WA Oracle vs Microsoft in the movies Oregon SQL Saturday Parameter controls Parameterize Parameters PASS 2012 PASS BAC Blog Feed PASS community leaders PASS Conference PASS Global Summit 2012 PASS Keynotes PASS Summit PASS Summit 2017 PASS Summit 2018 PASS Summit Announcements Paul te Braak PDF image distortion dithering fonts PerformancePoint Pinal Dave Poll About Product Usage Poll Results Pop-up window; Java script Portland OR Power BI Administration Power BI Best Visuals Contest Power BI DAX Power BI Partner Showcase Power BI Premium Power BI Pro Power BI Training Power BI World Tour Power Pivot PowerPivot Power Pivot DAX Power Query Power Query Training Power View Power View multidimensional cubes Preparing Data for a Tabular Model Project Phoenix Recipes Redmond SQL Saturday Reed Jacobson Remove columns Repeating list Report controls report dependencies Report deployment Reporting Services 2016 Reporting Services Training Report navigation Report parameters Report recipe book Reports for MDX Return specific row Rob Collie DAX Book Robert Bruckner Scheduled Refresh Scripting Tabular Model Measures Self-service reporting Seth Bauer SharePoint SharePoint 2012 SharePoint integration Simplifying and Automating Tabular Model Design Tasks SolidQ SolidQ Journal Solid Quality Mentors Spatial queries; happy holidays; Merry Christmas SQLAuthority SQLCAT SQL Saturday SQL Saturday 446 SQL Saturday Portland Oregon SQL Server SQL Server 2012 Upgrade Guide SQL Server community SQL Server Data Tools – Business Intelligence for Visual Studio 2012 SQL Server Denali SQL Server Denali; Self-service reporting SQL Server Denali CTP3 SQL Server MVP SQL Server Optimization SQL Server Pro Magazine SQL Teach SSAS SSAS Performance Logger SSAS Tabular SSAS Tools BI Development Tools SSDT BI SSRS 2016 SSRS dynamic columns SSRS PowerShell SSRS version control standards Start subscription Steve Jobs StreamInsight Strip line style Subscription Survival Tips for Using the Tabular Model Design Environment Tabular DAX Tabular Model & " Tabular Model Common Errors and Remedies Tabular Model Design Tabular Model Design Checklist Tabular Modeling Tabular models Tabular report design TechEd TechEd 2011 Sessions TechSmith Snagit Pro themes Threshold line Top values Training clsses Unconference User-related report content User authentication User prompted to login Using DAX to Solve real-World Business Scenarios Vancouver BC Vern Rabe Visualisation Visualization Visual Report Design Volunteers Weather and Climate Web.Contents Web API What About Multidimensional – Will Tabular Replace It? What are the Naming Conventions for Tabular Model Objects? What Do You Teach Non-technical Business Users About PowerPivot and Tabular Models? What’s the Best Business User Tool for Browsing & Analyzing Business Data with Tabular Models? What’s the Best IT Tool for Reporting on Tabular Models? What’s the Difference Between Calculated Columns & Measures? What’s the Difference Between PowerPivot and Tabular Models? Why Tabular? Wrox book
RSS
RSS Feed
Powered by WordPress.com.
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.
%d bloggers like this: