Power BI Mobile news February 2017

Today Microsoft has released a new list of feature about Power Bi Mobile application. Here’s the complete list of recent updates:

  • SSRS Authentication using Active Directory Federation Services, ADFS Preview: With single sign-on, you only have to sign in once with your organizational identity to explore all of your SSRS mobile reports and KPIs.
  • Load more than 100 rows in tables and matrices: If you have a large table or matrix on your dashboard or report, report will show as much data as possible in the tile
  • New and improved Annotate and share insights instantly: The new share and annotate capability has an improved menu, making it easier and quicker to annotate and share insights with your colleagues. Additionally, you can also share an annotated report or directly from the Power BI app.
  • Phone report – general availability: With phone reports, you can specifically tailor a portrait view of your existing report on Power BI Desktop for mobile viewers.

 

Run stored procedures with report data as input parameters

How can I run stored procedures with report data as input parameters?

Stored procedures are one of SQL’s most powerful tools to update, insert or delete data in your database. Since most reports are designed to provide Business Intelligence to users, there could be an action the user needs to take based on the presented data. Wouldn’t it be great if the user could take that action without leaving the report?
This post describes how to run a stored procedure directly from your report with row data as input parameters.
I will use a simple example with a custom table and a small stored procedure so you get the picture. Soon you will discover that with these steps and a creative mind, the options are near limitless for taking direct actions in your database! Think about checklist reports or scheduling reports.
1) Create a custom table in your database (you can also use existing tables if you know what you’re doing)
Create TABLE [dbo].[AdvancedSSRS_CusOrd]
([ID] [int] IDENTITY(1,1) NOT NULL,
[Account] [Varchar](50) NOT NULL,
[OrderNo] [Varchar](50) NULL
) ON [PRIMARY]
GO
2) Create a stored procedure that Inserts new records in the table created in step 1
SET ansi_nulls ON 
go 

SET quoted_identifier ON 
go 

CREATE PROCEDURE Advancedssrs_insertintoadvancedssrs_cusord 
@Account VARCHAR(50), 
@OrderNo VARCHAR(50 

AS 
  BEGIN 
      SET nocount ON; 

      –Check if record already exists 
      IF EXISTS (SELECT NULL 
                 FROM   advancedssrs_cusord 
                 WHERE  account = @Account 
                        AND orderno = @OrderNo) 
        — if exists 
        BEGIN 
            UPDATE advancedssrs_cusord 
            SET    account = @Account, 
                   orderno = @OrderNo 
            WHERE  account = @Account 
                   AND orderno = @OrderNo 
        END 
      — if record is new 
      ELSE 
        BEGIN 
            INSERT INTO advancedssrs_cusord 
                        (account, 
                         orderno) 
            VALUES      (@Account, 
                         @OrderNo) 
        END 
  END 

go 
Since I don’t want to get multiple records of the same Customer/Order combination I included a check to see if the record exists. If it does, the stored procedure will overwrite the row with the same values. If it doesn’t, the stored procedure will add an extra row.
3) Add report parameters that will serve as input parameters for the stored procedure
In this example I want to mark some orders as “special”, so I will add a parameter for @Account and @OrderNo. Make sure to allow blank values.
 
4) Add a dataset that exectutes the stored procedure created in step 2
The dataset checks if the report parameter(s) are not NULL (which is how the report runs by default). Only if the parameters have values, the stored procedure will be executed.
If you use data from your custom table in your report, you want to make sure this dataset is the first dataset the report will run. Unfortunately you cannot move the position of the dataset, so if you have an existing dataset, copy the query, delete the dataset and recreate it.
5) Add a column in your Tablix to launch the stored procedure
Insert text or an image and go to its properties. Browse to the Action tab. Choose to “Go to report” and choose the report you are working on. Add parameters to pass on so you pass the value in a row to the report parameters.
You see where this is going? Once the user cicks the image/text, the report will be launched again (refreshed) but this time the parameters are not blank. This will trigger the dataset created in step 4 to exectute the stored procedure. In my scenario a row will be inserted in the table created in step 1.
6) Test your report
7) Hide the report parameters

Use Change Tracking on SQl server

Here, we will explain change tracking functions, show code examples and demonstrate how to read the Change Tracking results

Change tracking functions

There is no out-of-the-box option to see the change tracking information. To see the rows that were changed and change details, use change tracking functions in T-SQL queries [1]

The CHANGETABLE(CHANGES) function shows all changes to a table that have occurred after the specified version number. A version number is associated with each changed row. Whenever there is a change on a table where Change tracking is enabled, the database version number counter is increased

The CHANGETABLE (VERSION) function “returns the latest change tracking information for a specified row“ [2]

SELECT * FROM CHANGETABLE(CHANGES <table_name>, <version>) AS ChTbl

Note that the table used in the CHANGETABLE function has to be aliased

Table changes that have occurred after the specified version number

The CHANGE_TRACKING_CURRENT_VERSION() function retrieves the current version number, i.e. the version number of the last committed transaction

SELECT NewTableVersion =  CHANGE_TRACKING_CURRENT_VERSION()

Returns NULL if Change tracking is not enabled, an integer otherwise. The minimal value returned is 0. In the example above, it returns 17

The CHANGE_TRACKING_MIN_VALID_VERSION() function shows the minimum version number that can be used to get change tracking information for the specified table using the CHANGETABLE function

SELECT MinVersion = 
CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('Person.Address'))

In the example above shows 14

The CHANGE_TRACKING_IS_COLUMN_IN_MASK function shows whether the specific column was updated or not. If it was updated, the value is 1, otherwise 0. It can only be used if the TRACK_COLUMNS_UPDATED parameter for enable change tracking on a table is set to ON

Reading the Change Tracking results

Here’s an example for the data changes executed on the Person.Address table

  1. Execute
    SELECT TableVersion = CHANGE_TRACKING_CURRENT_VERSION();
    SELECT * FROM Person.Address;
    

    The Change Tracking results show that this is the first version of the tracked table and the current records in the Person.Address table

    Change tracking results - the first version of the tracked tables

  2. Modify the records in the Person.Address table, either using T-SQL or editing rows in the SQL Server Management Studio grid. The changes I made are highlighted – I updated the rows with AddressIDs 1, 5 and 2, in that order

    Modifying records using T-SQL or editing rows in SSMS

  3. I added a row. Note that the AddressID is 32522

    Row is added into a table

  4. I deleted the row I added in the previous step
    DELETE Person.Address WHERE addressid = 32522;
  5. To read the Change Tracking results, execute
    SELECT
    NewTableVersion = CHANGE_TRACKING_CURRENT_VERSION()
    
    SELECT
    ChVer = SYS_CHANGE_VERSION,
    ChCrVer = SYS_CHANGE_CREATION_VERSION,
    ChOp = SYS_CHANGE_OPERATION,
    AddressID
    FROM CHANGETABLE(CHANGES Person.Address, 1) AS ChTbl;
    

The results are:

Showing current results

The values shown in the ChOp column indicate the changes made. ‘U’ stands for update, ‘D’ for delete, ‘I’ for insert. There are three updates on the rows with AddressID 1, 2, and 5 and deletion of the row with AddressID = 32522. There is no clear indication that the 32522 row was first inserted, but according to the Change Creation Version (ChCrVer) and Change Version (ChVer) values 5 and 6, there were 2 changes. The second one was a delete, but we don’t know what the first one was

I re-inserted the same 32522 row and refreshed the results

Re-inserting the same row and refreshing the results

As expected, the current version number is 7, increased by 1 as there was one more change. But the information about the 32522 row is even vaguer when it comes to row history

Tracking individual column updates

If you add the SYS_CHANGE_COLUMNS column to the query, you will get the binary number of the column that was changed. The value is NULL only if the column change tracking option is not enabled, or all columns expect the primary key in the row were updated

Showing binary number of the changed column

“Column tracking can be used so that NULL is returned for a column that has not changed. If the column can be changed to NULL, a separate column must be returned to indicate whether the column changed.” [2]

To present column changes in a more readable format, use the CHANGE_TRACKING_IS_COLUMN_IN_MASK function. It has to be called for each column individually. In the following example, I’ll check whether the columns AddressLine1 and AddressLine2 have been modified

SELECT
ChVer = SYS_CHANGE_VERSION,
ChCrVer = SYS_CHANGE_CREATION_VERSION,
ChOp = SYS_CHANGE_OPERATION,
AddLine1_Changed = CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (COLUMNPROPERTY(OBJECT_ID('Person.Address'), 'AddressLine1', 'ColumnId')
    ,ChTbl.sys_change_columns),
AddLine2_Changed = CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (COLUMNPROPERTY(OBJECT_ID('Person.Address'), 'AddressLine2', 'ColumnId')
    ,ChTbl.sys_change_columns),
AddressID
FROM CHANGETABLE(CHANGES Person.Address, 1) AS ChTbl;

Using column tracking to change the column

The value 1 in the AddLine1_Changed and AddLine2_Changed columns indicates that the specific column has been changed

As shown, SQL Server Change Tracking is a synchronous process that can be easily configured on your tables. It is supported in all SQL Server editions, so there are no additional licensing expenses. It can be utilized in applications designed for one-way and two-way data synchronization, as it can seamlessly synchronize several databases, each at a different time

The Change Tracking feature is not designed to return all information about the changes you might need, it’s designed to be a light auditing solution that indicates whether the row has been changed or not. It shows the ID of the row changed, even the specific column that is changed. What this feature doesn’t provide are the details about the change. You can match the change information to the database snapshot and the live database to find out more about the changes, but this requires additional coding and still doesn’t bring all the information that might be needed for auditing

Change tracking doesn’t answer the “who”, “when”, and “how” questions. Also, if there were multiple changes on a specific row, only the last one is shown. There is no user-friendly GUI that displays the results in just a couple of clicks. To see the change tracking records, you have to write code and use change tracking functions

The execution of the SELECT statements and database object access is not tracked. These events have nothing to do with data changes, but as SQL DBAs request these features when it comes to auditing, it should be mentioned

SQL: Compare the columns data types of two different tables

Often using SQL we need to compare the data types of the columns of two different tables. For example, when we bring the data from the Stage to the data warehouse, or if we have to put into union two tables with similar data.

When this happens, more often in error or distraction it appears there the usual generic error in the case in which the types of the two data tables are incorrect.

is very difficult to identify where the error is because sql does not show the column, I wrote this simple script that allows you to identify the error:

Continua a leggere

Power BI January Update

It’s a new year and Microsoft has just started with a new update for power bi, in particular for the desktop version:

Report View

  • Table header word wrap: Word wrapping splits on spaces and on long words with no hyphenation if they can’t fit on a single line alone.
  • Table & matrix conditional formatting improvement – blank formatting: When you open the conditional formatting dialog, there is a new section, Format blank values, where you can pick the formatting method you want to use for your blank values.
  • New aggregations for string and dateTime columns : For dateTime columns, you can change the aggregation to Earliest or Latest in the right click menu of the field in the chart. For strings, you can change the aggregation to First or Last in the same menu
  • Phone reports is in general availability from now

Data Connectivity

  • Visual Studio Team Services connector
  • Enhanced SQL Server connector – support for SQL Failover option

Query Editing Improvements

  • New transform: extract values from a nested list: allows you to extract values from a list into a new Text column, with a delimiter in between these values. This new transformation can be accessed from the column header when a column with nested lists is selected.

R graphs and tables in Power BI Desktop

TomazTsql

Power BI Desktop enable users to use R script visual for adding custom visualization generated with R language – regardless of R package used. Before using R script visual, you will need to enable it by setting path to R Engine on your client in the global options. Once this is done, you will be able to enhance your Power BI reports using R visualizations.

1450757873470

My personal favorite R packages I like to use in Power BI are shiny, plotly, ggplot2, googleVis, ggiraph, rCharts and d3js.  Apart from finding the package for data visualization where you feel home, one of the very great thing I find with Power BI + R is the ability to have visualizations created dynamically (more or less like if you would be using plotly or shiny)  with slicers and selectors. And this makes data exploration, data wrangling so much easier.

With October 2016 Power BI…

View original post 566 altre parole

Use Excel files on sharepoint with power bi

This post was create by Ken Pulse and Miguel Escobar and published on power bi Blog. I thinks it’s very interesting use of Power BI and Sahrepoint and i suggest you to read it:

clip_image002

Have you ever wanted to consolidate, combine or append data from multiple excel spreadsheets or workbooks? I bet you have, and it has been a painful process either done manually or via 3rdparty add-ins. In this blog post, we’ll show you how, with the newest version of the Power BI Desktop and Power Query for Excel, you can combine multiple data from Excel files into one big tall table.

The Scenario

clip_image004

In this specific case, we have an Office 365 group that we’ve created where we store some external sales data that do not come from our system. These files are provided by a 3rd party in order to give us a better understanding of the whole market and how well some products are doing on each market. Here’s a few remarks about our case:

Each of these files has only 1 month of data  we’ll have a file for January with just 1 sheet in it that will have all of the data for January.

These are all Excel files living a happy life in SharePoint – we get these files via email from the provider and then store them on our Office 365 group every month.

All of the files have the same structure, but different sheet names – all of the files only have 1 sheet and all of them have the same headers, but in a real case scenario the Power BI desktop and the process described here will work if you don’t have the same headers on all of the files.

Now, what we want to do is something that can be summarized in a series of steps like the following:

  1. Access all of the files from our site – we need to connect to our O365 group or SharePoint site and get the files first
  2. Combine all of the data from the files into one big table – once we connect to this data source, we need to be able to combine/consolidate/append all of the data from each of those excel files into one big table
  3. Clean and transform the data – ultimately, we want to unpivot some columns and also make the table more manageable to use with the Power BI Desktop and the DAX language.

In the end, we want the result of the combination to look similar to this:

clip_image006

Getting Data from SharePoint

First things first, we need to get the data and the Power BI Desktop makes it easier for us to get our data from either SharePoint or any O365 Group storage.

From the Get Data experience, let’s find the option for “SharePoint Folder”. This option will only require a text string which would be the URL for a SharePoint site.

clip_image008

Where can I find this so called url? Which one of the urls found in SharePoint should I use??

Good question! What the tool is expecting here is the root URL to the SharePoint site itself. Here’s a quick example:

clip_image010

My SharePoint site root url is https://powerbipanama.sharepoint.com/, but all of my files are actually in another site that starts with https://powerbipanama.sharepoint.com/sites/externalsales/ URL.

In order to use the correct URL, we need to be in the folder of the data that we’re trying to get and check the url that our browser shows. If it has the if it starts with the format of https://<site address>/sites/<sitename>/ then we need to use that url, otherwise we use the much simpler https://<site address>

In my own case, I’ll be using the https://powerbipanama.sharepoint.com/sites/externalsales url in order to connect to my site.

Once you input the url, you’ll then be asked for your credentials – be sure to select the “Organizational” type for your credentials and enter the same credentials that you use for your SharePoint site.

clip_image012

Now that we’re in, we’ll be able to see all of the files from all of the folders that we have access to in that specific site. This might be a pretty long list, so let’s do some filtering on the “Folder Path” column and select the folder where your data is being stored so you only get the files from it.

clip_image014

Filtering is key so you always get the files from the folder that you want. You can select multiple folders if you’d like as well.

We now have all of the files that we want to combine but, how do we combine these files?? What kind of magic is needed to consolidate all of them? Surely it can’t be just 1 click to combine them, right? Let’s find out.

New functionality! Combine Excel files with just 1 click

clip_image016

You might remember this popular posts from Microsoft itself on how to combine text and CSV files. It used to throw weird results with Excel files, but that changed with the latest version of the Power BI Desktop and Power Query.

This is now what happens after you click that button:

1. A new window will appear called ‘Combine Binaries’ – it will first find the first Binary from the table, which in our case is 03-March.xlsx, and it’ll create a sample function that will be applied to all of the other binaries.

clip_image018

We recommend always selecting the ‘Sample Binary Parameter’

In this window, you’ll see all of the objects found in the 03-March.xlsx file. If you select for example the 03-March spreadsheet, the same process will be applied to the rest of the binaries so Power Query/Power BI Desktop will extract the data from the 03-March spreadsheet from each file.

This is not our case. Each of our files has a different sheet name, so we recommend for these of cases to select the “Sample Binary Parameter” and click OK on this window.

2. Power Query / Power BI Desktop will do all the heavy lifting for you – you’ll end up with a table like the one we see in the next image where you can see all of the objects from each file. In our case, we see from the “Kind” column that all of our objects are simply sheets from the workbook and on the Name column we see the name of the workbook where each object comes from

clip_image020

After just 1 click, you have all of the data ready to be combined on the “Data” column. The elements within the red and orange outline were automatically created by the tool.

3. Expand the data found in the Data column – you’re one step away from consolidating all of your data! Just click the button with the double opposite arrow found on the “Data” columnclip_image022

Be sure to always click on “Load more” so you can expand the columns from all of your tables

At this point, we can check that our data has been consolidated in one table

clip_image024

YES!!!! All of the data from all of the 6 Excel files has been appended and now we have a table with all of this data.

Some more cleaning and transformation steps

clip_image026

After everything has been consolidated, we jump ahead and perform some cleaning and transformations shown within the outlined orange area

Here’s a quick list of the final steps that we did:

  • Removed Columns – we removed some unnecessary columns like the “Kind”, “Hidden” and “Item” column
  • Promoted Headers – after expanding the data, we noticed that the first row had the column headers so we immediately promoted the first row to become headers of the table
  • Renamed Columns – we simply renamed some of the columns like the “Name” column, which had the name of the product, to be named “Product Name”
  • Filtered Rows1 – since all of our files had headers, some of them were found in some rows, so we filter those out
  • Unpivot Other Columns – we used the unpivot feature on all the columns except the name of the month and the name of the product so we could get the “Attribute” and “Value” columns
  • Renamed Columns1 – we renamed the “Attribute” column to be “Market” and the “Value” column to be “Sales”
  • Sorted Rows – we sorted the data (ascending) by the Month column
  • Changed Type – we defined the data types for each of the columns and hit Close & Apply

The Result

clip_image028

Last but not least, get creative and create a report based on your consolidated data!

Of course, you could integrate this new table to an existing data model to add even more context to your sales performance and understand how you’re doing in each market.

Power BI Mobile December News

Microsoft some days ago introduce the latest update for Power BI mobile apps! Within this update, you’ll find these new capabilities:

  • New and improved Annotate and Share (iOS): improved the menu and user experience to make it even easier and quicker to annotate and share.can now also share an annotated report or mobile-optimized report directly from your Power BI app. when you share a snapshot via email we will also generate a deep link to the tile or report. When your colleague receives your email, they can continue their exploration in Power BI by clicking the link

AnnotationiOSGiff

  • Request access to dashboards directly from your mobile app (iOS) For those who scan a QR and don’t have access to the original dashboard, they can now submit a request for access directly from their mobile app.
  • Android Tablet preview
  • Geographic filtering (Android) ability to filter reports based on your current location on android app. (and with ios)
  • Apple watch improvements (iOS) : You can now refresh Apple Watch data directly from the Watch app
  • Custom URL on image tile (iOS, Android): If an image tile has a custom URL defined by the dashboard owner, when you tap the tile you go directly to that URL without opening the tile in focus mode
  • Offline background refresh adjustments (All): The refresh of data is performed in the background of the app, that ensures your business information will be up to date, even if you haven’t accessed it for a while.

Pyramid 3d on power bi

Microsoft released a new custom visuale create by Collatbion a 3d Pyramid.A pyramid chart represents data in the form of percentages, with the whole chart representing 100%.

image

Download Pyramid 3D chart by Collabion from the custom visuals gallery.

Pyramid 3D charts work best when you want to visualize data that diminishes in value with every successive stage.

You can utilize a 3D pyramid chart better by following the tips mentioned below:

  1. Sort the data in the chart.
  2. Click on one of the segments to make it pop out.
  3. Hover the pointer over one of the segments and the tooltip will give you a snapshot of the data without having to switch back to the data source.
  4. Customize chart title to indicate the subject matter more clearly.
  5. Try to avoid using lighter shades of colors in the chart.