–We have a schema called org. We are granting the SELECT permission for this schema to the above Role. this means that you can perform Select data Statements
GRANT SELECT ON SCHEMA :: Org TO db_Org_read
So we added a user and granted the user SELECT permissions
When we use this user ID and password in Desktop it works fine. We can refresh the data.
However in Service (Dataset – Schedule Refresh), the login credentials are invalid
Authentication Method – Basic
Choose Basic if you plan to use SQL authentication instead of Windows authentication
Privacy level Setting for this Data Source – Organizational
An Organizational data source limits the visibility of a data source to a trusted group of people. An Organizational data source is isolated from all Public data sources, but is visible to other Organizational data sources.
The user account you create needs to be added to the Data Source Credentials in the Power BI Service once the dataset has been uploaded.
Go to Settings
And again. Settings
And Edit Data Source Credentials of the data Set
This will add the user to the Data Source Credentials
Once added you can go back to the Schedule Refresh in the Data set in your app workspace and the Log in Credentials should now work.
Microsoft teams is your central hub for teamwork so we can all collaborate in one place. its an offering of Office 365.
You can either, Make a team and connect it to an existing office 365 Group
However Creating Teams rather that Office 365 Groups is probably the best solution
Teams can be private or public. They will have an owner and Members and guests.
When you create a new Team channel. For example
And then move to your Power BI Service you will find this …
An App workspace that corresponds to the team. In fact, you have lots of App Workspaces. Only some are actually relevant to power BI. Teams does not create the Power BI workspace. Teams actually creates an O365 group and Power BI uses the O365 groups created
a. Want to work with it as a proper app workspace and add reports and Dashboards
b. Really don’t need it in Power BI and want to get rid
Lets look at these options in more detail
Use the Teams Workspace as an App Workspace
I have data on the training courses offered so I have decided I want to use this app workspace
the Report is published to the App Workspace
Here are the menu items for the Report created over the Teams app Workspace
And here are the menu items for an App workspace that was specifically created in Power BI
Notice that Settings and Access aren’t there. This means you cant collaborate with your team in the Workspace or look at the workspace settings. Why is this?
When New Workspaces are created in Power BI Via Teams, the App Workspace is created as Classic. Not the new Workspace. New Workspaces aren’t tied to the Office 365 Group.
In order to collaborate in classic you need to click on … and go to Members. This takes you to Office 365 and you have to add individual members from here.
We don’t want the Workspace to be tied to Office 365. and we want to change the App workspace from Classic to New
Are you an Admin / Owner of the team?
No. I am a member of this group. Because of this there are no other options to look at in here.
To Upgrade to the New App Workspace you need to be a Group Admin. If the person who created the Teams channel isn’t a regular user you may want to see if you can get yourself admin permissions via the Teams owner
And change from Member to Owner
Now as an owner you can Edit this workspace
You can now See Advanced. click on this to get to the upgrade option
Once the workspace is upgraded it is no longer tied to the Team or Office 365 and can be used like the standard Power BI New App Workspace.
Delete the App Workspace as its not required
The Team has been created and its now in your Power BI App Workspaces list. Its not required and its just adding extra information into Power BI that is not needed
You are the Owner of the Team and can see this when you go to Workspace Settings
DO NOT DELETE THE WORKSPACE
If at this point you delete the Workspace, its still connected to Office 365 and you will delete everything.
Use the information above to Upgrade the the New App Workspace before deleting.
The App Workspace must be disconnected first by not being a classic Workspace.
And if you want to try this out. test with a small team first. We have done numerous tests and after upgrade the Delete hasn’t removed anything but better to be safe than sorry.
Its time to start tracking Projects with Azure DevOps boards.
With Boards, teams can manage software Projects. They can track user stories, backlog items, tasks, features etc. You can choose the environment you want to work with like AGILE or SCRUM.
For this example, there is only one developer (me) and I’m am tracking my progress on a project where I have been the single developer
AGILE is the process that going to be used
Agile is an iterative approach to project management and software development that helps teams deliver value to their customers faster and with fewer headaches. Instead of betting everything on a “big bang” launch, an agile team delivers work in small, but consumable, increments.
Now we have a new project we can start working with Boards but first we need to understand what our AGILE work items are and how they interact with each other.
I have specific Epics I want to achieve
Reporting from the companies main system
Social Media reporting
Reporting for the Surveys
Reporting for all the telephone enquirers
Reporting for Complaints
Main Reporting Area for all the data Auditing
Reporting for Report usage
So, just looking at this. I want 7 Epics to work with (To start with)
A feature is some complete behavior to implement a new business process. So for example. for the Social Media Epic we want
Overall View of Business Performance as provided by the surveys
Monthly level reporting on customer satisfaction with drill through
User stories are within a feature. These are the smallest change that will result in behavior change. If you don’t observe a change then it cant be demonstrated
For example, as the Customer Satisfaction Manager I want to see the Survey results by month and have the ability to see how we are doing by over the year and at the same point in the year because we need to know if we are doing well as a company to see our trends in satisfaction
As the Company Head of Service I want a full review of our performance using our scoring system against customer satisfaction and how our competitors are doing for benchmarking
These are within a User Story and are the smallest independently deployable change.
Get file of Survey data (Pilot project)
Move Survey data into the Azure Data Warehouse (Staging area) Incremental loading using Data Factory
Establish dimensions and facts
Create Dim 1
Create Dim 2…….
Create Power BI Data Flows
Create Top level report by Month of Customer Satisfaction containing last 12 months
Create KPIs for Satisfaction against this time last year
Drill through to detailed report
Drill through to lowest level
a Bug is an error in the code
Incremental Refresh is causing Duplicates
NULL data Items in Survey Data set
An issue is more related to a process, when the System fails to meet user expectation
Created a report based on poorly Served Customers but this needs changing to the new business logic.
Test cases can validate individual parts of your code. We will look at this item in another blog post
Lets start with boards. These boards are Kanban Boards
A Kanban board is one of the tools that can be used to implement Kanban to manage work at a personal or organizational level.
When the board is first opened up, Epics don’t seem to be available
With Boards Selected Go to Configure Team Settings
Make sure that Epics are ticked in Under Backlogs
And now with Epics selected click on New Items are start adding in the required Epics
Next we need to start adding some features. It would seem that you cant add the features and then connect them to the epics. You have to create the Features from the epics
go back to the epic, click on … and Add Feature
And you can then see the feature within the Epic
Now we have a Feature we can add the user Story. Go to Features and click on the Add User Story
Same again. Move to User Stories and add Tasks
You can also go into the items and add lots more detail
This link to the Microsoft Documentation gives you lots of information regarding, effort, story points, business value, Priority etc.
Its always good to create the epic and work your way down into the Tasks
For this example, items are being added for a sprint that was closed some time ago because the project is being retrospectively moved into Azure Boards
I am starting them all from the beginning of this particular Project and for this I can add a Start Date
The Start Date also Applies to Stories but these will be set when the stories were originally created
However when you close a task and move the whole story into completed, you cant set a completed date
If you click on History and look at the state graph, you cant change the New and Resolved Times. These are set at the time of the action which makes it difficult to add past information into the Board
Backlogs help you to Quickly define work (User Stories, backlog items, requirements)
You can reorder the backlog so you work on the highest priority first
Add details and estimates
Assign items to team members and sprints by either bulk update or drag and drop
Map items within a hierarcy
Review the portfolio of work
Forecase work to estimate deliveries
Display rollup progress, counts and totals to show completion of work
Basically your backlog displays work items as lists and boards display them as cards
The Remaining Active User Stories have been dragged to Iteration 1
All the work items you create can be viewed in here as well as created
Hopefully this gives you a little head start into the world of Azure DevOps boards
Currently we have lots of visuals on the report pages and the only way to know that they drill through to other reports is to hover over them
In the March 2020 release there is new functionality in the form of buttons
Add a blank button
This button is now set to drill through preview and you have chosen the drill through page. On normal drill through, everything this that is available is shown which is difficult when the report structure is complex. This allows you to target and manage the drill through structure.
And this drills through to the detail reports for 31 days. Note that the tooltip in Desktop tells you how to drill through. Lets see this in Service.
Change the text on the button
Lets customise the button text using conditional formatting
the data item behind this drill through is ‘Resolved in’ and then it contains number of days categories.
Create a new measure
A new measure has been added to the dimension table containing the Resolved in Item
String for Resolved in Button logic = IF((SELECTEDVALUE('dim table'[Resolved in ],0)==0),"See Resolved in Details", "See Details for " & SELECTEDVALUE('dim table'[Resolved in ]))
If the selected value is null, set it to 0. So if its zero, show “See resolved in Details”. If its not zero then it can be displayed
Add New Measure to the Button
Resolving a noisy drill through with Drill through Buttons
This visual in this Drill though report has three Drill through’s for Incident. Then the stage of the incident. Finally the Business Property Drill through. All are valid but we would prefer to Not have the stage here because this should only be accessible via Incident.
Add two buttons. Link one to the Incident Drill through report. The other to the Business Property Drill through report
We want the button to be dark red when its active.
Create a new measure for the Incident in the incident Dimension
String for Incident in Button logic = IF((SELECTEDVALUE('dim BPIncident'[Incident Ref],0)==0),"See Incident Details", "See Incident Details for " & SELECTEDVALUE('dim BPIncident'[Incident Ref]))
And add this as conditional Formatting to the button Text
Create a new measure in the Business Property Dimension
String for Business Property in Button logic = IF((SELECTEDVALUE('dim BusProperties'[BP Number],0)==0),"See Business Property Details", "See Business Property Details for " & SELECTEDVALUE('dim BusProperties'[BP Number]))
And add this as conditional Formatting to the button Text
And now you should get drill throughs that the user can immediately see and use. Avoiding the stage drill through because it doesnt match what we want the journey to be
The button takes up more space.
Currently you have the drill through button and the drill through on the visual. This could be confusing to the user. It would be great if you could turn the drill through on the visual to off
LY YTD Internet Sales = CALCULATE([YTD Internet Sales],SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey].[Date]))
These would look great in a KPI or two gauges. However, the Advanced Gauge allows you to view them together
Download the visual from the market place.
First of all I’m going to add a Filter for year to the report
The Actual and Target Values for your primary measure is the outer ring and your secondary measures for quantity make up the central ring
You could also add Minimum and Maximum measures if you had them.
This is a really nice visual but at a glance, without the table info it can be quite hard to read and I find the tooltips a little confusing
Once you get a handle on it its clear that we sold more in 2012 than 2011 but we made less money
Straight away, that leaves you wanting to set up analytics to find out why that happened.
Some of the reviews of this visual say it doesn’t work with percentages though which is disappointing
XViz Hierarchy Tree
This is another custom visual to show off your hierarchies
This is very similar to the decomposition tree which is maybe preferable in terms of look and usability. You cant choose the next step down in the tree like you can with the decomposition tree for example
You need ALOT of space for this one
There is already a nice hierarchy for Location data set up. Add Product category to this and analyse by Sales Amount
We are interested in the 20 items against North America so I can click on the end of the node to find out more.
You can see why you need space for this one. There is a bar to allow you to move in and out but its quite hard to control where your visual will end up on the page. I couldn’t get it looking any better than the above visual.
Still, Users will love this because it gives them the ability to interact with the data. Would I choose this over the decomposition tree? Probably not.
It doesn’t look to good when you bring the visual down to size. You would need a full page for this one
X Viz Hierarchical Filter
This visual is a similar one to an old hierarchical filter
This is the new version
And this is the old version
The findings are that if you have multiple hierarchy items in the new version, this visual takes up much more room than the original Hierarchy filter. The preferred choice may be the original one for taking up less space on a page.
And don’t forget, Hierarchical filtering is now available on the Slicer visual that comes with power BI
XViz Bullet Chart
This bullet chart was developed as a replacement for gauges and meters. It saves space and can be either horizontal or vertical.
We have three measures I want to look at by Product Sub Category and I want to be able to slice by Product Category so I can see, for example all the Bikes
2 Years ago YTD Revenue = CALCULATE([YTD Sales],PARALLELPERIOD(DimDate[FullDateAlternateKey].[Date],-24,MONTH))
We wanted to add Year to date for the same period two years ago and for this the PARALLELPRERIOD function can be used. In this case, its looking at 24 months previous to the Date.
Bands have been set within the format, rather than using data
Straight away we can see Jerseys are producing high sales and last year there seemed to be a dip in sales. We are doing much better in the current year
X VIZ Parallel Coordinates
this visual allows you to compare series on a set of numeric variables
Using the previous visuals measures as an example
This immediately shows us that there are 6 product categories leading the way and we are doing much better in the current year than in the last two years.
This visual tells us that we are selling more road frames than road bikes, however we make a lot more money with Road bikes
XVIZ GANTT Chart
In one of our Reporting projects we have a GANTT Chart by MAQ Software
This gives a really nice timeline overview of incidents per location and what the impact is, along with the stage of the incident. This has already been well received, so the question is, how will XVIZ GANTT chart compare?
The MAQ Software visualisation was created by the following fields
Essentially, the MAQ GANTT chart has been re-purposed here for something other than tracking of a project. A brief look at the XVIZ GANTT chart and it would seem like you use it specifically for project tracking.
Therefore you need data items like;
AssignedTo, CompletedDateTime, CreatedDateTime, DueDateTime, PercentCompleted, StartDateTime, TaskName, TaskStatus,TaskOwner etc.
This looks good and if you want to track projects within Power BI over the top of a data source this may be the way to go. However we use Azure DevOps Boards or Jira. the MAQ Software GANTT Chart gives you more options to use the visual for other reasons which is a really good reason to add it to your reports.
Zebra BI Charts
These are really great looking reports. As soon as you try and add any more detail like Category into Group, which would split you report into reports by Group
Annoyingly, you get pointed towards a free trial but I cant find anywhere that openly states what the costs are. Its not even mentioned in the frequently asked questions.
Personally, if there is a cost attached I like the company to be more open about it. Therefore I’m not comfortable with trying the free try. this is a shame and I think Zebra should state the costs before the trial.
Market Place visuals are created by External companies. Always check with your organisational policies that you are happy to use these visuals
This is really the most exciting update for probably all of us working with Power BI. Currently we already have a backlog or reports in Pro that have required Incremental loading for some time so its great to be able to finally get the chance to try it
Our Project, to view Tweets with sentiments etc in a Power BI Report involves the following Services:
A Logic app that is triggered when a new tweet is posted regarding a specific company
Content Moderator – Logic Apps uses the content moderator to check for profanity
Cognitive Services – Logic apps uses the Cognitive Service to add a sentiment score and to find key phrases
There is also a second Logic App that uses the same logic for Tweets posted by the company.
Azure Storage Account – Tabular Data Storage
The Logic Apps loads the tweet information into a Tweets Table
The Keyphrases into a Keyphrases table that connects to the tweets table
The Media items into a Media table that connects to the tweets table
Data Factory is used to load the information from the Tabular Data Storage into a SQL Server Data base staging area incrementally
The logic is fairly straight forward in that data items are inserted. Nothing is updated or deleted
There is a Pipeline for each single table
The SQL For the Lookup for the data set Mentions
SELECT MAX(WatermarkValue) AS WatermarkValue From [staging].[watermarktable] WHERE WatermarkValue IS NOT NULL AND TableName = 'staging.mentions'
the Watermark is a table in SQL that is updated with the Max Date at the end of the process
Once installed, connect to the SQL Database that is your data source
So long as you have the profiler extention installed you can Launch Profiler
If you don’t have it, you can download the extension
Once Launched Start a Profiler Session
Now we need sometime to Profile. go into Power BI service, Datasets.
click on refresh now and then go to data Studio to see whats happening
From Logon to Logout during the run, it took 20 minutes because the entire model is refreshed. obviously it would be really good if we could get the time down using incremental refresh
Before you set up Incremental processing, ensure that the services preceding the Power BI Load have been well tested and signed off.
Incremental Processing in Power BI Pro
In Power BI desktop. Incremental refresh is now out of preview so no need to go to Options and Settings to turn it on anymore.
Define your Incremental refresh policy
If the system are acting as they should and there are no bugs or issues
New rows are added into the data set
No historical data is updated or deleted
Incremental loading can be added to every table apart from media tables because there are that many records. they can be left as full upload
Set up incremental refresh in Desktop. Create Parameters
It isn’t practical to hold all your data when you are working in desktop if you are working with a Large model.
Go to Power Query Editor
Select Manage Parameters
The two parameters that need setting up for incremental loading are RangeStart, RangeEnd
These are pre defined parameter names only available in Premium for Incremental processing
Range Start and Range End are set in the background when you run power BI. They partition the data
You need to be aware of Query folding here. This is when, you write lots of steps in M Query to transform the data and where possible they are applied at source. So RangeStart and RangeEnd will be pushed to the source system. Its not recommended to run incremental processing on data sources that cant query fold (flat files, web feeds) You do get a warning message if you cant fold the query
The suggested values are simply ones you add that get amended later during processing.
this start date was chosen because at present, the data only started to get collected in 2019 so there is only around a year of data
Filter the data in the model
Still in Power Query Editor, all the tables that require incremental load need to have the rangeStart and RangeEnd paramters adding to the filtered date column
Incremental refresh isn’t designed to support cases where the filtered date column is updated in the source system.
With this in mind, imagine you have a sales table with an Orderdate and an UpdateDate. the OrderDate is static. The UpdateDate will be updated if there are any changes to the record.
Order date would need to be chosen as its static, so lets go through the tweet tables and set the filters. Click on the Column header icon to get to the filters
In power BI you don’t need so much data to do the testing, so this is great to keep the data smaller in desktop. At the moment, its using the default settings we provided.
dim. mentionsTweet CreatedAtDateTime
fact.Mentions Date (For this, date was created from the date time Key in Power BI Query Editor)
Close and Apply
Define your Incremental Refresh Policy in Power BI Pro
go to your first table and choose incremental refresh
Storing everything for 5 years. its set to months so the partitions are smaller
If this is running every single day then you would only need to refresh rows in the last 1 day. However as a just in case 1 month has been used, in case for any reason the job is suspended or doesnt run.
Detect Data Changes has been used. The months data will only be refreshed if the ImportDate for this record is changed (Or there are new records)
No records are deleted so we don’t need to worry about this
Publish the new Power BI Report and Data Flow
You might be thinking at this point, but I dont want the filters that I have set for Desktop to be applied in Service. I want to see all my data in Service
Dont worry, in Service RangeStart and RangeEnd don’t keep the dates specified for the filters in Desktop.
they are set via your incremental refresh policy. So they are set as the partitions for our 60 months (Instead of setting it to 5 years, meaning there is one RangeStart and OneRangeEnd, you get RangeStart for Month one, RangeEnd for Month 1, RangeStart for Month2, RangeEnd for Month2 etc, breaking your 5 years down into much smaller partitions to work with,
You need to set up the Incremental Refresh policy for every table that has been filtered with RangeStart and RangeEnd
Test the Process
I have a visual for Number of Tweets
Today so far there are 11 Tweets
I also have an audit report
The Logic App has been processing tweets realtime into Table Storage
Run Data Factory (2 new records)
Reprocess Power BI Pro data Error Resource name and Location need to match
If there hadn’t been an error we would move to Azure Data Studio Check. Note that it now takes a second to run
Check the Visual
Error Resource name and Location need to match
The data set now has a warning sign. after speaking to Microsoft this is a Known issue and should be fixed in April. it is believed to be something to do with detect Data Changes So basically…… to be continued
Recently we did a training course with two developers in our own tenant under two training accounts. the training was done on their own computers but they logged into our tenant with our training details.
Then they moved back to their own tenant in Power BI Pro with their own details to start creating a data flow.
They have an App workspace and are both Admins in the workspace
Developer 1 Created Workflows within this App Workspace. Developer 1 can open these data flows but Developer 2 doesn’t have access because they are not the owner which seems like the correct logic. they should only be able to use them in Desktop.
However, when both Developer 1 and developer 2 both go into Power BI desktop and attempt to connect to the data flow (Both Desktop and Service is connected to the company account) they see a dataflow that was created our tenant when we were doing the training course. the new data flows cant be seen by either Developer.
They have both tried to refresh the view (Above) but this didn’t work. This does seem to be something that happens with the users log out and use a different account in a different tenant
How to resolve the Issue
Use Files – Options and settings – Data source Settings
then find Power BI Dataflows in Global Permission, then Clear Permissions of it.
When you Connect to the Power BI Dataflows again you have to sign in. Sign in as the current user, then you can see the Dataflows of your current account.
So if ever you are working in more that one tenant, remember this fix if you can only see the dataflows from the previous account
In a nutshell, you will cause yourself a lot of headaches in the future if you do this because OLTP systems are specifically for users to enter data and not for analytics. You dont want to cause issues for this people trying to do the actual work by entering information onto your systems.
So what do you do when there is some parts of your data base that you need to have as real time reporting?
The first thing to understand is that, just because you need some real time reporting, it does not mean that you have to treat everything as real time reporting. You need to separate out your needs. It may be that only one report needs to be real time and the rest can be dealt with as overnight imports etc.
You also need to make sure that you do need real time reporting. In many cases, after some investigation, this isn’t actually required.
There are a few key things to look at here
Power BI Push data sets
With a push dataset, data is pushed into the Power BI service. The Power BI service automatically creates a new database in the service to store the data. Reports can be created over this data set rather than over your OLTP with Direct Import and your visuals will be updated in real time.
You cannot do as much with this data set as we will see later but it separates the two functions to a greater extent as your analytics will be run over the push data set
You can only keep 200K rows. Any more any old rows will be removed which is something to keep in mind
We will have a look at this in more detail later
Column Store Indexing in SQL
“A column store index is an index that was designed mainly for improving the query performance for workloads with very large amounts of data (eg. data warehouse fact tables). This type of index stores the index data in a column based format rather than row based as is done with traditional indexes.”
These can be added to OLTP systems to help you run real time from your transactional systems but there are a lot of issues with this and would need lots of admin time, so may not be the way forward. This will be looked at in another post.
“A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.”
This could be used for creating a seperate table to work with, allowing you to run analysis over this rather than your tables within your OLTP Schema.
You could have this table in a separate database which would be ensure that no useability issues occur for your transactional system users.
I will have a look at triggers in another blog post
Example 1 Power BI Push data sets
Two very simple tables have been created in the Azure SQL Database. A customer table and a sales table (So we can see how more than one table is affected)
An Updatedate has been added by adding GetDate() into the binding for the column for both tables
Once the seed data is in (The starting test data), make sure the ID of the dim auto populates and you can set the key as a primary ID
The last table I want is an audit table to record what the max update date is for each table
Push Data Restrictions
It should be noted that you cant work with more than one data set if you use push
Notice here, where an example Push Dataset is imported into Desktop, thats the only data set you can add.
This is the same if you create the Report in the Service
If you go into Power BI Service and to the data set (This is just an example, we haven’t created our data set yet)
And Click to Create Report
A table is added but you can’t do much to the data. You cant create measures or remove rows you don’t want in Power BI within Service
Two need to become one. For this, a view is added in SQL
CREATE VIEW dbo.VW_RealTimeDataStreamExample AS SELECT d.CustKey, f.DateKey, f.ProductKey, f.DateOfSale, f.Quantity, f.Cost, d.UpdateDate AS CustomerUpdateDate, F.UpdateDate as FactUpdateDate, d.Name FROM [fact].[RealTimedatatestingDE]f INNER JOIN[dim].[RealTimedatatestingcustomerDE] d ON f.CustKey = d.CustKey
Power BI Service
Go to power BI service and choose + Create and Streaming Dataset
create one push dataset for the view
Setting Historical Data Analysis to On changes it into a Push data set. the only time you would ever want to leave this as off is if you are looking at IoT data in real time
Get the Powershell script
You now have your streaming data sets in your data sets list within the App Workspace. Click on the i to see the API info for the streaming data set
This gives you the sample scripts for pushing to your data set. The Push URL is all you need to populate your dataset.
Copy the Powershell Script
We have one streaming table to work with over a view of two tables
Test the Streaming DataSet
We can either create a report in Service
By clicking on Create Report, or in desktop by connecting to the hybrid dataset RealTimeDataTestingDE
For this test I am going to create the report in Service
You cant create measures in service. I am simply going to add a table in order to view the data
Search for Powershell and then Run as Administrator
Quick tip, cls will clear the screen if you make any mistakes
then you can copy the script into Powershell. For this example, the dim table will be created first
There are a few things changed from the code taken from the push data set. Firstly, a While loop is added simply to incrementally send rows from the data set to Power BI, but you don’t need to use the while loop.
Then then default values are replaced with values from the data set
Straight away you can spot an issue. This is fine for new records but what if the data is also being updated?
This would involve actually deleting a record in real time streaming based on the key and at present I don’t know if that’s possible.
In this meantime lets stick to inserts only.
Test the Powershell Script
Time to add some data to see if its working via SQL
INSERT INTO [dim].[RealTimedatatestingcustomerDE] (Name) SELECT 'Example 3'
INSERT INTO [fact].[RealTimedatatestingDE]
(CustKey, DateKey, ProductKey, DateOfSale, Quantity, Cost)
SELECT CustKey, 20200220, 255, '2020-02-20 00:00:00.000', 100, 90 FROM [dim].[RealTimedatatestingcustomerDE] WHERE Name = 'Example 3'
Go back to the report in Report Server
The data can now be viewed in the report
The push data set can only show data added from the time its turned on so we dont get to see any of the data already in the tables before the creation of the push data set
Using the information above its not going to be able to deal with updates. Inserts only. This can be looked at in a later post, but this would actually involve updating data in the push data set or deleting based on ID and then recreating.
Orchestrating the Powershell Script
Add another clean record with the above insert SQL Script
Going back to Service you can click Refresh but note that there is no new data in the push dataset. It only worked for the one record. We need to automate the push data to Power BI Automatically.
Azure Automation with Powershell
Currently the services being used are Azure SQL Database in POC_Resource_Group so it would be good to add an Automation account in here
Note that Create Azure Run As Account is Yes. This means its a Service Principal and has a contributer role in this subscription
Set up Credentials
Runbook credentials are very similar to Azure SQL Server credentials. in your new Automation Account click on Credentials
The new account is set as azure_pocmaintenance with the same user name and and a password that has been saved for later.
Next make sure that all the Powershell modules are up to date
Next go to Runbooks and Create a Runbook
Now we are ready to add the powershell script that we have created
You can Test Pane to check everything is working
It looks like there there is an issue. The the Server isn’t accessible to the run book so we need to give it access.
We need to amend the script slightly in order for it to work in the Runbook with one extra row (Row 1)
$connectionString ="Server=$dataSource;Database=$database;trusted_connection=false; user id =$userName;Password=$password;Integrated Security=False"
relates to the data source variable
This time the Test Completes.
(At this point you will need to be able to add some records into SSMS (SQL Server Management Studio) in order to test and use the INSERT INTO SQL above
Publish the RunBook
Has simply publishing the run book worked. Lets add a record and check the report
No the new records haven’t come through.
Schedule the Run Book
Back in the run book
We want to link the run book to a Schedule Link to Schedule
The schedules are only for hour day week or month. Is this a one time run book because it just continually pushes data into Power BI. I have started it for 11 AM. In 10 minutes time so this can be tested then.
Ran the views again from above
Its missing attempts before the schedule was created. Let try adding another one
Having a look at the Runbook in azure
It completed, but it seems to have just completed the one record and nothing after the last update. This means its not a one time run book. It has to be scheduled for a recurrance.
You can set the Runbook to run Once an hour Via Recurring but we need it to run more than this
Run via a Logic App
Can we sort out this issue using a Logic App?
Start with a Recurrance Trigger
For this example, set to 3 minutes. Choose Start Time and Time Zone as extra parameters for the recurrence trigger
Next, search for and choose Azure Automation – Create Job Azure Automation
For this example, sign in to the Tenant
Once Signed in, Add the Subscription, Resource Group, Automation Account and Runbook name of the Runbook above
You can Save and Test the Logic App to ensure it runs successfully
Fully Test the Logic App
Now the Logic App is active and is set to run every three minutes from the start date, Start inserting Records using the insert scripts above. You can check that the Logic App is running jobs
So 4 jobs have run. Lets have a look at the Report in Power BI Service
The records are being transferred to Power BI, although it seems that you have to refresh the screen for them to appear.
So, there are issues with this, you can only add new records, and you miss data in the table previous to the Push data set. Also, the Logic App probably needs setting to Service Principal. But for this example we have:
Set up a Power BI Real Time Data set (Set to Push)
Created a Powershell script moving data from the SQL View to the Push Data set.
An Azure Automation Account and a Runbook containing the Powershell script
An Azure Logic App to run the Automation Runbook every 3 minutes