for – ARKSD Consultancy Private Limited

Data, Development Tips, How To, Information, PowerBI.com, Tip & Tricks

App for Power BI REST APIs for Streaming Data

August 11, 2016 Ashwini Tripathi

In this post we will see how to create app to use the Power BI REST APIs for Streaming Data.

Full documentation: https://powerbi.microsoft.com/documentation/powerbi-service-real-time-streaming/

To run this app follow the steps discussed in my previous post: [Real-Time Dashboard Tile & Streaming Dataset– in Power BI]

Summary as below:

Go to app.powerbi.com
Go to streaming data management page by via new dashboard > Add tile > Custom Streaming Data > manage data
Click “Add streaming dataset”
Select API, then Next, and give your streaming dataset a name
Add a field with name “Customer ID”, type Number
Add a field with name “Customer Name”, type Text
Add a field with name “Sales Value”, type Number
Click “Create”
Copy the “push URL” and paste it as the value of “realTimePushURL” in below app

We will start with new project in Visual Studio.

RealTimeSync-12

Create a new Visual C# Console Application.

Open the Program.cs File and write a code as shown below.

This app Uses the WebRequest sample code as documented here: https://msdn.microsoft.com/en-us/library/debx8sh9(v=vs.110).aspx

RealTimeSync-13

For your easy here is the code of Program.cs below:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Net;

using System.IO;

namespace RealTimeStreaming

{

class Program

{

// Paste your own push URL below as obtained from while creating Streaming Dataset and saved in step 9 above

private static string realTimePushURL = “https://api.powerbi.com/beta/4e7ca966-123e-4ce7-9833-3e858854b98f/datasets/7d154cd7-11e0-4d5e-9569-12fa3f82f224/rows?key=mD1nkJOf426PjPPaEQsW9xEg%2FN1EENQ2hRZvXIpHr%2BTXNk3XQpKsR2Jbe5CATiMoLmxjlzSp%2FIMlbe9HL8G4xQ%3D%3D”;

static void Main(string[] args)

{

while (true) { //Set Infinite Loop

try

{

// Declare values that we will be sending

Random r = new Random();

int currentValue = r.Next(0, 100);

String Name = “Dummy Name”;

// Send POST request to the push URL

WebRequest request = WebRequest.Create(realTimePushURL);

request.Method = “POST”;

//Here you will retrieve the data from the source and format as per the request.

//In this example we are sending Random Value generated by above code for testing purpose.

string postData = String.Format(“[{{ \”Customer ID\”: {0}, \”Sales Value\”:{1} }}]”, currentValue,currentValue);

Console.WriteLine(String.Format(“Making POST request with data: {0}”, postData));

// Prepare request for sending

byte[] byteArray = Encoding.UTF8.GetBytes(postData);

request.ContentLength = byteArray.Length;

// Get the request stream.

Stream dataStream = request.GetRequestStream();

// Write the data to the request stream.

dataStream.Write(byteArray, 0, byteArray.Length);

// Close the Stream object.

dataStream.Close();

// Get the response.

WebResponse response = request.GetResponse();

// Display the status.

Console.WriteLine(String.Format(“Service response: {0}”, ((HttpWebResponse)response).StatusCode));

// Get the stream containing content returned by the server.

dataStream = response.GetResponseStream();

// Open the stream using a StreamReader for easy access.

StreamReader reader = new StreamReader(dataStream);

// Read the content.

string responseFromServer = reader.ReadToEnd();

// Display the content.

Console.WriteLine(responseFromServer);

// Clean up the streams.

reader.Close();

dataStream.Close();

response.Close();

}

catch (Exception ex)

{

Console.WriteLine(ex);

}

// Wait 5 second before sending

System.Threading.Thread.Sleep(5000);

} //Infinite Loop ends here.

}

}

}

Compile and Run the Program.

RealTimeSync-14

Leave the Program Running and switch to Power BI dashboard. You will see your newly created Tile in previous post will be displaying the Random Value generated by this program updating every 5 seconds.

That’s all with little tweaking to this program you can fetch your data and send the updated data to your Real Time Streaming Dataset.

That’s end to this post.

I will come up with more details in my upcoming posts.

Till then keep Exploring and learning.

Development Tips, How To, Information, Profiles, Tip & Tricks

Setting different Column Layout for different users

July 8, 2016July 8, 2016 Ashwini Tripathi

On Community Forum i came across one requirement for opening Page with different column on Page for different users.

Here i have given an demo how can be done, but will require some tweaking to achieve the actual result.

I have copied the Customer List Page and removed extra fields.

I have added one Field above Repeater so that we can decide which format we want to see on screen. User can enter 1/2/or any other value.

I have created two more variables Set1 and Set2 to show/hide fields as per the value entered in SetSelect.

Assigned the Set1 & Set2 to few columns on the Page Fields to Property Visible.

Set1 to Responsibility Center, Location Code & Currency Code.

Set2 to Post Code, Country/Region Code, Phone No. & Fax No.

Added code on OnOpenPage Trigger to select Default page Format when Page is Opened. This code you will modify as per your requirement like check the User and assign the default Value.

Added Code to SetSelect-OnValidate Trigger to select the required format of Page. You need to modify the code as per your requirement.

In this demo if user enter value for SetSelect and you will get different view of same page.

Do some research and design one as per your requirement, this way we need not to create different Pages for different Users. Same Page can serve you different purpose if limited to only different views.

I will come with more information in my upcomming posts.

Till then keep browsing and learning.

Backup, Corfu Navision 2016, How To, Information, Job, Schedule, SQL, Tip & Tricks

Schedule SQL Job for SQL Database Backup

July 7, 2016July 7, 2016 Ashwini Tripathi

Today we will see how we can schedule to take daily backup of our database.

Before i start explaining the step i am confirming the version of SQL based on which i am writing this post.

Process will remain same may be screen bit different in different versions of SQL.

SCheduleJob-1

At first we will ensure that SQL Server Agent is running, in case it is not right click and Start it.

SCheduleJob-1

Expand the SQL Server Agent Folder, Select Jobs, Right click and select New Job.

SCheduleJob-1

Provide Name & Description to your New Job.

SCheduleJob-1

Select Page Steps & New from Bottom of the page to define Step for your Job.

SCheduleJob-1

Give name to the Step.

Select Type as Transact Script (T-SQL).

Select Database Name in my case i am selecting Demo Database NAV (9-0)

Write the Script as shown below:

SCheduleJob-1

Here is the Script for your ready reference:

{– Script Start

SET QUOTED_IDENTIFIER off

select getdate() “Start Time”

set nocount on
declare @dbname varchar(36),@cmd varchar(255)

declare dbname_cursor cursor
for select name from master..sysdatabases where name = ‘Demo Database NAV (9-0)’
order by name

open dbname_cursor
fetch dbname_cursor into @dbname

while @@fetch_status = 0
begin
DECLARE @DATE VARCHAR(36)
SELECT @DATE = (select CONVERT(char(8),DATEADD(dd,-30,GETDATE()),112))
if DATABASEPROPERTYEX(@dbname,’Status’) = ‘ONLINE’
begin
select @cmd =’backup database [‘+@dbname+’] to DISK=”C:\User Data\SQL Backup\’+@dbname+’.bak” with init’;
print @cmd
execute (@cmd)
end
fetch dbname_cursor into @dbname
end

close dbname_cursor

deallocate dbname_cursor

select GETDATE() “End Time”

–Script End}

Although above script is for multipurpose like if you want to backup all database on your SQL Server, you can make small tweaking and you are done.

If you would like to backup all the databases on a particular SQL Server, then make the following changes to the code above.

Replace following line of code:

for select name from master..sysdatabases where name = ‘Demo Database NAV (9-0)’

with the following line of code:

for select name from master..sysdatabases where name != ‘tempdb’

This will create a backup file for each database on the server, except for the temp database.

Next : Switch to Advanced Page.

Define other parameters as shown in below screen as per your requirement.

SCheduleJob-1

On Selecting OK, you will return to Job window.

SCheduleJob-1

Select Schedule Page:

Define your Schedule as per your requirement.

SCheduleJob-1

Select OK to return to Job Page.

SCheduleJob-1

Select OK to close and save the JOB.

Now our Job is created and scheduled.

You can see defined Jobs and its Status using Job Activity Monitor.

Before Job is executed

SCheduleJob-1

After executed you can see the Status as.

SCheduleJob-1

Post execution of Job here is the backup file created at defined path in the Script. Above script will overwrite the file on next execution.

If you want to create new file on every execution you need to modify the script for file name by adding timestamp or any other logic you desire.

SCheduleJob-1

I will come up with more information in my upcomming posts.

thats all for this post.

Development Tips, Functional Tips, How To, Information, Jet Reports, Tip & Tricks

Finding the Right Business Intelligence Solution for your Company

June 30, 2016 Ashwini Tripathi

Get answers to the most commonly asked questions around what to look for in a business intelligence solution and provider, including implementation expectations, important features to look for (that you probably didn’t know you need), and different options for report and dashboard distribution.

Watch Video here :

Corfu Navision 2016, Functional Tips, How To, Information, Tip & Tricks

Create Production Orders for/from Sales Order

June 19, 2016 Ashwini Tripathi

One of my reader have requested to demonstrate how he can create Sales Order which is directly associated with Sales Order.

Let us start with creating a Sales Order:

Here you can see we don’t have Available Inventory to fulfil the demand of this Order.

To fulfil the demand we will create Production Order for this Order.

Select Planning from Action TAB on Ribbon.

Sales Order Planning Window Opens, Select Create Production Order.

Select the Status for Production Order as desired.

For Order Type you have 2 Options

Item Order – Production Order is created for each Item on Lines
Project Order – Single Production order for Entire Order

Select the desired option and respond to Yes.

I have Selected Released & Project Order in my case.

You can check that Quantity to be produced is reserved for this Order.

There could be the situation where you may have few Inventory available, in that case you need to reserve the quantity from Function before creating Production Order else all quantity will be created. In other case only short quantity will be a quantity of Production Order.

Select the line from available lines in Item Ledger Entries (e.g. on hand inventory) and by using the ‘Reserve from Current Line’ function you can reserve those against the sales order line. The ‘Reserved Quantity’ then gets updated and you can see the rest in the ‘Unreserved Quantity’ field.

Although in this case entire line is already reserved from the Production Order.

But in case you want to reserve from any available Inventory in hand from Ledger entry.

This was one of the way how you can perform Creating Production Order and Reserving for Sales Order.

We can do the same by another way as below, directly creating Production Order for this Sales Order.

Create a Production Order Select Source Type as Sales Header and Source No as Sales Order No.

Then Refresh the Production Order.

This way we can create and Reserve the Item for required demand.

I will come with more details in my upcoming posts.

PowerBI.com, Tip & Tricks

Microsoft Power BI – Part IX

March 30, 2016 Ashwini Tripathi

Continuing from my previous post. Today we will see usage of Power BI Publisher for Excel.

In case you have missed my previous posts here I present the link to all previous posts below.

Microsoft Power BI – Part – I

Introduction to Power BI and Creating Report from Excel Data, Local Files.

Microsoft Power BI – Part – II

Introduction to few Features of Power BI

Microsoft Power BI – Part – III

Power BI Desktop, Creating Dataset & Reports from In Premise Database installation

Microsoft Power BI – Part – IV

Power BI Gateway usage

Microsoft Power BI – Part – V

Scheduling Refresh of Dataset & Report created using In Premise Database

Microsoft Power BI – Part – VI

Power BI Content Pack

Microsoft Power BI – Part – VII

Power BI Mobile App

Microsoft Power BI – Part – VIII

Power BI Content Pack

Sharing key Excel insights just got easier. Save snapshots of important PivotTables, Charts, cell ranges, and more from across all of your spreadsheets to a single location quickly with the Power BI publisher for Excel. Simply highlight the elements you want to save, then click the “Pin to” button: now you’ve got a powerful web dashboard that you can share with everyone in your organization.

Let us see how we can use this feature.

PowerBI-95

From Download Menu Section choose Power BI Publisher for Excel.

Or visit Link: https://powerbi.microsoft.com/en-us/excel-dashboard-publisher/

PowerBI-96

Select your desired Excel Version 32 or 64 bit as installed on your PC and download the Package.

Close your Excel and Install the Package and follow on screen guide.

After Installation when you open your Excel First time after Installing the Package. You will get similar to below screen.

PowerBI-97

PowerBI-98

PowerBI-99

PowerBI-100

I have created a Pivot in excel as below which I want to share on my Desktop.

PowerBI-101

Select the Region/Range that you wish to publish from Power BI choose Pin, as shown below.

PowerBI-102

In my case I have already pined so confirmation Dialog Box is taken else it will Pin the Selected Range as a snapshot on selected Workspace.

PowerBI-103

Make sure you select right Workspace before you Pin.

PowerBI-104

After Publishing you will get confirmation as below.

PowerBI-105

Using the Link, Login with your credentials to the Power BI and switch to the Dashboard on which you published.

PowerBI-106

This way you can Pin Snapshot of PivotTables, Charts, cell ranges, and more. You can update existing with new one and manage all your Pins.

PowerBI-107

Save your Excel and Exit.

PowerBI-108

Try useful feature.

I will come up with more details in my future posts.

Till then keep practicing and learning using online Blogs, help and documents.

Jet Reports, Tip & Tricks

Leveraging Business Intelligence for Ad Hoc Analysis

March 30, 2016 Ashwini Tripathi

Leveraging Business Intelligence for Ad Hoc Analysis

Video-1

Video-2

Corfu Navision 2016, Development Tips, Extension Package, How To, Information, Tip & Tricks

Useful Windows PowerShell cmdlets for managing Extensions in Dynamics Navision 2016

January 7, 2016 Ashwini Tripathi

Today I will list few cmdlets which will help you getting your task done while working with Extensions.

For Overview of Extensions, please see my earlier posts

Introducing Extensions in Microsoft Dynamics NAV 2016

Which Object types you can Include & Restrictions applicable to C/AL code in Extension Packages

Which Properties are Restricted in Extension Packages

Just a quick reference you can study in details using links of MSDN below every cmdlet.

Get-Help

To get Help about syntax and options for a specific cmdlet, type the following cmdlet.

Syntax: Get-Help <cmd name>

Export-NAVApplicationObject

The Microsoft.Dynamics.Nav.Model.Tools.psd1 module includes a function, Export-NAVApplicationObject, which runs the ExportObjects command. This means that you can run a command such as the following:

Export-NAVApplicationObject

–DatabaseServer MyServer

–DatabaseName “Demo Database NAV (7-1)”

–Path C:\UserData\MyPackage\ORIGINAL\MyObjects.txt

Export-NAVApplicationObjectLanguage

Exports captions from the specified text files with Microsoft Dynamics NAV application objects. The captions are exported to text files.

Syntax:

Export-NAVApplicationObjectLanguage

[-Source] <String[]>

[-Destination] <String>

[[-LanguageId] <String[]> ]

[-DevelopmentLanguageId <String> ]

[-Encoding <FileEncoding> ] [-Force] [-PassThru] [-Confirm] [-WhatIf] [ <CommonParameters>]

The resulting text files are similar to the multilanguage files that you can export in the Microsoft Dynamics NAV Development Environment. If your source files contain more than one language, you can choose to export one language, multiple languages, or all languages

Example:

PS C:\> Export-NAVApplicationObjectLanguage

–Source .\ORIGINAL\ -LanguageId “DEU”,”FRA”

-Destination .\RESULT\languages.txt

Id	Name	Version	Publisher
9a47a833-e22f-4812-ade314219c53	EDD1.01	1.0.0.00	Ashwini
3c88160c-e0eb-4fe1-b4f6-011e45d74b10	EDD1.02	1.0.0.01	Ashwini

Id	3c88160c-e0eb-4fe1-b4f6-011e45d74b10
Name	EDD1.02
Version	1.0.0.01
Publisher	Ashwini
Description	Second Extension by Ashwini
Compatibility Id	1.0.0.00
Capabilities
Prerequisites
Dependencies

AppId	3c88160c-e0eb-4fe1-b4f6-011e45d74b10
AppName	EDD1.02
AppPublisher	Ashwini
AppDescription	Second Extension by Ashwini
AppVersion	1.0.0.01
AppCompatibilityId	1.0.0.00
Capabilities	{}
Prerequisites	{}
Dependencies	{}

AppId	3c88160c-e0eb-4fe1-b4f6-011e45d74b10
AppName	EDD1.02
AppPublisher	Ashwini
AppDescription	Second Extension by Ashwini
AppVersion	1.0.0.01
AppCompatibilityId	1.0.0.00
Capabilities	{}
Prerequisites	{Table=18, CodeUnit=80}
Dependencies	{EDD1.01, Ashwini, 1.0.0.00}

Rate this:

Share this with:

Like this:

Rate this:

Share this with:

Like this:

Rate this:

Share this with:

Like this:

Rate this:

Share this with:

Like this:

Rate this:

Share this with:

Like this:

Rate this:

Share this with:

Like this:

Rate this:

Share this with:

Like this:

Get-Help

Export-NAVApplicationObject

Export-NAVApplicationObjectLanguage

Export-NAVAppPermissionSet

Split-NAVApplicationObjectFile

Compare-NAVApplicationObject

Merge-NAVApplicationObject

Import-NAVApplicationObjectLanguage

Get-NAVAppInfo

Get-NAVApplicationObjectProperty

Set-NAVApplicationObjectProperty

Update-NAVApplicationObject

Get-NAVAppManifest

Set-NAVAppManifest

New-NAVAppManifest

New-NAVAppManifestFile

New-NAVAppPackage

Publish-NAVApp

Unpublish-NAVApp

Install-NAVApp

Uninstall-NAVApp

Get-NAVAppTenant

Repair-NAVApp

Rate this:

Share this with:

Like this:

Rate this:

Share this with:

Like this:

Rate this:

Share this with:

Like this: