Enterprise Analysis Services Development: Interview with Alberto Ferrari and Marco Russo

December 2, 2017 by Paul Turley

Between sessions at the PASS Summit, I had the privilege of interviewing Alberto Ferrari and Marco Russo; SSAS Maestros and BI industry thought leaders. Alberto and Marco are revered as expert trainers and practitioners of SSAS and Power BI solutions. They are also well known for evangelizing best practices using the DAX calculation and query language. We spoke about some of the challenges and learnings they have gained from years of large-scale project implementations.

Both SSAS Tabular and Power BI Desktop share the same characteristic in that the semantic data model definition is described in a single file. Compared with older multidimensional model projects, the single file approach simplifies much of the development effort but it can be challenging to coordinate projects with multiple developers. They shared tool recommendations to assist with project integration, version management and development. Marco and Alberto recommend a number of community supported add-ins and other freely available software to enhance Visual Studio and Power BI Desktop.

I sincerely appreciate these two gentlemen taking the time to share their insights in this short interview, and their many contributions over the years that continue to improve Microsoft’s industry-leading Business Intelligence platform and the community that supports it. A comprehensive library of resources, training, tools and articles are available at: SQLBI.com.

During the interview we talked about several different tools that they adamantly endorse for any SSAS developer to have at their disposal. From my own experience, in rough order of priority, I recommend :

BIDS Helper / BI Developer Extensions – This Visual Studio add-in is an important tool for anyone developing Business Intelligence solutions in any version of Visual Studio or SQL Server. It includes several essential features and capabilities for SSIS, SSRS and SSAS projects. It has been a community-developed and community-supported tool for many years and is considered by most experienced BI developers to be a core extension of the Visual Studio SSDT (formerly called “BIDS”) add-in.
Community developed tools like this help to spur future enhancements to the officially-supported capabilities in later Microsoft development tooling. I suggest that you carefully read the documentation for SSAS Tabular features in this tool because some of the options only apply to certain versions of SSAS. Some of the BIDS Helper features for SSAS 2012 and 2014 have since been added to the officially-supported designer for SSAS 2016 and 2017. BIDS Helper was initially maintained on the Microsoft CodePlex site (here: http://bidshelper.codeplex.com/) and has since been moved to GitHub here: https://bideveloperextensions.github.io/. It can now be installed in the Visual Studio Tools > Extensions and Updates menu by search for “BI Developer Extensions” in the Online section.
DAX Studio – This is a stand-alone application and the most comprehensive DAX, SSAS and Power BI calculation script and query editing and performance tool available. It is an open source project managed and maintained by a collaboration of several community MVPs and industry leaders (coordinators are Darren Gosbell & Marco Russo). The latest version and codebase have recently been moved from CodePlex to http://daxstudio.org.
Tabular Editor – An open source, stand-alone SSAS Tabular model design tool that runs outside of Visual Studio. It is distributed through a GitHub repo at: https://tabulareditor.github.io. The source code is maintained at: https://github.com/otykier/TabularEditor. It is described as a lightweight editor for SSAS Tabular Models built in .NET/WinForms and utilizes the Analysis Management Objects (AMO) library.
DAX Editor – an extension to SSDT that makes it easier to edit DAX measures in a Tabular project. It includes: DAX Syntax Highlighting, DAX Formatting and Textual representation of measures defined in a Tabular project
Extracting/importing measures from/to a Tabular model file (.BIM). This add-in is developed and maintained by SQLBI. It can be found in the Visual Studio > Tools > Extensions and Updates menu or downloaded from SQLBI at: https://www.sqlbi.com/tools/dax-editor.
BISM Normalizer – An add-in for Visual Studio/SSDT that can be installed from the Visual Studio Tools > Extensions and Updates menu by search for “BISM Normalizer”. This is an essential tool if you need to compare multiple SSAS Tabular projects and databases (including workspace databases) and then manage the steps to selectively merge additions and differences between objects. I have found this useful for integrating parallel development work on the same SSAS Tabular project but I will warn you that if used routinely to merge multiple changes to a single project, it can be a complex undertaking and not something I recommend on a daily basis.
BISM Normalizer is developed and maintained by Christian Wade, formerly an independent consultant and now a Senior Program Manager on the SSAS product team. Although Christian works for Microsoft, this is still considered a community-supported, third-party tool not officially supported by Microsoft. It is, however, the only tool designed specifically to perform schema comparisons and object merging in SSAS.

In our discussion, we also refer to these tools and resources:

VertiPaq Analyzer – An older tool (actually an Excel Power Pivot workbook) created by Kasper de Jonge, Program Manager from the Power BI/SSAS product team, that imports SSAS DMV query results and reports object size, object data compression rates and optimization statistics. It has since been updated and enhanced. This is a convenient way to get information quickly about your SSAS or Power BI model. The same information can be obtained, with a little more effort, by running DMV queries in DAX Studio or SSMS. Links to the original and updated versions are available on the SQL BI site at: https://www.sqlbi.com/articles/data-model-size-with-vertipaq-analyzer/.
DAX Formatter – An online tool for optimizing and reformatting DAX expressions, available from SQLBI, at: http://www.daxformatter.com/
Tabular Modeling in Microsoft SQL Server Analysis Services – is a Microsoft Press book written by Alberto and Marco that applies mainly to SSAS Tabular 2016 and 2017 projects. This is an absolutely invaluable reference and tutorial on SSAS design best practices and design techniques. Previous books from our Italian friends include DAX Patterns and The Definitive Guide to DAX; all excellent learning resources that I have in my book library.

Shortly after this conversation, Marco and Alberto delivered two sessions at the PASS Summit conference about DAX best practices and performance optimizations. A little later, Marco sat with Guy In A Cube hosts Adam Saxton and Patrick Leblanc to chat about these sessions. You can find that interview here. I also had a chance to catch-up with Adam and Patrick in an earlier interview during the conference, which I will share in a later blog post.

Implementing Row-Level Security in Power BI

August 14, 2017 by Paul Turley

The best method to implement row-level security in a published Power BI model or SSAS Tabular model consumed from the Power BI service will depend to some degree on your data structure and requirements. The method I demonstrate here is one of the most flexible approaches and one that I commonly use in my projects. Continue reading →

Microsoft Data Insights Summit Live Streaming & Free Sessions

June 5, 2017 by Paul Turley

Continue reading →

Professional SSRS 2016 Book Preview Posts

January 22, 2017 by Paul Turley

This page is a table of contents for for several new and forthcoming posts. I’m posting a series of excerpts for my Wrox Press book: Professional SQL Server 2016 Reporting Services and Mobile Reports. Each of the posts is a condensed version of the material covered in a corresponding chapter from the book. Although I would love for you to buy the book to get the full edition of each topic, each post will contain valuable information that I hope will be informative and educational on it’s own; whether you buy the book or not. I’ll update this index with topics and links as I continue to add each post. Until then, some of these will serve as placeholders for future posts.

Please post comments or contact me through my blog if you have questions or feedback, or if you are interested in training and consulting related to this material. – Paul

Continue reading →

Publish Power BI Reports to SSRS On-premise Preview Now Available

January 17, 2017 by Paul Turley

This just in from the Reporting Services product team:

“Power BI reports in SQL Server Reporting Services: January 2017 Technical Preview now available” This feature addition will allow Power BI reports to be published to a local SQL Server Reporting Services server, entirely-on-premises without using the Power BI cloud service.

The January 2017 Technical Preview can be downloaded from: https://www.microsoft.com/en-us/download/details.aspx?id=54610

I literally just received this update from Microsoft and plan to download and start working with the preview. I’ll post updates with my experience.

This is not an update for SQL Server 2016 but it is actually the next version of SQL Server which is due out in a few months.

Please check back for updates!

More information on the SQL Server Reporting Services Team Blog

Analysis Services and Power BI: Time-travel, Space-warping and Teleportation

December 10, 2016 by Paul Turley

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 result for jet climping

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.

Image result for VertiPaq Compression

image: microsoftpressstore.com, Ferrari, Russo

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.

Migrating reports between SSRS report servers simplified with new PowerShell commands

December 3, 2016 by Paul Turley

I recently needed to replicate all of the reports on one report server to a different server. I started downloading each report one at a time to a file system folder, moving them to the other machine and then uploading each RDL file. Needless to say, that’s a slow and painful process. Since a new set of PowerShell command lets (Cmdets) were introduced for Reporting Service just a couple of weeks ago, I decided to give them a try and share the results.

Aaron Nelson, long-time Microsoft Data Platform MVP and PowerShell fanatic, has literally been hounding me for the past few weeks to take some time and look at a new PowerShell library and scripts originally developed by Parth Shah from the SSRS product team. Jaime Tarquino also played a big role in automating the build process. It’s the personal commitment of folks at Microsoft like Parth and Jaime that really make a product like SSRS shine.

Aaron can be pretty persistent and I admit it’s ironic that I’ve been too busy finishing up the final editing work on the Wrox SSRS 2016 book and the new edX SSRS course to look at these RS PowerShell CmdLets but I’m glad that I did because they’re an absolute gold mine.

Back in July Aaron asked me to post some questions about what PowerShell support members of the Reporting Services community would like to see. That post is here. We got some good feedback to help the product team prioritize their efforts in this area. Aaron and and Chrissy LeMaire worked closely with the Reporting Services team to improve the commands before they were recently announced, and the team cited their help in this announcement: https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/11/07/community-contributions-to-the-powershell-scripts-for-reporting-services/

Back to my project… I have developed several demo and lab exercise reports for the new edX SSRS course: Analyzing and Visualizing Data with SQL Server Reporting Services. The development environment is an Azure virtual machine that I recently didn’t have access to while travelling and needed to move all the reports to a local VM to continue my work. I needed to make sure that current versions of these dozens of reports are on both servers. Using a couple of new PowerShell commands, this was quite easy. Here’s how it worked:

Step 1: I open the PowerShell ISE console on the server where I need to archive the reports (shown with the script I’ll run in the next few steps)

Step 2: run a command to download and install the rstools library (see line 1):

Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)

Step 3: Line 5 downloads all the reports in the “DAT214x Course” folder on my Azure VM report server to a local file system folder:

Out-RsFolderContent …

Step 4: On the destination server, I also run the command to install the rstools library…

Step 5: After copying the files to a local folder (or you could use a UNC path to access a shared folder), run Write-RsFolderContent to upload all the reports to the same destination folder:

Progress is displayed while the files are uploaded:

With shared data sources in the same places, all of the files are uploaded and working:

This saved me hours of time and provided peace of mind, knowing that I could easily automate large migrations in the future.

Here is the complete list of commands. You can get help, execution details and optional parameter information by prefixing the command with the word “help “:

Backup-RsEncryptionKey
Get-RsCatalogItems
Get-RsDataSource
Get-RsItemReferences
Grant-AccessOnCatalogItem
Grant-AccessToRs
New-RsConfigurationSettingObject
New-RsDataSource
New-RsWebServiceProxy
Out-RsCatalogItem
Out-RsFolderContent
Register-PowerBI
Remove-RsCatalogItem
Restore-RSEncryptionKey
Revoke-AccessOnCatalogItem
Revoke-AccessToRs
Set-RsDataSet
Set-RsDataSource
Set-RsDataSourcePassword
Set-RsEmailSettingsAsBasicAuth
Set-RsEmailSettingsAsNoAuth
Set-RsEmailSettingsAsNTLMAuth
Set-RsSharedDataSource
Write-RsCatalogItem
Write-RsFolderContent

Some additional notes (from Aaron)…

You can download these commands into your PowerShell environment very easily by running this simple command:

Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)

You need to be running PowerShell as Administrator in order to do this.

My favorite command among them is this one which will upload an entire folder worth of Reports & Datasets to an SSRS server for you. Here’s an example using Jamie Thomson’s SSIS Reporting Pack (attached):

Write-RsFolderContent -ReportServerUri ‘http://localhost/ReportServer_SQL2016’ -Path ‘C:\SSIS Reporting Pack\Reports’ -Destination /SSISReporting

You can get the full list of commands by running this:

Get-Command -Module ReportingServicesTools

If you have access to multiple report servers, you only need to run PowerShell commands on one machine.

Folder recursion is supported if you have nested folders.

I think you’ll find these commands can help speed up your Development Lifecycle.

Thanks again to Parth, Jaime, Aaron, Chrissy, Riccardo and the rest of the product team for making this possible!

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

Category Archives: BI Projects

Enterprise Analysis Services Development: Interview with Alberto Ferrari and Marco Russo

Implementing Row-Level Security in Power BI

Microsoft Data Insights Summit Live Streaming & Free Sessions

Professional SSRS 2016 Book Preview Posts

Publish Power BI Reports to SSRS On-premise Preview Now Available

Analysis Services and Power BI: Time-travel, Space-warping and Teleportation

Migrating reports between SSRS report servers simplified with new PowerShell commands

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: