Power BI Service Dataset Connect to Power BI Desktop

This Features Allow you to  connect datasets in the Power BI service to Power BI Desktop . This feature allows you to create new reports off existing datasets you’ve already published to the Power BI web

To get started with this feature, you’ll first need to enable the preview option in Power BI Desktop. Navigate to File > Options and settings > Options > Preview features and enable Power BI Service Live Connection. You’ll need to restart Desktop in order for the option to take effect.

From Get Data – we can get Power BI service Option

From there, you will get a list of your workspaces. Expand each workspace to see the list of datasets you can use to create reports.

Advertisements

New Navigation Features In Power BI

Navigation Make easy to  access content quickly.  With Recent, get quick access to the dashboards and reports we’ve most recently accessed all of your workspaces.

It have new location for all dashboards that are shared with us  by others.

It  also appears in your ‘My workspace’ or Group Workspaces. The content shows up on the main area, with separate tabs for dashboards, reports, workbooks and datasets.

 

We can switch workspaces by clicking on the arrow next to the current workspace name.

Turn on to Access this  New Features Go to  Settings menu by selecting the gear icon in the top right corner of Power BI screen and choosing Settings.

Select the General tab and choose Preview features.

Select the On radio button to try out the new experience. Click Apply.

Push Datasets To Power BI Streaming Datasets By Microsoft Flow

We Can use Power BI streaming datasets to easily build real-time dashboards by pushing data into the REST API endpoint, and having that data update in seconds on their streaming visuals

For Creating Streaming Datasets

First Of all Go To Power BI Web and click on Streaming Datasets option .

From there, we will create a dataset of type API

We Can Name the dataset as We want but necessary to remember Then, add the following fields to the streaming dataset

For Twitter We can Create data with time (DateTime) , tweet (Text) , sentiment (Number)

Now For Create a Flow which will push Tweets and their sentiments to Power BI. Go To https://flow.microsoft.com/en-us/

Sign in, and then go to “My Flows”, then “Create from blank.”

Now, go to Twitter category, and select the “When a new tweet is posted”  and  Use Power BI For Search term .It will start a Flow whenever a tweet which contains the search term is posted.

we will pipe these Tweets into the Microsoft Cognitive Services Sentiment Detection Flow action to understand the positivity of the Tweet content. This action takes in a Tweet, and outputs a number from negative to positive.

Select “New Step,” then “Add an action,” then search for “Sentiment Analysis” and select the “Cognitive Services Text Analytics – Detect Sentiment” action For That We Need  API Key

entered API key and Information in it and pipe in the Tweet text to the sentiment detection action by selecting it from the dynamic content pane on the right side of the screen. Flow should now look like the following

Now Go to “New step,” then “Add an action,” and then enter “Power BI” into the search box. Select “Add row to streaming dataset” from the actions list

Select the name of the workspace, then the name of the streaming dataset in the first step, and select the Table titled “RealTimeData.” Note that all streaming datasets created in powerbi.com will have one table named “RealTimeData.”

Create a dashboard with a streaming visual in Power BI. Go To  Power BI Web , go to a dashboard, select “Add tile,” then “Custom streaming data” and finally the name of the streaming dataset that we created in the first step.

Dashboard is Look Like

Power Bi Report View Updates

Drop down slicer –  Power Bi adding adding  more types of slicers to reports. The drop down slicer.This Slicer is use full for when we have a lots of data to locate.

In Slicer Custom Visual We Can make it Drop down Box

We can See Data like below

 

Hierarchical axis – Hierarchy With in this Visual, in a Drill Down Option of Visual We Can Get Detail View Of Hierarchical axis. And Better Understanding of particaular field Details.

 

We Can Use hierarchical labels by turning off the label concatenation experience in the formatting pane.

 

Matrix conditional formatting –  In matrix We Can Turn On Conditional formatting by opening the field well menu for the measure and selecting Conditional formatting.

By Conditional Formatting We can make Different Between Lowest Middle and Highest  Value By Giving Color

Its Look Like..

 

Axis label and title color control – By it we can now format  axis title color  also separately from the axis labels.

Get Data From Mongo and Store it again to Mongo DB.

Get Data From Mongo and Store it again to Mongo DB.

Use Case:

Here we will create one database and collection in mongo db and get data of it in Spark’s dataframe after which we will again store it back to the mongodb collection.

Versions Used:

Scala-2.11.8
Spark—2.0.2

Steps:

1. First we need to create database in mongo db.If database already exist we can use it.To create a database write following command.

2. Create a collection in the database.Here in Customer database create collection named CustomerInfo.

3. Now insert some data into the collection.

4. Now we have these documents in our CustomerInfo collection.

5 .Now to connect mongodb with Spark first we need to include two dependency libraries in our
project.

“org.mongodb.spark” % “mongo-spark-connector_2.11” % “2.0.0”,
“com.stratio.datasource” % “spark-mongodb_2.11” % “0.11.1”

6. Now start programming with Spark.As in version 2.0.2 we do not need to create SparkContext
diffentely we will now create SparkSession as shown below.

val spark=SparkSession.builder().appName(“Spark Mongo Integration”).master(“local[*]”).config(“spark.some.config.option”,”some-value”).getOrCreate()

7. Now in this version of mongo spark connector it provides two classes i.e ReadConfig and
WriteConfig which we can use for read from mongodb and write to mongodb respectively.

Here we will create object for ReadConfig class first which we will use to read data from
Mongodb.

Use below command:

val readConfig=ReadConfig(Map(“uri” -> “mongodb://localhost:27017/Customer.CustomerInfo”))

Here we can mention different options for ReadConfig.Refere https://docs.mongodb.com/spark-connector/v2.0/scala/read-from-mongodb/

8. Now use MongoSpark.load method to create an RDD representing a collection.
MongoSpark.Load() method can take argument as sparkSession and ReadConfig object.

val info=MongoSpark.load(spark,readConfig)

info.printSchema()

println(“Total count of records is :” + info.count())

println(“First element of data is :” + info.first)

Note:

• SparkContext has an implicit method called loadFromMongoDB() to load data from MongoDB.

For example:

sc.loadFromMongoDB() // This Uses SparkConf for Configuration

sc.loadFromMongoDB(ReadConfig(Map(“uri” -> “mongodb://example.com/database.collection”))) // Uses the ReadConf

9. We can also do some modification on the data and we can also apply some operations and can
store it back to the mongo db on the new collection.

For example:

If we want to multiply whole english info column by two and create a new column calles new_info then we can do that using following:

First create WriteConfig object.

val writeConfig=WriteConfig(Map(“uri” -> “mongodb://localhost:27017/Customer.CustomerInfo_new”))

Then do the operation on previously made RDD for ReadConfig object.

For example:

val info_updated=info.withColumn(“Current_age”,info(“age”)+3)

10. MongoSpark.save() method accepts WriteConfig object .So we use this method to save data
back to mongodb.

MongoSpark.save(info_updated.write.mode(“overwrite”),writeConfig)

info_updated.show()

We can see the new created collection in MongoDb.

Note:

• RDDs have an implicit helper method saveToMongoDB() to write data to MongoDB.

• For example, the following uses the documents RDD defined above and uses its saveToMongoDB() method without any arguments to save the documents to the collection specified in the SparkConf:

• documents.saveToMongoDB() // Uses the SparkConf for configuration

• Call saveToMongoDB() with a WriteConfig object to specify a different MongoDB server address, database and collection

• documents.saveToMongoDB(WriteConfig(Map(“uri” -> “mongodb://example.com/Customer.CustomerInfo”)))// Uses the WriteConfig

Read Data From Kafka Stream and Store it in to MongoDB.

Read Data From Kafka Stream and Store it in to MongoDB.

Use Case:

In this tutorial we will create a topic in Kafka and then using producer we will produce some
Data in Json format which we will store to mongoDb.
For example ,here we will pass colour and its hexadecimal code in Json in kafka and put it in the Mongodb table.

Version which we are using :

Kafka—0.10.0
Spark—2.0.2
Scala—2.11.8

Steps:

1. Start the zookeeper.
Run the command below:

bin/zookeeper-server-start.sh config/zookeeper.properties

2. Start the Kafka severs on your local machine in standalone mode.
Run the below command:

bin/kafka-server-start.sh config/server.properties

3.Create a topic in Kafka server from which you want to produce messages.

For example:

We want to create a topic names test.

bin/kafka-topics.sh –create –zookeeper localhost:2181 –replication-factor 1 –partitions 1 –topic ColourCoe

Note:

You can check the list of topics using following command :

bin/kafka-topics.sh –list –zookeeper localhost:2181

4.Kafka comes with a command line client that will take input from a file or from standard input
and send it out as messages to the Kafka cluster. By default, each line will be sent as a separate
message.

Run the producer and then type a few messages into the console to send to the server

bin/kafka-console-producer.sh –broker-list localhost:9092 –topic ColourCode

5.Kafka also has a command line consumer that will dump out messages to standard output.

bin/kafka-console-consumer.sh –bootstrap-server localhost:9092 –topic ColourCode –from-beginning

6.Now before writing program we need to include following depedecies in buid.sbt

“org.apache.spark” % “spark-streaming-kafka-0-10_2.11” % “2.1.0”,
“org.apache.spark” %% “spark-streaming” % “2.1.0”,
“com.stratio.datasource” % “spark-mongodb_2.11” % “0.12.0”

You can get this from Maven Repository :

https://mvnrepository.com/

7.First create a Sparksession and set master.

val spark = SparkSession.builder().master(“local[*]”).config(“spark.some.config.option”, “some-value”).getOrCreate()

8.Now create a StreamingContext which is for kafka streaming.

val ssc = new StreamingContext(spark.sparkContext, Seconds(2))

Here we are passing sparkContext as spark.sparkContext.

9.Now create one val containing all the values for kafka parameters.

val kafkaParams = Map[String, Object](“bootstrap.servers” -> “localhost:9092”,
“key.deserializer” -> classOf[StringDeserializer],
“value.deserializer” -> classOf[StringDeserializer],
“group.id” -> “use_a_separate_group_id_for_each_stream”,
“auto.offset.reset” -> “latest”,
“enable.auto.commit” -> (false: java.lang.Boolean))

• Here Bootstrap.servers indicates a list of host/port pairs to use for establishing the initial connection to the Kafka cluster.
• key.Deserializer indicates Deserializer class for key that implements the Deserializer interface.
• value.Deserializer indicates Deserializer class for key that implements the Deserializer interface.
• group id indicates A unique string that identifies the consumer group this consumer belongs to.
Note :

Also you can refer to so many other properties here : http://kafka.apache.org/documentation.html#newconsumerconfigs

10. Now create val to set the parameters for mongoDb.

For example :

val mongoDbFormat = “com.stratio.datasource.mongodb”
val mongoDbDatabase = “ColoursInfo”
val mongoDbCollection = “Colour”

val MongoDbOptiops = Map(MongodbConfig.Host -> “localhost:27017”,
MongodbConfig.Database -> mongoDbDatabase,
MongodbConfig.Collection -> mongoDbCollection)

where

mongoDbFormat –is the format in which we store in mongodb using this library.
MongoDbDatabase –is the database in Mongodb in which we want to save that collection.
MongoDbCollection– is the collection of database in which data will be stored.

By default host for MongoDb is 27017 which is mentioned in th Options.

11.Now here we can get data in any format from kafka stream.Lets assume we are getting
data in Json format.
We can specify schema structure for the data using StructType.

For example in incoming data if we have two filed name and age we can specify scehma like

val schemaString = “colour code”

val fields = schemaString.split(” “).map(fieldname => StructField(fieldname, StringType, nullable = true))

val schema = StructType(fields)

12.We need to pass the topic name as a parameter in the KafkaUtils method.So

val topic1=Array(“ColourCode”)

13.Kafka library provides us KafkaUtils class of which createDirectStream method we can use
to fetch the kafka streaming data and get in format of key value pair.

val stream = KafkaUtils.createDirectStream[String, String](ssc, PreferConsistent, Subscribe[String, String](topic1, kafkaParams))

14. We get Dstream in the stream val.We will covert that stream into Dataframe.For that first we
convert it into RDD of stream which we will covert into dataframe.

That dataframe we will store into MongoDb.

val elementDstream = stream.map(v => v.value).foreachRDD { rdd =>
val PeopleDf=spark.read.schema(schema).json(rdd)
PeopleDf.write.format(mongoDbFormat).mode(SaveMode.Append).options(MongoDbOptiops).save()
}

15. At the end start spark context .

ssc.start()
ssc.awaitTermination

16.Now go to the directory where project reside and run it.

17.Produce some messages in Producer for consumer to consume.

Note :

Here as we have taken data in Json format enter the data in json format.If you enter data other than
Json format it will show null in the Mongo db table.

In Consumer you will get the same messages produced by consumer irrespective of the fomat.

18.Now you can see the this data inserted in the Mongo db table.

Here name of our database is KafkaMongoTest1 and Collection name is People1.

So here we are able to see the data inserted in the Mongo db table.

Tutorial: How to load and save data from different data source in Spark 2.0.2

In this blog we will discuss about Spark 2.0.2 . It demonstrates the basic functionality of Spark 2.0.2. We also describe  how to load and save data in Spark2.0.2. We have tried to cover basics of Spark 2.0.2 core functionality  like read and write data from different source (Csv,JSON,Txt) .

Loading and saving CSV file

As an example, the following creates a DataFrame based on the content of a CSV file. Read a csv document named team.csv with the following content and generate a table based on the schema in the csv document.

id,name,age,location
1,Mahendra Singh Dhoni ,38,ranchi
2,Virat Kohli,25,delhi
3,Shikhar Dhawan,25,mumbai
4,Rohit Sharma,33,mumbai
5,Stuart Binny,22,chennai

 

def main(args: Array[String]) {
  val spark = SparkSession
    .builder()
    .master("local")
    .appName("Spark2.0")
    .getOrCreate()
  val df = spark.read.option("header", "true")
    .csv("/resources/team.csv")
 val selectedData = df.select("name", "age")
   selectedData.write.option("header", "true")
    .save(s"src/main/resources/${UUID.randomUUID()}")
  println("OK")
}

 csv1.png

csvsave.png

Loading and saving JSON file

Here we include some basic examples of structured data processing using DataFrames. As an example, the following creates a DataFrame based on the content of a JSON file. Read a JSON document named cars_price.json with the following content and generate a table based on the schema in the JSON document.

[{"itemNo" : 1, "name" : "Ferrari", "price" : 52000000 , "kph": 6.1},  {"itemNo" : 2, "name" : "Jaguar", "price" : 15000000 , "kph": 3.4},  {"itemNo" : 3, "name" : "Mercedes", "price" : 10000000, "kph": 3}, {"itemNo" : 4, "name" : "Audi", "price" : 5000000 , "kph": 3.6}, {"itemNo" : 5, "name" : "Lamborghini", "price" : 5000000 , "kph": 2.9}]
def main(args: Array[String]) {
  val spark = SparkSession
    .builder()
    .master("local")
    .appName("Spark2.0")
    .getOrCreate()
  val df = spark.read.option("header", "true")
    .json("/resources/cars_price.json")
  val selectedData = df.select("name", "price")
  selectedData.write.option("header", "true")
    .save(s"src/main/resources/${UUID.randomUUID()}")
  println("OK")
}

savejson.pngjsonsave

Loading and saving txt file

As an example, the following creates a Data Frame based on the content of a text file. Read a text document named rklick.txt with the following content and generate a table based on the schema in the text document.

Rklick creative technology company providing key digital services.
Focused on helping our clients to build a successful business on web and mobile.
We are mature and dynamic solution oriented IT full service product development and customized consulting services company. Since 2007 we're dedicated team of techno-whiz engaged in providing outstanding solutions to diverse group of business entities across North America.
Known as Professional Technologists, we help our customers enable latest technology stack or deliver quality cost effective products and applications
def main(args: Array[String]) {
    val spark = SparkSession
      .builder()
      .master("local")
      .appName("Spark2.0")
      .getOrCreate()
    import spark.implicits._
    val rklickData = spark.read.text("src/main/resources/rklick.txt").as[String]
    val rklickWords = rklickData.flatMap(value=>value.split("\\s+"))
    val saveTxt = rklickWords.write.text(s"src/main/resources/${UUID.randomUUID()}")
    println("OK")
  }

txtload.pngtxtsave

We would look at how we can create more useful tutorials to grow it , then we would be adding more content to it together. If you have any suggestion feel free to suggest us 🙂 Stay tuned.