Tech Tip Thursday: Dynamic Power BI reports using Parameters

Did you know that you can dynamically filter data in Power BI using parameters that are stored in an Excel workbook? In this video, Patrick from Guy in a Cube shows us how, using M Functions within Power Query and a gateway to enable data refresh. Check it out!

from Category Name https://powerbi.microsoft.com/en-us/blog/tech-tip-thursday-dynamic-power-bi-reports-using-parameters/

Webinar Wednesday: Dashboard in an Hour

If you’re new to Power BI, or want to introduce the rest of your team to ways they can go quickly from data to insight to action, you won’t want to miss Wednesday’s Dashboard in an Hour webinar! Dashboard In An Hour is a high-level introduction to Power BI in just 60 minutes. It’s being presented this week by Reid Havens, Founder & CEO of Havens Consulting Inc. Join us at 10:00 a.m. PT on Wednesday, May 24, 2017.

from Category Name https://powerbi.microsoft.com/en-us/blog/webinar-wednesday-dashboard-in-an-hour/

Explore your Office 365 Adoption Data in Power BI

The Public Preview of the Office 365 Adoption Content Pack combines the intelligence of the Office 365 usage reports that are available for you in the admin center with the interactive analysis capabilities of Power BI, providing rich usage and adoption insights.

from Category Name https://powerbi.microsoft.com/en-us/blog/explore-your-office-365-adoption-data-in-power-bi/

Tech Tip Thursday: Better use of colors in Power BI

Microsoft’s Guy in a Cube has been providing tips and tricks for Power BI and Business Intelligence on his YouTube channel since 2014. Occasionally on Thursdays we highlight a different helpful video from his collection. In this video, Guy in a Cube has a special guest presenter: regular Power BI webinar host Chuck Sterling! Chuck talks about color themes, one of his favorite new features. He looks at how you can quickly create your own themes to use within Power BI, or browse the Themes Gallery in the community to get pre-created themes from others.

from Category Name https://powerbi.microsoft.com/en-us/blog/tech-tip-thursday-better-use-of-colors-in-power-bi/

What’s new in SQL Server 2017 CTP 2.1 for Analysis Services

The public CTP 2.1 of SQL Server 2017 is available here! This public preview includes the following enhancements for Analysis Services tabular models.

  • Shared M expressions are shown in the SSDT Tabular Model Explorer, and can be maintained using the Query Editor.
  • Data Management View (DMV) improvements.
  • Opening an file with the .MSDAX extension in SSDT enables DAX non-model related IntelliSense.
  • Encoding hints can be set in the SSDT properties window.

Shared M expressions

Shared M expressions are shown in the Tabular Model Explorer in SSDT! By right clicking the Expressions node, you can edit the expressions in the Query Editor. This should seem familiar to Power BI Desktop users.

query-editor

DMV improvements

DISCOVER_CALC_DEPENDENCY

M dependencies are included in DISCOVER_CALC_DEPENDENCY for CTP 2.1. As communicated in the CTP 2.0 blog post, DISCOVER_CALC_DEPENDENCY now works with 1200 models.

The following query returns the output shown below. M expressions and structured data sources are included for 1400 models.

SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY
WHERE OBJECT_TYPE = 'PARTITION' OR OBJECT_TYPE = 'EXPRESSION';

dmv-output

The output is a superset of the information shown by the Query Dependencies visual, which is available in SSDT from the Query Editor.

query-dependencies

This information is useful for numerous scenarios including the following.

  • Documentation of tabular models.
  • Community tools such as BISM Normalizer that perform incremental metadata deployment and merging, as well as other 3rd party tools, can use it for impact analysis.

MDSCHEMA_MEASUREGROUP_DIMENSIONS

CTP 2.1 provides a fix for MDSCHEMA_MEASUREGROUP_DIMENSIONS. This DMV is used by various client tools to show measure dimensionality. For example, the Explore feature in Excel Pivot Tables allows the user to cross-drill to dimensions related to the selected measures.

explore

Prior to CTP 2.1, some rows were missing in the output for 1200 models, which meant the Explore feature did not work correctly. This is fixed in CTP 2.1.

We intend to make further DMV improvements in forthcoming releases, so stay tuned.

DAX file editing

Opening a file with the .MSDAX extension in SSDT allows DAX editing with non-model related IntelliSense such as highlighting, statement completion and parameter info. As you can imagine, we intend to use this for interesting features to be released in the future!

dax-file-editing

Encoding hints

As documented in this blog post, encoding hints are an advanced feature introduced by CTP 1.3. They can help optimization of processing (data refresh) for large in-memory tabular models. In CTP 2.1, encoding hints can be set in the SSDT Visual Studio properties window.

encoding-hints

Download now!

To get started, download SQL Server 2017 CTP2.1. The May 2017 release of the Analysis Services VSIX for SSDT is available here. VSIX deployment for Visual Studio 2017 is discussed in this blog post.

Be sure to keep an eye on this blog to stay up to date on Analysis Services!

from Category Name https://blogs.msdn.microsoft.com/analysisservices/2017/05/18/whats-new-in-sql-server-2017-ctp-2-1-for-analysis-services/

Power BI Report Server preview now available

On May 3, Microsoft announced Power BI Premium, a capacity-based licensing model that increases flexibility for how users access, share and distribute content. The new offering also introduces the ability to manage Power BI reports on-premises with the included Power BI Report Server.

from Category Name https://powerbi.microsoft.com/en-us/blog/power-bi-report-server-preview-now-available/

Try new SQL Server command line tools to generate T-SQL scripts and monitor Dynamic Management Views

This post was authored by Tara Raj and Vinson Yu, Program Managers – SQL Server Team

We are excited to announce the public preview availability of two new command line tools for SQL Server:

  • The mssql-scripter tool enables developers, DBAs, and sysadmins to generate CREATE and INSERT T-SQL scripts for database objects in SQL Server, Azure SQL DB, and Azure SQL DW from the command line.
  • The DBFS tool enables DBAs and sysadmins to monitor SQL Server more easily by exposing live data from SQL Server Dynamic Management Views (DMVs) as virtual files in a virtual directory on Linux operating systems.

Read on for detailed usage examples, try out these new command line tools, and give us your feedback.

mssql-scripter

Mssql-scripter is the multiplatform command line equivalent of the widely used Generate Scripts Wizard experience in SSMS.

You can use mssql-scripter on Linux, macOS, and Windows to generate data definition language (DDL) and data manipulation language (DML) T-SQL scripts for database objects in SQL Server running anywhere, Azure SQL Database, and Azure SQL Data Warehouse. You can save the generated T-SQL script to a .sql file or pipe it to standard *nix utilities (for example, sed, awk, grep) for further transformations. You can edit the generated script or check it into source control and subsequently execute the script in your existing SQL database deployment processes and DevOps pipelines with standard multiplatform SQL command line tools such as sqlcmd.

Mssql-scripter is built using Python and incorporates the usability principles of the new Azure CLI 2.0 tools. The source code can be found on Github at https://github.com/Microsoft/sql-xplat-cli, and we welcome your contributions and pull requests!

Get started with mssql-scripter

Install
$pip install mssql-scripter
For additional installation tips, visit https://github.com/Microsoft/sql-xplat-cli/blob/dev/doc/installation_guide.md.

Script Your First Database Objects
For usage and help content, pass in the -h parameter, which will also show all options:
mssql-scripter -h

Here are some example commands
# Generate DDL scripts for all database objects (default) in the Adventureworks database and output to stdout
$ mssql-scripter -S localhost -d AdventureWorks -U sa

# Generate DDL scripts for all database objects and DML scripts (INSERT statements) for all tables in the Adventureworks database and save the script to a file
$ mssql-scripter -S localhost -d AdventureWorks -U sa –schema-and-data  > ./adventureworks.sql

# generate DDL scripts for objects that contain “Employee” in their name to stdout
$ mssql-scripter -S localhost -d AdventureWorks -U sa –include-objects Employee

# Change a schema name in the generated DDL script on Linux and macOS and bash in Windows 10.
# 1) Generate DDL scripts for the Adventureworks database
# 2) Pipe results and change all occurrences of SalesLT to SalesLT_test using sed, and save the script to a file
$ mssql-scripter scripter -S localhost -d Adventureworks -U sa | sed -e “s/SalesLT./SalesLT_test./g” > adventureworks_SalesLT_test.sql

DBFS

A big part of operationalizing SQL Server is to monitor to ensure that SQL Server is performant, highly available, and secure for your applications. With SQL Server 2017, Dynamic Management Views (DMVs) on Windows are also accessible on Linux, allowing your existing scripts and tools that rely on DMVs to continue to work. Traditionally, to get this information, you would use GUI admin tools such as SSMS or command line tools such as SQLCMD to run queries.

Today, we are also introducing a new experimental Linux tool, DBFS, which enables you to access live DMVS mounted to a virtual filesystem using FUSE. All you need to do is view the contents of the virtual files in the mounted virtual directory to see the same data you would see as if you ran a SQL query to view the DMV data. There is no need to log in to the SQL Server using a GUI or command line tool or run SQL queries. DBFS can also be used in scenarios where you want to access DMV data from the context of a script with CLI tools such as grep, awk, and sed.

DBFS uses the FUSE file system module to create two zero byte files for each DMV—one for showing the data in CSV format and one for showing the data in JSON format. When a file is “read,” the relevant information from the corresponding DMV is queried from SQL Server and displayed just like the contents of any CSV or JSON text file.

Features

  • Access data in .json format if you are connected to SQL Server 2016 or later
  • Compatible with Bash tools such as grep, sed, and awk
  • Live DMV data at time of access
  • Works with both SQL Server on Windows and SQL Server on Linux

Notes

  • This tool is currently only available for Ubuntu, Red Hat, and CentOS (SUSE coming soon!).

Next Steps:
See more usage examples and read more about mssql-scripter at https://github.com/Microsoft/sql-xplat-cli and get started with the DBFS today at https://github.com/Microsoft/dbfs.

We are open to suggestions, feedback, questions, and of course contributions to the project itself.

from Category Name https://blogs.technet.microsoft.com/dataplatforminsider/2017/05/17/try-new-sql-server-command-line-tools-to-generate-t-sql-scripts-and-monitor-dynamic-management-views/