DAX – RELATED & RELATEDTABLE
This post explains the concept of below DAX functions
- RELATED
- RELATEDTABLE
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.
- One-to-One Relationship
- One-to-Many Relationship
- Many-to-Many Relationship
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])
[Solved] Microsoft Access: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine
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.
- Visit https://www.microsoft.com/en-gb/download/details.aspx?id=13255
- Download 64-bit version of the software.
- Install on the machine where Power BI is installed.
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.
Don’t use boring default tooltips in Power BI
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
- Create a new page on your Power BI Report
- Go to Format option of the page and expand the Page Size option. There you can set the type as “Tooltip”
- Set the Page view as “Actual Size”
- You can see the small canvas area where you can create a simple report with different visuals.
- Go to Main report page where you want to integrate the customized tooltip. Make sure that you enabled the preview feature.
- Choose the visual and go to Format option and you could see an option called “Tooltip”. Extend the option.
- Under Page option, choose the created tooltip page. That’s it.
- Mouse hover the visual and you could see the customized tooltip.
Screenshots.
CData SharePoint SSIS Components
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.
Extract Data from SharePoint List using SSIS
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.
- Make sure to use the latest version of SQL Server Data Tools to work with SQL Server integration Projects.
- Use the Data Flow Task
- You can see “OData Source” task. Drag and drop into the data flow canvas.
- This OData Source task required a connection manager. Click New on the OData Connection Manager.
- You need to pass the below information to successfully connect with SharePoint Online.
- Service document location – https://<yoursharepointsitename>/_vti_bin/listdata.svc
- Authentication Type: Microsoft Online Services
- UserName: Your email address to connect SharePoint Online
- Password: your password.
- Test the connection.
- On the OData Source Editor, Choose the SharePoint list on the collection section.
- Check the columns and use any destination where you want to store the data.
Screenshots.
Extract Data from SharePoint excel using SSIS
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.
- Visit – https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-sharepoint/download
- Download 64 bit of Toolkit
- Install it on your system where you have installed the SQL Server Data Tools
- Create a new SQL Server Integration Projects
- Drag and Drop the data flow task into the control flow
- You could see “SharePoint Source” and “SharePoint Destination” tools under Common folder.
- Before Drag and drop the “SharePoint Source” task, you should create a SharePoint connection Manager.
- Configure SharePoint Connection Manager for SharePoint on-premises or online.
- Test the connection once.
- Drag and drop the “SharePoint Source”. Point the created SharePoint connection and Choose the source list as “Documents” or the library where you have stored the excel files.
- Configure the Download Destination.
- Once excel files are downloaded then you can use that excel files to process further using excel source tasks.
Screenshots.
Linear Regression with R – Part 2
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)
Linear Regression
Before starting with linear regression, let us try to understand the machine learning concepts.
As of today, machine learning is one of the trending technologies as the applications are started to consume real-time data and deliver machine learning services.
As part of machine learning, we have three different types of machine learning models available.
- Supervised Learning
- Unsupervised Learning
- Reinforcement learning
In Supervised Learning, the system tries to learn from the previous examples that are given.
Under supervised learning, we have classification and regression models.
One of the common and simple techniques for predicting a continuous variable is called linear regression.
It assumes a linear relationship between the outcome and the predictor variables.
The linear regression equation can be written as y = b0 + b*x + e, where:
- b0 is the intercept,
- b is the regression weight or coefficient associated with the predictor variable x.
- e is the residual error
This post covers the linear regression on excel and R studio.
Linear Regression on Excel
Follow the below steps to perform the linear regression in excel
Step 1: Consider you have year wise sales amount. You are in the situation to predict the sales amount for the year 2011.
Step 2: Create a scatter plot like below with Year and Sales Amount
Step 3: Right-click on the line and choose trendline.
Step 4: Display the equation on the trendline by selecting the checkbox on the Format trendline properties.
Step 5: Predict the value using the generated formula for existing values.
Step 6: Predict for the year 2011.
As you can see the values are not 100% correct, we need to adjust the con-efficient and reduce the r2 values to get the perfect result.