Data is king!

SQL Server Analysis Service 2016 CTP 2.3 DirectQuery in action (by Kasper de Jonge)

Posted on Updated on

In my previous blog post “What’s new in Microsoft SQL Server Analysis Services Tabular models in SQL Server 2016 CTP 2.3” I introduced some of the enhancements coming to SQL Server Analysis Services Tabular –and many more will be coming in the next CTPs. In this article, I’d like to show you what the combination of Super DAX and improved DirectQuery could mean to you.

For illustration, I loaded a dataset with 220 million rows in a fact table and several other dimension tables into a relational SQL Server 2016 CTP 2.3 database, I then enabled columnstore indexes on all the tables.  The hardware was a Lenovo W530 laptop, so no server hardware by any means. Your mileage might vary, but I would imagine much better performance on a proper server with lots of RAM.

Next, I built a Tabular model in DirectQuery mode on top of this relational database by using SSDT for Visual Studio 2013 (this version also works with SQL16). For comparison, I installed two instances of Analysis Services, SQL Server 2014 and SQL Server 2016 CTP 2.3, and deployed the same model to both. All running on the same laptop.

Finally, I built a report by using Power BI Desktop. I connected to the SQL Server 2014 SSAS instance and started building my report:

Looks beautiful doesn’t it :). I added some realistic visuals like a matrix as well as these are usually the worst for performance.

Now, let’s take a look at a SQL profiler trace to see what is generated when I refresh the entire report:

The complete refresh of the report took 3 minutes and 34 seconds. 15 source queries were sent to SQL Server.

Let’s see what happens when I use the same report against my SQL Server 2016 CTP 2.3 instance:

The same report refreshed in 10 seconds instead of 3 min and 34 seconds, a whopping 20 times faster! And Analysis Services only generated five SQL queries!

That is a significant improvement you can start experiencing today. You will see the most improvement when using Power BI Desktop and Power BI service together with the Analysis Services connector but improvements can also be expected when using Power View in Excel and SharePoint. And as mentioned in my previous blog post, we are planning to ship even more performance updates in the next SQL Server 2016 CTPs.

So start using SQL Server 2016 CTP2.3 today and see for yourself! Here are the steps you can try out:

Use SuperDAX and the new DirectQuery with an existing model:

  1. Download SQL Server 2016 CTP2.3 here.
  2. Install Analysis Services from the 2016 setup on your development machine. You can install it as a new instance so you can run it side by side with SQL Server 2014.
  3. Backup the database you want to use and restore it to the SQL Server 2016 CTP instance.
  4. Point Power BI, Power BI Desktop, or any other client tool to the restored database.
  5. You can now enjoy improved performance with your tabular model.

To develop a new model using the new DAX functions or DirectQuery functionality:

  1. Download SQL Server 2016 CTP2.3 here.
  2. Install Analysis Services from the 2016 setup  on your development machine. You can install it as a new instance so you can run it side by side with SQL Server 2014.
  3. Use SSDT for Visuals Studio 2013 and point the workspace database to the newly installed SQL 2016 instance.
  4. Open SSDT and then open your Tabular model solution.
  5. Start using the new DAX expressions.
  6. Deploy to your newly installed SQL Server 2016 CTP instance.
  7. Be advised that the new DAX expressions will not work with previous versions of Analysis Services, SQL Server 2016 is required.

I hope you are excited about the upcoming SQL Server 2016 release. This is just the beginning. More will come over the next couple of months, but I’m repeating myself. Stay tuned!

From <http://blogs.msdn.com/b/analysisservices/archive/2015/09/08/sql-server-analysis-service-2016-ctp-2.3-directquery-in-action.aspx>


new feature in Excel 2016: Forecasting

Posted on Updated on

Just read from Microsoft Blog, that Excel2016 will have forecasting feature! Previously I have only seen this feature was once in Office365′ PowerBI service though was quickly “mysteriously” disappeared – now it’s the first time at least for me – come back, in Excel?

New forecasting capabilities

We also revamped the forecasting capabilities provided in Excel 2016. In previous versions, only linear forecasting had been available. In Excel 2016, the FORECAST function has been extended to allow forecasting based on Exponential Smoothing (i.e. FORECAST.ETS() …). This functionality is also available as a new one-click forecasting button. On the Data tab, click the Forecast Sheet button to quickly create a forecast visualization of your data series. From the wizard, you can also find options to adjust common forecast parameters, like seasonality, which is automatically detected by default and confidence intervals.

Business Analytics 3 - b

Limit of 10 Reports per Dataset/Data Model

Posted on Updated on

NOTE: This information is good as of 7/22/2015 and is subject to change.

*** Update ***

I had posted earlier that this was specific to Free users, but it has nothing to do with Free or Pro. Reposted to reflect the accurate behavior.


I was looking at the Power BI Community Site and someone had indicated that they cannot save a report.

Unable to save report

When looking into that Activity ID, it appears that we have a limit of 10 reports.  This has nothing to do with if you are a Free or Pro user, but rather a resource limitation based on your Dataset/Model.

When you try to save the 11th report against the same Dataset, you get the error above.  If you create an 11th report against a different Model it will work.

Adam W. Saxton | Microsoft Business Intelligence Support – Escalation Services

Refresh for on-premises sources is here!

Posted on Updated on

According to Microsoft PowerBI team:

One of the most awaited and requested feature is now available in Power BI. Starting today, in Power BI you can refresh datasets connecting to your on-premises sources such as SQL Server. Any workbook created using Power BI Designer, Power Query or Power Pivot are supported. For these datasets the Power BI Personal Gateway enables automated refreshes against on-premises data sources in your organization, all via a single gateway you can install directly on your desktop machine.

All common data sources are supported:

  • SQL Server
  • Oracle
  • Teradata
  • IBM DB2
  • PostgreSQL
  • Sybase
  • MySQL
  • SharePoint List (on-premises)
  • File (CSV, XML, Text, Excel, Access)
  • On-premises SQL Server Analysis Services models (uploaded data; not live connections)
  • Folder
  • Custom SQL/native SQL

this is really exciting  – even a normal workstation can do the work!

Dear Accountants: PowerPivot is your friend!

Posted on Updated on

…64bit version is a must have!

by: Mike Griffin

Intro by Avi: Power Pivot and Power BI tools can be used to transform BI for a wide array of industry verticals and vocations. But it is especially suitable to some roles; Accountants are probably at the top of that list! Our friend Mike here is a Financial Manager in the interesting vertical of Cruise Lines. And has a post for us describing just one of the ways they are using the Power BI tools, in this case to find needles in the haystack. Take it away Mike…

Accountants are NOT typically data GEEKS

Accounting related problems open doors to a different set of applications for PowerPivot and PowerQuery. Although it’s fair to say most accountants like numbers, an affinity for numbers does not always translate into a love for data – especially lots of data. This example illustrates how PowerPivot and PowerQuery can be used to help automate accounting related tasks that can be incredibly time consuming when a lot of data is involved.

The scenario I’m presenting is not sophisticated in terms of DAX formulas and is very simple from a data modeling point of view. However, it’s an incredibly useful application of the tools we use as PowerPivot enthusiasts that can save valuable time when closing the accounting period.

The Accounting Need: Remove needles from the hay stack

Use Power Pivot and Power BI to look for the proverbial needles in your data haystack
Use Power Pivot and Power BI to look for the proverbial needles in your data haystack

In this scenario, I need to reverse invoice specific journal entries that were originally posted as part of an automated process between an internal database and our accounting software. This entry is posted as a batch with thousands of other invoices (the original journal entry can’t just be reversed).

A data source is available with details at an invoice level that represent the batch total. However, this data source does not share the same names for the account details required to reverse the original journal entry and it is not data-model friendly.

The Goal:

  • · Extract 17 of the 118 columns available in the detailed data source with the invoice level detail.
  • · Map the 17 columns to the appropriate general ledger accounts in another table and add other details for the journal entry.
  • · Isolate a subset of the invoice level details based on another table with the specific invoices I need.

I’m working with three different tables:

  1. EntryAccounts” table that translates the column labels in my detailed data set into the appropriate journal entry details such as account number, department, etc. (see Step One below)
  2. BookingList” table that helps me extract the specific invoices I’m interested in from the detailed list of thousands. (see Step One below)
  3. RevDetail” table which is a detailed data set with all of the invoice specific information I need (the transformed version of this table is in Step Two below).

Step One:

Create a table that maps the appropriate columns from the data source to the general ledger accounts I want and a list of the specific invoices I want to isolate.

  • · Load each table into PowerPivot
Table One: EntryAccounts Table Two: BookingList
Accounting Detail for PowerPivot Invoice List
The Rev_Description column is a list of the column headers I’m interested in from the detailed data source with invoice specific details. The Invoice List table helps filter the list of thousands of invoices in my large detailed data set to the smaller subset I’m interested in.

Step Two:

I use PowerQuery to turn the detailed data source table with roughly six thousand rows and 118 columns into a three column data-model friendly format. I’ve “un-pivoted” the columns so I can create a relationship between what had been column headers to the unique row values in the Rev_Description column in the “Entry Accounts” table. Note: this step turns the table into over 85K rows in this case. (A tall and skinny table – lots of rows, fewer columns – is far more ideal shape for Power Pivot than short and squat).

This is the PowerQuery view of what becomes the “RevDetail” table in PowerPivot.

Table Three: RevDetail
Unpivot data with PowerQuery Steps to unpivot with PowerQuery
  • · Load the table into PowerQuery, un-pivot the data, & filter the attribute column for the columns I’m interested in. These columns are synonymous with specific account numbers on my “EntryAccounts” table.
  • · Remove unwanted columns and load this table into PowerPivot if using Excel 2010 (the query can be loaded directly into the PowerPivot data model in Excel 2013 – a very useful feature!)

Step Three:

Relate the invoice level data to the subset of invoices & appropriate accounting details.

Create Relationship - revised

  1. The Attribute column in the “RevDetail” table is related to the “Rev_Description” column in my “Entry Accounts” table. The Attribute column had been 17 different columns in the original data source.
  2. The Invoice column in the “RevDetail” table is related to the Booking column of my “Invoice List” table.

Step Four:

Create a pivot table that brings the data source, specific invoices of interest, and mapped accounting details “in line” for review and ready to scan with some VBA.

Flag data with PowerPivot Related

Create calculated columns to create filters for the pivot table output.

PowerPivot Output

I’ve pulled the appropriate details into a pivot table. The relationship between the RevDetail table and the BookingList table help me filter down to the invoices I care about. The relationship between the RevDetails table and the EntryAccounts table allow me to pull in the accounting related detail that is not in my detailed data source. From here, I use a little VBA that scans the pivot table and moves the data into a template that is designed to interface and upload with the specific accounting software being used.

Journal Entry with VBA and PowerPivot

The Result:

This process takes no more than ten minutes to gather and upload all required data, leading to a journal entry that can be uploaded to an accounting system. Creating these entries had taken 15 to 20 minutes per invoice prior to implementing PowerPivot as a tool to automate this process. This example was only isolating four invoices; the list is often longer. The time savings can be huge!

What is Tabular Modelling?

Posted on

just notice some great stuffs in MSDN documentation library. I just wonder why I didn’t see it before!? maybe did but forgot 😦

from below we can go very deep further to the good concept.

Tabular Modeling (SSAS Tabular)

Tabular models are in-memory databases in Analysis Services. Using state-of-the-art compression algorithms and multi-threaded query processor, the xVelocity in-memory analytics engine (VertiPaq) delivers fast access to tabular model objects and data by reporting client applications such as Microsoft Excel and Microsoft Power View.

Tabular models support data access through two modes: Cached mode and DirectQuery mode. In cached mode, you can integrate data from multiple sources including relational databases, data feeds, and flat text files. In DirectQuery mode, you can bypass the in-memory model, allowing client applications to query data directly at the (SQL Server relational) source.

Tabular models are authored in SQL Server Data Tools (SSDT) using new tabular model project templates. You can import data from multiple sources, and then enrich the model by adding relationships, calculated columns, measures, KPIs, and hierarchies. Models can then be deployed to an instance of Analysis Services where client reporting applications can connect to them. Deployed models can be managed in SQL Server Management Studio just like multidimensional models. They can also be partitioned for optimized processing and secured to the row-level by using role based security.

Power BI Designer May Update

Posted on Updated on

by 19 May 2015 11:00 AM (original source: http://blogs.msdn.com/b/powerbi/archive/2015/05/19/power-bi-designer-may-update.aspx)

Today we’re announcing a new update to the Power BI Designer Preview.

This month’s update is packed with lots of new features across the Data Modeling, Analytics, Get Data & Transformations areas of the product:

Data Modeling & Analytics

Get Data & Transformations

You can continue reading below for more details about each item.

Calculated Columns

This month we’re adding the ability to create “Calculated Columns” from the Report view.

With Calculated Columns, you can add new data to a table already in your model. But instead of querying and loading values into your new column from a data source, you create a Data Analysis Expressions (DAX) formula that defines the column’s values.

You can create “Calculated Columns” from the Report view by using the “New Column” button under the “Data Tools – Modeling” contextual tab.

Calculated columns you create appear in the Fields list just like any other field, but they’ll have a special icon showing its values are the result of a formula. You can name your columns whatever you want, and add them to a report visualization just like other fields.

You can watch this feature in action in the following video:

Learn more about Calculated Columns in the Power BI Designer

Data Categorization

Another new feature this month is the ability to specify data categorization for columns loaded into your report. When the Power BI Designer imports data it gathers all the information it can from the source (e.g. it is a primary key), the data in the column, the table and column names, etc.  With that information the Power BI designer makes some assumptions about how to give you a good default experience when creating a visual.  A simple example is if we know that a column is a date time column we’ll assume a time hierarchy for an axis on a line chart.   A more difficult example is geography.

With this month’s update, you can manually specify the categories for your columns by accessing the Data Category dropdown menu in the “Data Tools – Modeling” tab within the Report view.

Watch the following video to learn how to use this feature in the Power BI Designer.

Learn more about Data Categorization in the Power BI Designer.

Sort By Another Column

With Sort by Column, on the Data Tools Modeling tab in Power BI Designer, you can change how values in a column are sorted in a visualization.

When you add a column to a visualization, the default sort order works well most of the time. But, sometimes a column’s natural sort order isn’t really what you need.

Common cases where you would want to use “Sort By” column include: Weekdays or Months (sort days/months by chronological order rather than by alphabetical order), Funnel Stages (sort by logical order rather than alphabetical), etc.

“Sort By Column” can be found under the “Data Tools – Modeling” tab within the Report view.

This option brings up the sorting dialog where you can specify which column you want to sort and which column will define the sorting order.

You can watch the following video for more details.

Learn more about “Sort by Another Column” in the Power BI Designer

Improved DAX Formula Editor: Function Help and Prototype

We’re improving our DAX formula authoring support by offering you function help and prototype tooltips as you author your formulas.

New “ODBC Tables” connector (Beta)

One of the biggest challenges when trying to connect to a data source via ODBC is having to write custom query statements to specify the data that needs to be retrieved.

With this month’s update, we’re making it possible for users to retrieve tables via ODBC without having to provide a query. Simply specify the Connection String and use the Navigator dialog to select one or multiple tables. You can also reshape these tables or mash them up with other tables in the Query view, just like you would do when connecting to other sources.

Learn more about this feature by watching the following video:

Improvements to the “Excel Workbook” connector

We have improved the “Excel Workbook” connector in two different areas this month:

  • Improved Column Type Inference when importing worksheets.
  • Faster load for Data Previews.

New Text Column Filters

In this update we have added a couple of new Text Filters to the Query view: “Does Not Start With” and “Does Not End With”. These filters are available within the Filters menu for Text columns.

Watch the following video to see these new filters in action:

Enhanced Privacy Levels dialog

We are improving the Privacy Levels dialog where users are asked to provide privacy levels for all data sources involved in a query. With this update, users can control whether privacy levels apply to a specific location or a more general one. For instance, control whether privacy levels should be applied to a page vs. an entire site.

See this feature in action:

That’s all for this month. As mentioned previously, we’re making lots of incremental improvements to the Power BI Designer and we hope that you find it better with every new monthly update…

Please continue sending us feedback using our “Send a Smile/Frown” feature, or by voting for what you’d like to see next.

Here is also a full version of the video that combines all What’s New videos from this announcement.