Power BI November News

Today we released the November update of the Power BI Desktop. Let’s see the news:

Report View

  • Dropdown slicer:When you create a normal list slicer, you can turn it into a drop down by using the dropdown option next to the Clear button.
  • Hierarchical axis: improving the experience around viewing visuals that contain a hierarchy. You can use hierarchical labels by turning off the label concatenation experience in the formatting pane.

clip_image002

  • Matrix conditional formatting You can turn on conditional formatting by opening the field well menu for the measure and selecting Conditional formatting.
  • Table & matrix column formatting ability to format columns in tables and matrices. This lets you set the font and background color for any column.
  • Axis label and title color control you can now format your axis title color separately from the axis labels.
  • Mobile reports scrolling You can now extend the length of your mobile report and allow users to scroll to see the additional content.

Analytics

  • Clustering (preview): Clustering lets you quickly find groups of similar data points in a subset of your data.You can use clustering on your scatter chart by clicking the “…” and then selecting Automatically find clusters.When you select this, a dialog opens where you can decide how many clusters you want us to find. If you leave it blank, we will automatically find the number of clusters we think makes the most sense with your data.

clip_image004

  • Forecasting – now available on Power BI on the web: With this release, the feature is generally available to everyone, so you no longer need to enable it through the Options dialog. Additionally, you can now see your forecasting in reports on the web and pinned tiles.
  • Groups – ribbon entry point You can create and edit groups from the Modeling tab. You can also create a new group from the Data / Drill tab when you are on a chart.

Data Connectors

  • Spark DirectQuery This includes the HDInsight Spark connector (under Get Data -> Azure) as well as the Spark connector for other Spark distributions (under Get Data -> Other).
  • OData connector – folding of expand record operations: adding support for the underlying OData Service doing the expand record operation rather than having this operation done in Power BI.
  • Enhanced “combine binaries” experience:
    • It analyzes the input files and detects the right file format to use
    • it provides you the chance to select a specific object from the first file
    • It automatically creates a few different artifacts
  • Azure Analysis Services: You can connect to Azure Analysis Services in the Power BI Desktop.To connect, select Analysis Services in the Get Data experience.
  • Web connector updates: new datasets that leverage import from Web pages won’t be automatically configured for refresh in Power BI on the web and will require the use of a gateway.

Query Editing Improvements

  • Improved “function authoring” experience: When a function definition has been created based on another query (i.e., using the “Convert to Function” option on the Queries context menu), a link will be created between the original/exemplar query and the newly generated function, so that you can simply modify the exemplar query steps and the linked function will be automatically updated for them.
  • Support for percentage data type: An input value such as “5%” will be automatically recognized as a Percentage value and converted to a 2-digit precision decimal number
  • Maximize/Restore buttons in the Navigator and Query Dependencies dialogs: The Navigator and Query Dependencies dialogs support window resizing by dragging the bottom/right edges of the dialog.In this release it’s possible to maximize/restore these dialogs by exposing Maximize and Restore icons in the top-right corner of the dialogs so that they behave like standard Windows dialogs.
Annunci

Deleting all duplicate rows but keeping one

When you work with Power Bi or Power Pivot duplicates are not allowed but due to different problems or error I know there are some duplicates in this table.

I need to eliminate the extra rows from the perspective of the key columns. Some other columns may have slightly different data but I do not care about that. I still need to keep one of these rows however.

SELECT DISTINCT won’t work because it operates on all columns and I need to suppress duplicates based on the key columns.

How can I delete the extra rows but still keep one efficiently?

It’s very simple you have only to use this query:

WITH cte AS (
  SELECT[KEY1], [KEY2], 
     row_number() OVER(PARTITION BY KEY2, KEY1 ORDER BY KEY1) AS [rn]
  FROM TABLE
)
DELETE cte WHERE [rn] > 1


With this simple query you can delete al duplicate row keeping one. You can use order by to keeping for example the last raw (order by date asc).

Create a dynamic (automatically updated) Excel dropdown

If you often edit the items in the drop-down menu, you may want to create a dynamic drop down list in Excel. In this case, your list will get updated automatically in all the cells that contain it, once you remove or add new entries to the source list.

The easiest way to create such a dynamically updated drop-down list in Excel is by creating a named list based on a table. If for some reason you prefer a usual named range, then reference it using the OFFSET formula, as explained below.

  1. You start by creating a usual dropdown based on a named range as described above.
  2. In step 2, when creating a name, you put the following formula in the Refers to box.=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

    Where:

    • Sheet1 – the sheet’s name
    • A – the column where the items of your drop-down list are located
    • $A$1 – the cell containing the first item of the list

As you see, the formula is comprised of 2 Excel functions – OFFSET and COUNTA. The COUNTA function counts all non-blanks in the specified column. OFFSET takes that number and returns a reference to a range that includes only non-empty cells, starting from the first cell you specify in the formula.
Creating a dynamic drop-down list in Excel using the OFFSET formula

 

The main advantage of dynamic drop-down lists is that you won’t have to change the reference to the named range every time after editing the source list. You simply delete or type new entries in the source list and all of the cells containing this Data Validation list will get updated automatically!

Pushing Data From Excel To Power BI Using Streaming Datasets

Very interesting Article!

Chris Webb's BI Blog

One Power BI feature that almost passed me by (because it was released in August while I was on holiday) was the ability to create streaming datasets in the Power BI web app and push data to them via the Power BI REST API. This blog post has the announcement:
https://powerbi.microsoft.com/en-us/blog/real-time-in-no-time-with-power-bi/
The documentation is here:
https://powerbi.microsoft.com/en-us/documentation/powerbi-service-real-time-streaming/
And Charles Sterling has an example of how to use it with Flow and PowerApps here:
https://blogs.msdn.microsoft.com/charles_sterling/2016/10/17/how-to-create-and-customize-applications-with-powerapps-with-laura-onu-webinar-1020-10am-pst/

However, when I played around with this I found there were a few things that were either confusing or not properly documented, so I thought it would be useful to give an example of how to use this functionality to automatically synch data from a table in Excel to Power BI using a Power Query query.

Creating the streaming dataset in Power BI

Imagine that you have a table called Sales in an Excel workbook on your…

View original post 1.143 altre parole

Columnstore Indexes SQL 2016

The columnstore index is the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over traditional row-oriented storage, and up to 10x data compression over the uncompressed data size. Beginning with SQL Server 2016, columnstore indexes enable operational analytics, the ability to run performant real-time analytics on a transactional workload.

These are key terms and concepts are associated with columnstore indexes.

columnstore

A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.

rowstore

A rowstore is data that is logically organized as a table with rows and columns, and then physically stored in a row-wise data format. This has been the traditional way to store relational table data. In SQL Server, rowstore refers to table where the underlying data storage format is a heap, a clustered index, or a memory-optimized table.

column segment

A column segment is a column of data from within the rowgroup.

Delete bitmap:

cancellations are logical, are erased when you recalculate

Delta rowgroups:

inserted rows that have yet to be compressed

Clustered Columnstore Index

They can even be created on tables in memory that does not. it’s handled as all other indices. Through the Delay feature is possible to force the delay after a certain time instead of reaching the number of rows. You can also Filter data and exclude  certain indices values (eg the deleted parts)

How to write it:

— Create a clustered columnstore index on disk-based table.
CREATE CLUSTERED COLUMNSTORE INDEX index_name
ON [database_name. [schema_name ] . | schema_name . ] table_name
[ WITH ( < with_option> [ ,…n ] ) ]
[ ON <on_option> ]
[ ; ]

— create a durable (data will be persisted) memory-optimized table
— two of the columns are indexed
CREATE TABLE dbo.ShoppingCart (
ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
CreatedDate DATETIME2 NOT NULL,
TotalPrice MONEY

INDEX ShoppingCartId CLUSTERED COLUMNSTORE
) WITH (MEMORY_OPTIMIZED=ON)
GO

Here there are the principal differences between sql 2014 and sql 2016 :

Columnstore Index Feature SQL Server 2014 SQL Server 2016
Batch execution for multi-threaded queries yes yes
Batch execution for single-threaded queries yes
Archival compression option. yes yes
Snapshot isolation and read-committed snapshot isolation yes
Specify columnstore index when creating a table. yes
AlwaysOn supports columnstore indexes. yes yes
AlwaysOn readable secondary supports read-only nonclustered columnstore index yes yes
AlwaysOn readable secondary supports updateable columnstore indexes. yes
Read-only nonclustered columnstore index on heap or btree. yes yes*
Updateable nonclustered columnstore index on heap or btree yes
Additional btree indexes allowed on a heap or btree that has a nonclustered columnstore index. yes yes
Updateable clustered columnstore index. yes yes
Btree index on a clustered columnstore index. yes
Columnstore index on a memory-optimized table. yes
Nonclustered columnstore index definition supports using a filtered condition. yes
Compression delay option for columnstore indexes in CREATE TABLE and ALTER TABLE. yes

Power Pivot, decimal separator error

When working with Decimal number data in PowerPivot, you might see different general error during the import of new file or when import it the decimal number is without comma or point.

These issues can occur when the data source in your workbook is a comma separated values (CSV) file that was created on an operating system (OS) with an English locale and being accessed on an OS with a non-English locale. The Microsoft Jet Database Engine is unable to directly handle certain values in the data.

It cannot handle certain values when the format in the Regional and Language settings of the OS is set to use a character other than a period (.) as a decimal separator. For example, your OS settings may be set to use a comma (,) for a decimal separator.

To resolve this issue, do one of the following tasks:

Replace the decimal separator:  Teplace the decimal separator in the CSV file with another character, such as a semicolon (;). To do this, use the find and replace feature in the text editor you use to open the CSV file, find all characters that are used as a decimal separator, such as a period, and replace with a semicolon. It’s works oly if you csv si very small and simple to edit.

Create a schema.ini file: You can create a standard schema.ini file that will tell Jet how to read the CSV file, regardless of the regional settings, it’s work with all csv type of file:

  • First create e normal txt file and convert it to .ini ( rename the extension)
  • Call the file Schema.ini and save the schema.ini file in the same directory as the CSV file.
  • Include a line that defines the current decimal separator as used in the CSV Using the example above, the first two entries in your schema.ini file will look like the following:

[filename.csv]
DecimalSymbol=.

the first row is the nam of you csv, the second one set the character for your decimal symbol in your csv. You can also set the column name, his type and leght using these string:

Col1=CustomerNumber Text Width 10
Col2=CustomerName Text Width 30

Col is the position of colum( from left to right in you csv.

For more information about the schema.ini file, go to the MSDN website: http://msdn.microsoft.com/en-us/library/ms709353%28VS.85%29.aspx.