This post explains that anyone can get R visuals for their data with ggplot2.
Power BI is very famous for different types of visualizations right from out of the box visualizations, R visualizations and custom visualizations.
To generate R visuals, you need to write R codes on the editor window. It is possible when you have a basic knowledge of R packages. Microsoft makes this process easy and provided many R custom visuals which is like other visuals where you can drag and drop the fields on the respective axis.
R custom visuals doesn’t require R codes.
Another option that you can easily create R visuals using ggplot2 builder without writing much code.
Esquisse is a small add-in to R studio helps you to interactively explore your data by visualizing it with the ggplot2 package. It allows you to draw bar graphs, curves, scatter plots, histograms, then export the graph or retrieves the code generating the graph.
Refer the below URL,
https://github.com/dreamRs/esquisse
Follow the below steps to use this on your Power BI Desktop.
You need to have R studio installed on your machine
Install the devtools using below command.
install.packages(“devtools”)
Install the “dreamRs/esquisse” using below line of code
devtools::install_github(“dreamRs/esquisse”)
install.packages(“ggplot2”)
You need to perform the above steps in the R studio.
Once above steps are performed then you are good to go with Power BI Desktop.
You can take any dataset which you want to use it for R visuals. Try to have different columns like date, number string to experience all the visuals from ggplot2.
Add the R visual into the canvas area and it will open the editor below. To add a R script, you need to choose the fields from your dataset.
When you added the fields, you can see the R editor enabled to write codes.
Copy and paste the below line of code and click the run button.
esquisse:::esquisser()
It will open your browser and load the ggplot2 builder.
It will look like below.
First, you need to choose the dataset from the drop down of the data.frame list.
It will show all the columns as variables. You need to select all or whichever you want to use it for your visuals. Then finally choose the selected variables will be added.
On the Left top, you can see “auto”, you can click and see what are the visuals are supported as of now.
Next, you need to drag and drop the variables on respective sections. The sections are,
X Axis
Y Axis
Fill
Color
Size
Based on the data type you can choose different visuals. Sometimes few visual will not be enabled, if that is the case then your data is supported for those visuals.
To customize the visual, you can use the bottom bar options.
Label & Title
Plot Options
Data – Filter
Once you satisfied with the visual then you can copy the generated code and paste it on the R editor in Power Bi Desktop.
Before running the script, you need to comment on the previously added line of script and add the below line of code.
Library (ggplot2)
Then run the script.
It will generate the R visual on your report.
Thanks. Let me know if you have any doubts. Reach me on hari@yoursqlman.com.
This post explains the concept of below DAX functions
I need to explain the data modelling little bit before going to DAX functions. As you know, we need to create a relationship either manually or automatically to use the tables together to get the expected result.
We have 3 different relationships available.
In the context, let us consider a one-to-many relationship. If you have CUSTOMER master and FACT tables then trying to create a relationship, then that would create a one-to-many relationship.
It means we have unique records on the customer table but multiple same values on the FACT table with reference to the connected columns.
If you want to create a new calculated column on the CUSTOMER table then you need to use RELATEDTABLE DAX function. On the other hand, when you want to create a calculated column on the FACT table then you need to use the RELATED function.
RELATEDTABLE:
Customer First Purchase = MINX(
RELATEDTABLE(‘Sale’),
‘Sale'[Invoice Date Key])
RELATED:
State = RELATED(‘City'[State Province])
This post helps you solve the below issue in Power BI.
Scenario: There will be an error when we connect with 32-bit Access Database from Power BI 64-bit desktop.
Error: “Microsoft Access: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine”.
Solution
To solve the above issue, follow the below steps.
Once completed the above steps, try to connect access DB from Power BI desktop. This time you will be able to connect without an issue.
Post your comments if you get any more issues in this scenario.
Power BI recently announced a new feature to customize the tooltips. This post explains the steps to create a customized tooltip to improve your analytics.
As you know, tooltips are an important feature of every business intelligence project. It helps end user to understand the values when they mouse hover on any visuals on the report.
Traditionally, it will give us the very minimal version of the information. Microsoft has extended this feature to create analytics even on mouse over. Yes, can create a simple report and can integrate that report on your visuals. It means, when end user mouse hover on the visuals, it will show your customized tooltip report.
It is a preview feature now, so you need to enable this feature on Options->Preview Features
Follow the below steps
Screenshots.
I had a requirement to extract the data from SharePoint (only Metadata) and I was looking for a task to handle this in SSIS.
Then I found a simple 3rd party plugin to fulfil my requirement. In this post, I am explaining how to use the CData SSIS components.
You can download the component from https://www.cdata.com/drivers/sharepoint/ssis/.
The above link will take you directly to the download page. You need to buy this tool to use it on your production packages. If you want to test, then you can go with a trial version.
Download and install where you installed the SQL Server Data tools.
Once you installed then you can start creating an SSIS package using CData SharePoint Source and Destination tasks.
You need to create a connection manager to speak with on your SharePoint server either on-premises or online.
Right click on the connection manager section and choose “CDATA_SHAREPOINT”.
You need to enter your SharePoint site URL and credentials.
Once everything is done then drag and drop the “CData SharePoint Source” task. Point the created Connection manager. Under Table or View, you can choose any of the resources from SharePoint.
Check the columns and ensure everything is working. Then you can map the output to the destination tasks.
Microsoft released a new task called “OData Source” which helps us to connect to online SharePoint and extract the lists.
Follow the below steps to configure and extract the data.
Screenshots.
There is no direct way of extracting the SharePoint Excel data to SQL Server using SSIS. However, we can process multiple steps to achieve the result.
This post explains that how we can download the excel files from SharePoint and store it in a local drive and then process the stored excel from SSIS.
To download the files easily, we can use the tool called “SSIS Integration Toolkit for Microsoft SharePoint”.
Follow the below steps.
Screenshots.
Check the Linear regression introduction and how to use excel for linear regression.
In this post, let me explain how to use R programming for linear regression.
I am taking the same dataset in R to produce the linear regression.
Run the below codes.
Year<- c(2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010)
SalesAmount<-c(1450,1486,1560,1200,1205,1100,1150,1750,1650,1500,1900)
#linear regression
result<-lm(SalesAmount~Year)
#display coefficient
result
# new value for year 2011
year2011 <- data.frame(Year=2011)
year2011
#predict the sales amount.
predict (result,year2011)