Uncategorized

ByHariharan Rajendran

Power BI with Teradata Report Considerations

In recent times, I am using Teradata Database and connecting with Power BI Desktop and Report Builder. I want to share my experience so that it might be helpful to others.

My setup – I am connecting the Teradata database through VPN and I don’t have access to use windows authentication to connect with Teradata from my system. We need to use LDAP authentication to connect from system to Teradata database.

Quick Bytes – LDAP is nothing but “Lightweight Directory Access Protocol” and it is helping us to access the directory information services over an Internet Protocol (IP) network. You can learn more information in online.

Power BI Desktop

From Power BI Desktop, we can connect to Teradata either using Windows Authentication or Database Authentication.

If you see my above scenario, i can’t connect with windows or database. Teradata DBA has enabled LDAP for any users who want to connect the database using VPN. LDAP is one of the authentication which is not available as default. We need explicitly make LDAP enable for Power BI Desktop.

Need to install Teradata client driver and then need to run the below command in command prompt.

setx PBI_EnableTeradataLdap true

The will enable LDAP as one of the authentication method in Power BI Desktop as like below.

We can use LDAP to connect with Teradata and can import the tables into Power BI.

Power BI Desktop with Teradata via Direct Query.

As we all know, Direct query is something that will run the query against database when user run the report, query will be triggered and run against database through Gateway. My understanding was, we can use any authentication to connect with database from Power BI Desktop and when we publish to service it is always use the account which we use on the gateway data source.  The below scenario gave me the new understanding on Direct Query with Teradata again via LDAP.

I have used Direct query using LDAP and report and  was working fine in the Power BI Desktop. Once I published the report to Service and mapped with Gateway Service account data source, we got the below error message.

When we see the error message, we can understood something that there is an issue with Gateway service account access but really not.

The file which I use same LDAP with Import mode is working fine on schedule refresh but only direct query is not working in the service. We have done many analysis and finally found that LDAP method which is used on the PBIX file is the problem. It means the Power BI Desktop file with Taradata LDAP is not working in the service with Direct Query.

Finally, we got the database account and it resolved the issue.

Conclusion – We need to make sure to choose proper default authentication method in Power BI Desktop when we deal with direct query in Power BI Service.

If anyone from Microsoft team see this, please let me know – is this a bug or known issue?

Happy Learning!!

ByHariharan Rajendran

Raspberry Pi 3

This post helps you to identify the pins in raspberry pi.

In an earlier version of Raspberry Pi, the pin details are clearly specified on the board itself but it is not there on the latest version of Raspberry Pi.

To get the pin details you can type the below command on the console.

pinout

Running the above code will give the details as like below image.

I will be posting IoT related scripts and how Raspberry Pi can be integrated with Azure and Power BI.

ByHariharan Rajendran

Connect Live option disabled

This post solves the very simple problem in Power BI.

If you are trying to access the Power BI datasets to create reports then you may encounter this below error message.

Error: “The connect live option for this file is disabled because it already contains data from another data source.”

“Power BI datasets” is one of the options from “Get Data”.  Whenever you publish a report from Power BI desktop to Power BI services, it is publishing the dataset and report.

You can create multiple reports using the same published datasets from Power BI services. This is something like a shared dataset. In case, if you are experiencing the above error then you need to ask your administrators to fix the issues.

The administrator has to fix this issue on the admin side of the Power BI services. Follow the below steps.

  1. Go Power BI cloud services
  2. Go to the Admin portal
  3. Click Tenant settings on the left side and check the export and sharing settings
  4. Enable the export data feature.

That’s it. The issue has been fixed.

ByHariharan Rajendran

How to handle date in R

Date data type is very important for all programming languages. To handle the date in a proper way, we need to apply some formatting logic.

This is the case for R programming language as well. This post explains you, how we can handle the Date data in R. There are different functions available in R to handle date.

To get the today’s date, use Sys.Date()

Sys.Date()

> Sys.Date()

[1] “2018-05-22”

 

To get date and time, use date()

> date()[1] “Tue May 22 15:29:18 2018”

 

Check the below list of symbols to play with date and time format.

Symbol Meaning Example
%d day as a number (0-31) 01-31
%a
%A
abbreviated weekday
unabbreviated weekday
Mon
Monday
%m month (00-12) 00-12
%b
%B
abbreviated month
unabbreviated month
Jan
January
%y
%Y
2-digit year
4-digit year
07
2007

 

> today <- Sys.Date()> format(today, format=”%B %d %Y”)[1] “May 22 2018”

 

Date Conversion

In a real-time scenario, we will not get date as date data type. Always, we need to convert to proper date data type. To convert to date data type, we need to use as.Date() function.

Syntax

as.Date(x, “format”)

x – Field or column

format – use the above symbols to frame the proper format.

 

DateVec<- c(“01/05/2018”, “08/16/2018”)

DateVec

[1] “01/05/2018” “08/16/2018”

summary(DateVec)

Length     Class        Mode

2            character   character

dates <- as.Date(DateVec, “%m/%d/%Y”)

dates

[1] “2018-01-05” “2018-08-16”

summary(dates)

Min.      1st Qu.       Median         Mean      3rd Qu.         Max.

“2018-01-05” “2018-03-01” “2018-04-26” “2018-04-26” “2018-06-21” “2018-08-16”

ByDr. SubraMANI Paramasivam

Run Your Python Script

Use the below console to run your python scripts

# Assign value to the variable # a = 5 #Print
Print
ByHariharan Rajendran

Live Query Statistics

SQL Server 2016 introduces a new feature called “Live Query Statistics”.  This is the feature available in SQL Server Management studio and visible to everyone.

This live query plan displays below information when executing the query

  1. Overall query progress
  2. Number of rows produced
  3. Elapsed time on each operation

Data is available on run time so not required to wait for the query complete. This helps us to debug the query performance issues.

Steps to use this feature

  1. Check this feature in SSMS
  2. Include (select the icon) the “Live Query Statistics” before executing the script
  3. Execute the Script
  4. Check the Live Query Statistics window.

LiveQuery1

LiveQuery2

LiveQuery3

 

LiveQuery4

1