Monday, July 31, 2017

SQL Server bcp error - String data, right truncation

We generally expect a proper row terminator with files loaded using bcp and with all accurately defined switches, data should be loaded without any issue. But sometime, you may see issues like below even if you try with switches like -r "\r\n" or -r "\n".

In most cases, the reason is the row terminator. If you open the file to be loaded using something like Notepad++, and enable Show All Characters (icon is in the toolbox), you should see the exact row terminator.

If you see something like above, then you need to add the row terminator as a hexadecimal value. In this case, if I add -r "0x0a", then my data loading will work without any issue.

Tuesday, July 11, 2017

Power BI supports numeric range slicer now

This is not something released this month but March 2017. These new capabilities of Power BI Slicer is available but many do not use it because it is still under Preview and it has some limitations. But, let's see how useful it is and what sort of limitations it has.

As you see below, I have created a Power BI Report connecting with AdventureWorks2014 local database using DirectQuery mode. This is the view I used as the source;

USE AdventureWorks2014;

CREATE VIEW dbo.SalesByCustomer
      p.LastName + ' ' + p.FirstName Customer
   , t.Name
   , SUM([SubTotal]) Amount
  FROM [Sales].[SalesOrderHeader] s
 INNER JOIN Sales.Customer c
  ON s.CustomerID = c.CustomerID
 INNER JOIN Person.Person p
  ON p.BusinessEntityID = c.PersonID
 INNER JOIN Sales.SalesTerritory t
  ON s.TerritoryID = t.TerritoryID
GROUP BY p.LastName + ' ' + p.FirstName, t.Name;

The table of the report is created with Customer and Amount. Then two slicers are added, one using Amount and other user Name (Territory).

You will not see Numeric Range Filter unless you have enabled it. For enabling it, go to File -> Options and settings -> Options -> Preview features and check Numeric range slicer.

Once it is enabled, whenever a numeric value is dragged to a slicer, range slicer will be appeared automatically.

Multiple options such as Less than or equal to, Greater than or equal to are given with it for filtering based on values in the input boxes.

We can use this without any issue but you will face a limitation if you publish this to Power BI Service and view it.

As you see, this feature is still not available in Power BI Service. There are two more things to remember on this feature, 1) measures that are created with the model or measure in Analysis Services models cannot be used with this, 2) this filters row data that come from the source, not aggregated data shown in visuals.