Becoming an MVP

I am extremely excited this month I was awarded the  Microsoft Data Platform MVP.

So besides getting lots of Microsoft shirts, stickers and a cool glass art- work,

The #reward and the #award

A post shared by yana (@yanaber777) on

I have also made new friends, participated in community meetings and learned how important it is to share with the community my expertise, time and efforts.

This week, we had a meeting of MVP’s in the Vancouver area, organized by Simran Chaudhry, our Microsoft manager, and it was great to meet there people that are MVP’s for 7 and even 15 years. So what do we talk about in those meetings? Mostly about fun stuff, we get to know each other, our goals and personal things, it is a community of friends after all that work together. We also talk about new initiatives and hope to be working together.

My mailbox is filled with questions and answers by very talented and friendly people who can answer and improve just about anything. I am still pretty new to this and off course not yet contributing much in the MVP community but I have set myself a personal goal to be able to help and ask or answer a number of questions. I hope to meke it there with some hard work and fun spirit.

Meanwhile I am part of the organization team for Vancouver’s SQL Saturday, August 28th, and I would love to see everyone there.

Registration

Measure the innovation – KPI for Product Initiation

In my presentation about Product Management using Office 365, I am using PowerBI and the connection to flow to track the performance of the Product team during the “Ideation” phase.

The core of this solution is a basic SharePoint list, where the product team can track it’s ideas and vote on them. While those ideas are gathered in order to be implemented in the product later on. Meanwhile the feature or enhancement can be considered to be part of the product scope. The list is very simple by design:

The list is in the Product Group and on the top, you can see all the participants of this group.

I have connected this list to the Product group section in PowerBI, to get the KPI report in there.

 

The ideation process can be problematic to measure and there might be irrelevant ideas that got voted out. If a company would like to reward it’s employees based on the new ideas they bring to the table this might be a quick measure for creativity if implemented as part of the Product team site.

Connection PowerBI to the SharePoint list gives a quick view that can be embedded both in the product team site as a PowerBI Web Part and added to the company management dashboard elsewhere as an external report outside of the group permissions.

Great Apps News

This was the picture I have seen today, I knew that Apps are part of PowerBI, I thought I was ready and knew everything about it but seeing it this morning in feed when opening PowerBI made it official.  Isn’t it exciting?

So I thought it must be something connecting PowerApps and PowerBI but apparently it is a confusing term. From this link we can connect to other applications in our organization (86 options for the PowerBI related apps).

This is also not the connection to PowerApps, this is done through Flow and Microsoft has 121 pages document describing the how. () You can also visit our SharePoint user group or PowerBI user group in Vancouver we talk about this connection in many of the sessions.

There is an Application insights option to be used as part of this platform any application your organization has already got, similar to the get data menu options; but how can I create and app?

I have recently created a report and a dashboard that has the result of the BC elections.

After publishing the report and the data set to PowerBI, I have created a Dashboard based on the report and created an App.

The result is a Shipping App that is part of the Shipping Group.

The Apps is currently a Read only replacement of the former content packs. For now, I cannot move the app or share it with another group, I cannot do much or customize the already created visualizations. This will probably change very soon.

What I can do?

  1. I can see all the artifacts in the App:
    1. the Dashboard,
    2. the Report
    3. the Data Set
  2. I can filter and drill down data in the dashboard
  3. I can filter the data in the report and choose the displayed fields from the existing once.

What is the Business need?

If I have users that I want them to access only this and not play too much with the visualizations or the data.

  Here is the report view with the filters.

On the top (the red ribbon color is customization I can see the number of dashboards, reports and data sets that are part of the app.)

The right navigation is for the filtering option

The canvas is static and you cannot edit the existing report (Permanent or temporary?)

The report can be embedded in SharePoint group same as any other report, I assume that the apps will be visible in the SP gallery soon, I will keep posted if the apps are going to be available by group or cross organization. I would also add an option to publish in the App Catalog, that would have given me the option to link it here. Very excited to see what’s next and write about it here.

 

 

 

 

Election in BC – Publishing and the Insights Part 3

In the first part I have prepared the data for the visualization, uploaded the data to the desktop version, deleted the unnecessary rows, populated the data in the empty rows and parsed the data. In the 2nd part I have created a custom color scheme using a designer that has generated a jason file and created some basic visualizations.

Now I got lazy, didn’t want to create the drill down visualization for the results by the regions and decided that PowerBI must create that for me using Insights.

The first step is to publish my local dashboard created in PowerBI desktop into my online account. I simply click Publish, login to my o365 client and publish to the group of my choice (Shipping). 

This action creates 2 objects:

  1. The report that I have just build
  2. The data set associated to the report that I have created from the 2 html pages in part 1

The next step is clicking on the small lamp icon to see all the insights! So I am getting the Quick insights by PowerBI:

or…I can create a dashboard, by going to the report and choosing to Live pin all the data visualizations.

The dashboard:

On the top I can ask questions about my data, such as: How many NDP? and get the answer: 717,073 NDP

Number of Candidates? was auto complete to Number of Candidates ballot names (this is the column name after all…) and the answer is: 369.

I can pin each data visualization separately, to get the insights, unlike with Live pin which only gets the visualization.  Even create another one with another color scheme, to which I can get the insights from the dashboard itself.

On the left, the default colored election results; on the right the insights by district

In order to share it with the entire Organization I have created an App! This is the new feature from PowerBI and this will be discussed in a separate post.

The report is right here with all it’s vibrant colors! I have added it by simply clicking File-> publish to the web and added the created i frame to the Text (code view) of this WordPress post

Election in BC Part 2 Colors!

In the first part I have prepared the data for the visualizations, I was complaining about colors in the existing article so let’s start by making this all look like a circus!

Preparing custom color scheme –

I did mention that I didn’t like the colors int he existing news story so I have tried the Color Scheme Generator, in the PowerBI tips and tricks blog (Really recommended!!!) recommended by @Chass in his video Better use of colors in Power BI (Guy in a cube channel). I have created a scheme of my own!

 After Choosing the colors, I have downloaded the color theme to my computer and Upload it to my PowerBI desktop.

The scheme was imported using the Switch Themes  in the home ribbon tab.

I love the Tree Map visualization, also a heat map comes in mind. For the parties in each region it seems like a good fit even without the regions actual map. Another version of the same is the brick map. The simple bar chart gives more accurate information but the Tree Map can tell the same story. I have decided to put them side by side for my first report page just to see what makes more sense for people?

You might be able to vote on the one you like the best… (part 4?)

There is one more subject that is covered by the map visualization.

So I have started creating the new visual in a separate page, but then I have realized, PowerBI can tell me all this by simply clicking on “Insights”.

What do I have to do for that? To Publish it – Will explain in Part 3

Election in BC Part 1 – Preparing the data

Yesterday was May 9th that means the election day!

There were elections and I felt like our beloved province of British Colombia didn’t get the necessary attention and doesn’t have it’s own data visualization solution! There was a small one in the Vancouver sun: but it is a custom based solution by their talented Graphic designer: Lucas Timmons (GitHub, Twitter: @lucastimmons) not a PowerBI or Tableau!  Not to worry BC Yana to the rescue!

The data sources are 2 pages with the elections results:

1st data set: Results split by candidates: http://electionsbcenr.blob.core.windows.net/electionsbcenr/GE-2017-05-09_Candidate.html

2nd data source: Results split by parties:

http://electionsbcenr.blob.core.windows.net/electionsbcenr/GE-2017-05-09_Party.html

Since Lucas did the map data visualization (I don’t like the colors but I will not be doing the same).

So What shall we do?

  • I have opened PowerBI Desktop
  • added a data source of the type Web

1st data set: Results split by candidates:

  • Added the link to the first HTML page and in the selection dialog chose: Table 0. PowerBI recognized the table on the page and uploaded it.

Data manipulation I have used: Complete missing lines in the 1st data set

The data set items look like this and I need to fill all the blanks to get the community name:

Electoral District Candidate’s Ballot Name Affiliation Total Valid Votes % of Popular Vote
Abbotsford-Mission Dan Cameron Christian Heritage Party of B.C. 607 2.60%
Andrew Murray Christie BC NDP 6,754 28.89%
Simon John Gibson BC Liberal Party 12,080 51.67%
Jennifer Holmes BC Green Party 3,940 16.85%

(referenced from the PowerBI community site)

What shall I do? how to fill the missing data? so the obvious choice might be export everything to Excel or even just copy to Excel and duplicate the result. Let us not despair, this is exactly what DAX & M are for!

I have added a new custom column with the following function written to add it in DAX:

= Table.AddColumn(#”Changed Type”, “Custom”, each if [Electoral District]=”” then null else [Electoral District])

Than I have used the Fill option, from the Transform ribbon tab to fill down all the Null cells. The problem that accord when the Fill down didn’t work in the original column was that those cells did not have the null value to begin with, means they are not empty and cannot be filled according to the definitions.

In the View Ribbon tab, I can see all the data manipulation I have completed so far using M:

let
Source = Web.Page(Web.Contents(“http://electionsbcenr.blob.core.windows.net/electionsbcenr/GE-2017-05-09_Candidate.html”)),
Data0 = Source{0}[Data],
#”Changed Type” = Table.TransformColumnTypes(Data0,{{“Electoral District”, type text}, {“Candidate’s Ballot Name”, type text}, {“Affiliation”, type text}, {“Total Valid Votes”, type number}, {“% of Popular Vote”, Percentage.Type}}),
#”Added Custom” = Table.AddColumn(#”Changed Type”, “Custom”, each if [Electoral District]=”” then null else [Electoral District]),
#”Filled Down” = Table.FillDown(#”Added Custom”,{“Custom”})
in
#”Filled Down”

Data manipulation 2-

I have also removed the total 100% ballot box number as the bottom value for each community. This value we can get later in the 2nd data set by party and connect them by community. Or simply add a measure to calculate it.

I haven’t used code this time, only the existing PowerBI options.

  1. Transform ribbon tab->Split the column [Electoral District]
  2. Used the custom split with the custom value “of” (common for all the ballot boxes numbers there)
  3. Got 2 columns when in the target columns had “null” value for all the rows without the ballot counting
  4. Used the top row filter to simply filter out all the rows that didn’t have null
  5. In PowerBI, now that I don’t need the extra data manipulation column, I just got rid of it 🙂 delete column it is.

The current M code for the whole operation looks like this: (It’s different for a data source that didn’t have to be changed from HTML, the references might contain other commands for different data source data types)

let
Source = Web.Page(Web.Contents(“http://electionsbcenr.blob.core.windows.net/electionsbcenr/GE-2017-05-09_Candidate.html”)),
Data0 = Source{0}[Data],
#”Changed Type” = Table.TransformColumnTypes(Data0,{{“Electoral District”, type text}, {“Candidate’s Ballot Name”, type text}, {“Affiliation”, type text}, {“Total Valid Votes”, type number}, {“% of Popular Vote”, Percentage.Type}}),
#”Added Custom” = Table.AddColumn(#”Changed Type”, “Custom”, each if [Electoral District]=”” then null else [Electoral District]),
#”Filled Down” = Table.FillDown(#”Added Custom”,{“Custom”}),
#”Renamed Columns” = Table.RenameColumns(#”Filled Down”,{{“Custom”, “Full Electoral District”}}),
#”Split Column by Delimiter” = Table.SplitColumn(#”Renamed Columns”, “Electoral District”, Splitter.SplitTextByDelimiter(“of”, QuoteStyle.Csv), {“Electoral District.1”, “Electoral District.2″}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Split Column by Delimiter”,{{“Electoral District.1”, type text}, {“Electoral District.2″, type text}}),
#”Filtered Rows” = Table.SelectRows(#”Changed Type1″, each ([Electoral District.2] = null)),
#”Removed Columns” = Table.RemoveColumns(#”Filtered Rows”,{“Electoral District.2″})
in
#”Removed Columns”

The data set now look like this:

2nd data source: Results split by parties:

 

(referenced from the PowerBI community site)

  • I did the same entering the URL of the results by party
  • Entered to edit the data and split the column with the ballet boxes. I only need their number not the real time count since the election are over.

The data manipulation in the query mode I have used: Split cells,
the split value was: “of” since the values are: “35 of 35”. I ended up with 2 columns containing the same number. so I deleted one of them.

  • I have made sure that all the columns are from the type Decimal number, since I really don’t want to upload a column containing numbers as a text column and wonder why things keep not working? 🙂
  • Next I have uploaded the data to the data set as well.

Now I have 2 Tables containing the data – so easy now the visualization part begins!

(in part 2)

 

PowerBI Premium the big news 2nd part

In this part I will focus more on the online version and the big news that are going to happen.

Let’s first of all review the things according to the features that are going to be available at launch vs the once we will need to be waiting for (just 1 table instead of 15 pages…)

Feature Description Available at launch?
Workload & Query capacity isolates Your hardware, the performance is up to your resources only not shared Yes
Huge data-set storage 100TB, data set no longer limited to a 1GB Yes
No User Quotas From 10GB in pro tenant to 100TB in Premium for each capacity Yes
Data-set Refresh rates From 8 per day in pro to 48 per day in Premium. Yes
Schedule limit From hourly refresh interval to minute based Yes
Large DataSets cash From 1GB to 50 GB limit in P3 (The 32 v-Cores) No – gradually
Incremental refresh (my personal fav) Incremental update No
Refresh Policy With incremental update a policy can be defined No
Pining Data-sets to Memory PowerBI dynamically manages the memory, large or less active data sets will go into the back-end nodes, pinning the important data-sets so they still are a priority No
Data Refresh Nodes Dedicate nodes to separate interactive queries and refresh to optimize performance No
Large distribution – Read only replica Create read only replica to publish, maintain better performance for other reports No
Geographic distribution Geo Replicas to prevent performance issues (*) No

* The coolest sentence in the whitepaper, PowerBI also has something to do with the speed of light! It limits the performance when sending the data far away.
The biggest news – Dedicated Capacity! you don’t need to share anymore!!! What does it mean?

Well this is my sketch of how I understand this all dedicated solution with PowerBI premium is going to be based entirely on the White Paper mentioned:

The pro version will still be out there, so individual users who are already paying and deploying and also small organizations, who need not more than 8 syncs a day, 1GB of online upload and share can still stay on the same subscription.

This will all change, evolve and develop in the near and far future, for now it is my intake on the important part’s of the PowerBI Premium news latest and greatest.

Don’t forget to watch Guy in the Cube to understand how your Apps are going to be affected:

LINK guy in a Cube

 

PowerBI Premium & Report Server announcement part 1

So Microsoft have announced something extremely significant for me this week…they are launching soon the powerBI premium with the reporting services for the On Premise solution (something many clients that are not so happy with the idea of moving to the cloud were really waiting for and I have written about it here: Link to On Premise PowerBI Architecture).

The exciting announcement of the Report Server was made in the technet blogs site.

The white paper following the Report Server announcement can be found here to download

The White Paper that was introduced yesterday, is revealing a bit more about the Reporting server and the future directions for the on premise version of PowerBI. I have discussed the on premise PowerBI option in my post about a month ago and came to the conclusion that it might be too soon. So…Are we there yet? I mean after publishing and including all the planned features that are part of the release?

“The new Report server will trace the roots into the existing SQL server reporting services.” (according to the White Paper referenced above). The creation of the reports is done through the PowerBI desktop, but what about the delivery?

  1. PowerBI mobile App
  2. Mobile Browser (meaning uploading the reports to the cloud the dedicated to the organization through Premium or mutual one)
  3. Embed into an application (meaning uploading to the cloud, grab the API and embed it in the application or use the SharePoint web part)

Unfortunately for some of us, there are clients who still think, even after the dedicated hardware solution, that the cloud is not an option. Therefore even after the new features and the exciting solution are implemented, there are still some clients who are going to stay out of the game.

Creating a Product using PowerBI?

In order to showcase ability using PowerBI dashboards, I was looking for ideas of what exactly can be done and displayed as a project. Thinking about the Dashboard as a product is also a topic widely discussed on Quora, Twitter and and other social media boards.

When looking for ideas that might appeal to businesses, I think showing your capabilities might be a great first step. The business need will emerge during the conversation and understanding the process. There are few solutions that were created by Microsoft that might give the initial ideas for dashboards you might build as products:

Departments | Microsoft Power BI

Other than that, I think about the customers needs, if it is a specific industry, such as healthcare, think about what they would like to know, which questions they would ask and where is this data located. Use one of the many free public data repositories to showcase and build the dashboards.

If you are into showing your capabilities there are some ideas I might be helpful with

I have build my resume (Yana Berkovich Product Manager) using powerBI and have a lecture of how it was done (

 Many customers will appreciate dashboards with live data relevant to their business.

The dashboard Link

The “holy grail” of this specific search for product based on PowerBI, is the live data. There are many free data sets that can be used for a dashboard that is no live. In the powerBI community there is a link for data sets of this sort. my-favorite-public-data-sources

There are also not live connections that can be used – for example, you can download all your tweets data as an Excel file, it is available from Twitter, for 90 days, and use it for any data queries and visualizations as a static data source. You can connect to the Twitter live feed using API or other synchronization option.

Live data sets are more scarce and tend to be associated with cost. So when I had the brilliant idea of connecting the NHL or NFL results and data about players, it appeared to be blocked and the membership fees for a direct API access were pretty hefty. The traffic volume is simply going to be too high for them to maintain I suppose. This also means that the demand for data analytics and data visualization solutions in those areas is pretty high as well (there are several movies about that part…)

What are we left with?

Dashboard templates – If you already have a specific data set, for example lab tests, you can create a dashboard that has in mind not only the specific client but general for the industry. In my examples I use a data set for engine oil tests. When creating the dashboard for roughly 700 sample tests, I am thinking about other companies that can reuse the solution to visualize their data, by merely connecting their data set. The problem is off course the different fields and definitions. Changing the query might prove to be more difficult than adding the visualizations from scratch.

My “live Performances” :-)

My presentations, Meetups and Webinars…See you in every PowerBI and SharePoint/Office 365 Meetup in Vancouver or Redmond!

I am going to be helping Charles Sterling (Chass), with his Power Apps presentation in Office 365 user group Redmond, this July

SQL Saturday Redmond PASS #613- Using PowerBI and Office 365 to enable Decision Support in Small Companies Slides Deck

 

Recording
SharePoint Saturday Vancouver 2017 – Product Management using Office 365 tools Slides Deck  Recording
SharePoint Saturday Calgary 2017 – Product Management using Office 365 tools Slides Deck
SharePoint Saturday Calgary 2017 – Give More Power To the users PowerBI and API, presenting with Scott Stauffer Slides Deck Recording
Vancouver PowerBI user group March 2017 meeting- My story with data and PowerBI The Dashboard Recording
Vancouver SharePoint user group October 2016 meeting- Give more Power to the users! PowerBI and API MeetUp Page
Webinar – November 2016 PowerBI as a Decision support methodology enabler  Recording

 

On Premise PowerBI, what’s in it for the organization?

About a year and a half ago I have witnessed a very interesting interaction between a consultant and a client. The client kept insisting that they wanted a PowerBI based solution, the free dashboard one, and that they cannot join the cloud. PowerBI was only just getting started. The consultant, created an Excel report, using Power Pivot and Power query from the clients SQL data base and…he added a black background. I found it genious, the client was super excited and added it in their SharePoint site as the “on premise PowerBI”.

Reminds me of:

Well what was changed since?

The online version has developed into a “connect everything” model and the advantages seem to be enormous comparing to many other platforms. While the on premise version lacks the maturity to be implemented in the organization. (In my humble opinion).

Let’s review the necessary components and the options we are presented with by PowerBI:

  • Connectivity to the on premise DB
  • Synchronization with the DB
  • Access to data according to permissions levels (Autentication)
  • Publishing the data in a portal solution
  • Self Service BI
  • Usability and access log

The closest model that Microsoft is suggesting in the Governance whitepaper is:

Well, it still does include the cloud for:

  1. Authentication
  2. Publishing
  3. Logs

What if we have an organization that does not include an internal SharePoint portal and Azure AD to authenticate with?

So we get the following architecture:

This particular architecture has many disadvantages:

  1. It allows multiple copies by multiple users and the data governance can hardly be implemented- the same problem Excel spreadsheets are creating, and getting read of stand alone Access or Excel solutions just to get a better looking one is not an option
  2. The synchronization is done manually, each time the .pbix file is opening, through a local client that must be installed on the machine
  3. The authentication and synchronization is performed by the DB itself, if the organization doesn’t have the AD implementation
  4. There are no usability logs, they are created in the desktops per user

I have thought of a creative solution for problem no 4, build an aggregator and a timer job that is going to connect and collect the data to a PowerBI report on one of the administrators machines. This is not exactly feasible…but I get some points for creativity. 🙂

What does PowerBI still has to offer?

Well the Self Service BI option, friendly interface comparing to other Enterprise solutions and recently ability to later use the data sets as data sources.

In conclusion, in order for PowerBI to work on premise, I would love to see if the integration can work great on something other than Office 365. Not all the organizations are willing to move to the cloud, and right now I feel that PowerBI lacks a few features to be a leading product.

So if we quickly sum up the current state, I would like to add that

PowerBI on premise? well maybe…not just yet

Requirements PowerBI on premise
Connectivity to DB Direct query direct access to DB
Synchronization Client, slow and authentication against DB each time
Permissions PowerBI gets the permissions form AD application or public to all the users who have access to the cashed data in the report, if the data was imported
Publishing Stand alone files

Can be reused as data sources

Self Service BI Stand alone, flexibility and easy interface
Activity log Log only, on premise don’t have a viewing tool

 

 

My thoughts on the technical aspects of manuall migration of an Access or other legacy forms application

Divide the migration to 3 different parts:

  1. The governance & Process flow
  2. The content- migrating the data
  3. The interface

Governance, planning and process flow

First of all decide what is going to be migrated and create a migration map and a document containing all the information.

Before any migration to the cloud I would like to refer you to the following article: Five pitfalls to avoid when migrating to the cloud

The most important one is: do not forget the what? who? and why business users are going to use it.

The simple understanding of who access what? which users are going to use this application and how? have a basic process flow and well defined personas for your application. If it is big enough, user stories will be needed as well.

After reviewing those topics you would be able to figure out the SharePoint solution architecture vs the existing one in Access. The how will be cleared, should you use several lists? how about groups? sites? PowerApps? maybe several pages just for the view and some custom made buttons?

2. Migrating the data –

    1. Create the data repositories – the lists, you can export the Access data by tables to Excel.
    2. SharePoint, unlike Access, is not a relational Data Base, that means that the data should be not normalized and hierarchy’s are not going to work the same unless a custom solution is applied.
    3. The easiest way is using the quick edit mode on a pre-created tables – yes that means basically rebuilding the Access solution

3. Migrating the interface

Prepare the solution – the data is going to reside in SharePoint lists? the access is going to be through list forms? another 3rd party solution? What is the user interface going to look like?

I would recommend to use Power Apps in order to achieve the forms look and feel and enjoy the benefits of the flow for any business rules you have mapped in the first part. The Power Apps can be connected to several data sources, hence creating the illusion of a relational DB after all. A very important tip, make all your editing in Power Apps desktop application. The online version is a bit, sneaky ;-).

Also on Quora

Connecting PowerBI Desktop to Oracle?

One of PowerBI’s biggest strengths is the connectivity to almost everything. (They say that through PowerApps it is going to connect to your home espresso machine soon enough), the challenge is when things don’t go as expected.

What is the business need?   Data visualization from ORacle data base.
What are the constrains? No cloud is allowed, everything is on premise.
The proposed solution? Use PowerBI to create a local dashboard that I can save and share internally in the company. The data displayed will be from ORACLE database that is local in the company.

Simple enough? well as I have later discovered not that simple…

In order to connect Oracle and PowerBI desktop there is a need in a connection on the Oracle side, otherwise we get the following error:

In the excellent blog post from the PowerBI community, the instructions are:

  1. Make sure you have 32bit/64 bit matching windows and PowerBI
  2. Install the ODAC from Oracle site (32bit or 64 bit)
  3. Go to PowerBI desktop
  4. Add new data connection, choose data base and Oracle
  5. Add the servers name and that’s it 🙂

So I have followed, but what happens when it doesn’t work? I got the same error as if nothing was installed to begin with!

It is recommended to install and uninstall all the Oracle client, install diffrent version, make sure you have matching 32/64 bit versions and so on…

I have notice something different, between the connection properties in my Oracle SQL developer tool and the PowerBI data connection window:

So what am I writing in the server name? Nothing seemed to work and worse, I was prompted to use my machine access credentials instead of the DB access credentials. Meaning the connection that PowerBI is trying to establish is to some object on my computer instead to the data base.

The easiest path was discovered in this excellent blog about Excel Power Pivot: PowerPivot Pro

Connecting to Oracle Using TNSnames.ora

The process goes like this:

Creating a file named TNDnames.ora either manually or using the ODAC release 4 which can be found right here for the 64 bit version 

Download the Zip file-> unzip-> run the file setup.exe setup as administrator

The setup will ask you to enter the exact same data you have entered in your database connections properties.

  • Connection Name (Please do not put space or special characters in there)
  • Hostname
  • Port (usually 1521)
  • Service Name
  • User Name
  • Password

There will be a file created in the following directory:

C:\app\client\YOURCLIENTSNAME\product\12.1.0\client_1\Network\Admin

The files name is: tnsbanes.ora

In the blog post I have mentioned before, contains the full file created.

Mine looked very similar:

MYCONNECTIONNAME =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = HostName )(PORT = PortNumber))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ServiceName)))

After this file is created, either manually or by using the ODAC wizard, it is the equivalent of the connection string. I have entered in the PowerBI get data option the following:

Server = MYCONNECTIONNAME

and used the database credentials in the permissions part.

It worked!

More tips around creating the connection:

  1. You do not need to install Visual studio just for the connectivity option. If you are prompted to install VS and the error while installing the connector is:

That means it is not the right connector for the data connection proposes.

2. As recommended in the PowerBI community, clear all the data connections associated with this connection. There might be automatically saved credentials from your previous attempts that will make things not work. In the desktop version go to file-> options and settings-> Data Source Settings; simply delete the not working connections.

Good Luck! and don’t forget to visit the powerBI community: https://community.powerbi.com

The one who got away

I am answering many questions in Qoura, it usually makes me think and explore about the things I find interesting such as SharePoint.

I was asked a question on a topic that people just love to comment about. Why is Sharepoint so disliked?

OK let’s put it plain and simple, why do people hate SharePoint?

 

First of all let us think who those people are. To dislike a platform, a knowledge management solution or anything else the workplace “makes” you to use instead of what you are used to manage your documents or creations in the previous workplace or at home, at first, might be understandable. Later on, most of the users should be indifferent to the tool they are using and simply enjoy their work or ask for improvements when a new process, workflow, site or report is needed. However, this is not always the case with SharePoint.

Just like in My answer, I would like to divide those users who dislike or perhaps hate SharePoint so much into 3 groups:

  1. Those who tried and got burned – The product was implemented in their company not in the best way, presented as the single answer to all the questions and perhaps the version they used was 2007 or even earlier so things didn’t go smoothly.
  2. Developers – SharePoint is a product which prefers customization! Customization is something developers usually see as beneath them. so they resist it or create beautiful and sophisticated custom solutions that are so hard to upgrade.
  3. Late adopters – they want things to stay as they are and have difficulty adopting to new ways of managing their processes and documents. They simply want to get things done. SharePoint is notorious for adding new features and being constantly upgraded. This is it’s strength and weakness at the same time.

Now what have triggered this question? who dislikes SharePoint so much? the topic came out of a stack overflow survey and SharePoint was the most disliked among the sites users:

StackOverflow users usually are developers, SharePoint has not the best public relations in this group. End users are less emotional about their sharing option as long as it is implemented and explained, sometimes they even not realize it is a SharePoint solution. Developers usually dislike it. This is a “one size fits it all” product, it has less options and abilities than niche products, easier to implement and get used to and works with other familiar products. All of which makes it less appealing for the more perfectionist crowd. 😉

Now lets look at the big picture:

Most Loved, Dreaded, and Wanted Platforms Stackoverflow

Anyone can see that SharePoint is in a good company SalesForce, WordPress (where this exact blog is written and everything Windows related). So let’s look at the demographics:

The vast majority of the people participating in this survey identified themselves as web developers. (more than 72%). Even if they are holding more titles than that and are for example data analyst and a web developer, they still are the majority. SharePoint is a local intranet in it’s core. I am a web developer (when I am not too lazy to create a blog instead of using Worpress;-)) Off course we think we can do it better, plan it better and have million features we would have included if we were in charge! The developers expect need and want more and this is exactly their prerogative as the developers. They are the “one who got away” and maybe at times it is a demographic that should not be pleased, otherwise they will not improve and progress the tool. Let them go, they will come back to make it better or design something even more awesome based of the lessons learned from SharePoint.

The survey is talking about platforms, for developers who prefer less standardized and more open source solutions they can build upon such as Linux. SharePoint is all about the end user and customization on the other hand.

We still have the other 2 groups of the late adopters and the “burned once” users. For those, there are many user adoption strategies and helpful resources. One of which I would like to recommend. An excellent blog post about driving the users adoption and getting those users back.

The Excellent Joanne Klein 

O365 Adoption: Noteworthy user groups

So for all of us it is always useful to look at the story the data tells us not just the headlines.

Thanks and Happy SharePointing!

 

SharePoint Saturday Vancouver

This was a very exciting event for me as a proud member of the VanSPUG board – the local SharePoint user group and personally as a SharePoint enthusiast.

The preparations and organization went well, we had many sessions with leading SharePoint, BI, Dev and user adoption experts.

We also had an announcement that was made by Charles Sterling (Chass) from Microsoft regarding a new connectivity between Power Apps and PowerBI, demonstrated the first time during his session. I had the privilege to be his “data monkey” and help with all the data entries. A useful trick that he is always preaching for, add a person who is going to help you presenting and will enter all the code lines, url’s and maybe even move the slides forward. You can even pick a person from the audience, and worse case scenario, just ask him or her to step back. During this presentation we have also learned that it is better having all your designing with PowerApps done in the desktop editor rather than the online tool… ohh well.

The event was a great success, you can see the pictures in our Facebook page:

VanSPUG Facebook 

My tips for organizing an event like this and sharing my expirience in VanSpug:

  1. Select the winning team! I am part of the best board I have ever had the chance to work with and it shows in the growth of our community, the cooperation, supporting each others decisions and commitments
  2. Plan – We have used the tools such as planner to complete the planning. We came up with a checklist of tasks, assigned and delivered. In a workplace it is a common sense, but in a volunteer organization it is not that obvious.
  3. Communicate- we have tried the teams communicator and Skype chat. Eventually settled on the Office 365 Outlook. The emails are short and we are all attentive to the requests.

Small but significant- looking at other SharePoint Saturday and SQL Saturday events, the small stuff were the once that made the difference. Clear navigation option, understanding where the rooms are located and having all the lectures printed near the room’s door really helped the audience to get to the right place in the right time. Just like navigation is an important part of your SharePoint portal, it should also be a priority in the venue.

I had also the privilege to participate in the event and give my presentation regarding Product Management using all the Office 365 tools. I must say that groups, planner and off course SharePoint and PowerBI were my leading recommendations. I will explain in details in another post.

 Link to the YouTube Video

Calgary SharePoint Saturday

  • I was speaking in SharePoint Saturday Calgary April 1st 2017.

    Calgary SharePoint Saturday event site

    My first session was with the wonderful Scott Stauffer, his blog: http://scottstauffer.ca/about/ and the twitter feed: @SQLSocialite, you should follow him if PowerBI and SQL is somehting you find interesting. We have discussed PowerBI basics, made some jokes and showed the end users that you don’t have to be part of the IT team in order to be successful with PowerBI. 

    You can fins our session recording in my YouTube.

    Power BI & API: Give more power to the users!
    The 2nd session was more about Product Management and the art of the possible using SharePoint, powerBI and office 365.

    Product Management in SharePoint online and Office 365 end to end process

    Product Managment with Office 365.PNG

    I had lots of fun, mat new and old friends and was happy to share my experiments with SharePoint and data platforms.

Creating my story with Data

So I was looking to make my resume stand out while presenting my skills with the new and shiny PowerBI. (my powerBI page in my site)

I was inspired by an article, Business Insider has created about Marissa Mayers resume.

So I have continued, just like with any other product, using my Business Analysis skills:

Defining the the Business need​:

  • Who is the audience?
    • Potential employer/customer
    • People I am looking to impress
  • Why I want them to see it? ​
    • To show case my skills with PowerBI
    • Look into the details of my resume without getting bored
  • What do I want to  show them? ​
    • How can I tell a story using data

Now that I know what is going to be part of my story with data, I have started to think about the technical aspect.

Technical requirements  ​

  • Real time update? Custom visuals? Sharing? Mobile? ​

​The real time update won’t be necessary, once a month I will update my current work status and the dashboard can be updated within 24 hours. My subscription (pro) allows up to 8 scheduled updates a day, with an hour minimum intervals. I can also manually refresh and republish the dashboard if this is a live demo. The reports were created for my web page, which is built using HTML CSS and some JS for the fun parts. The design is responsive but I will not create separate mobile version of the data visualizations. The out of the box PowerBI versions seemed too look fine in the mobile browser. Since the target audience is going to watch from the browser and not from the PowerBI mobile app as this is not a link to the dashboard but an embedded report.

Designing the solution

What do I need?

  1. Data Source to create a data set –
    • I have used first a google docs table but it had one problem, it was not saved as a table and I needed to save it again from scratch.
    • I have used a SharePoint list, but discovered that this cannot yet be a direct data source (this was done just a month before it became an option 🙂 )
    • I have used an Excel file, that is saved in a SharePoint library and is accessible publicly. (it’s called One Drive bc I have first saved it there and than moved to the SP library. It works from One drive the same way) I have connected this as a data source in PowerBI, after saving the data in the Excel as a table which is very important to make the spreadsheet a valid data source.
      The data source contains a line for each month since my graduation and starting my career, September 2005 till today (March 2017). In each line, I have shown my work location, the field, technologies I have been working with, company I have worked for and off course the job title.
  2. Report/Reports
    Based on the data-set I have created a new report:
    2.1 dragged the first data visualization I have chosen, the map, checked the columns from the table indicating location and
    2.2 Chose the next visualization the Treemap, chose the Group as the Location city, the Values as Count of Workplace (which will count the number of times the specific workplace is displayed in the rows)
    2.3 Dragged the bar-chart visual to have the information regarding my experience in each industry type to be presented.
    The Treemap visualization filter selection can be seen here:
  3. Publish option –

I have saved the report in my PowerBI account, that means it was uploaded to my storage, the PowerBI API library, on my Office365 account. After which I have chosen in the menu File-> Publish to web
PowerBI have opened a wizard, where I could choose the option of embedding in my WebSite. The result was the following: 

This is what I have pasted in the HTML code of my WebSite:

<div class=”container”>
<div class=”row”>
<iframe width=”800″ height=”600″ src=”https://app.powerbi.com/view?r=eyJrIjoiZTY4Y2M4NTktMjZmMS00ZWEwLTkyZmQtMDliMmMyZjRjYmVjIiwidCI6IjAyMTJmZDNkLWQ1ZGYtNGVkNy05NzYyLTQ5ZGRmZDQ1ZGM1MCIsImMiOjZ9″ frameborder=”0″ allowFullScreen=”true”></iframe>
</div>
<div class=”row”>
<div class=”col s12″>
<iframe width=”800″ height=”600″ src=”https://app.powerbi.com/view?r=eyJrIjoiN2NjZDIzMjUtNThjNC00YTUzLThhNDgtMDBlMmM2ZGYzNzVkIiwidCI6IjAyMTJmZDNkLWQ1ZGYtNGVkNy05NzYyLTQ5ZGRmZDQ1ZGM1MCIsImMiOjZ9″ frameborder=”0″ allowfullscreen=”true”></iframe>
</div>
<div class=”col s12″>
<h2 class=”text_h2″> MY PowerBI Twitter Feed </h2>
<a class=”twitter-timeline” href=”https://twitter.com/search?q=%23PowerBI%20from%3Ayana_berkovich” data-widget-id=”843961672100274176″>Tweets about #PowerBI from:yana_berkovich</a>
<script>!function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0],p=/^http:/.test(d.location)?’http’:’https’;if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src=p+”://platform.twitter.com/widgets.js”;fjs.parentNode.insertBefore(js,fjs);}}(document,”script”,”twitter-wjs”);</script>
</div>
</div>
</div>

The last div is for the Twitter feed related to PowerBI that I have created, just to add this is just for fun and actually works! 🙂

Testing ​

  • Hi there, have you seen my page yet? (5 AM Israel time is 7 PM in Vancouver…)​ I have asked people with different browsers and used the chrome different sizing option to test in different screen sizes. (There are still some problems with the top links in my site but that’s not part of the PowerBI topic…so next post)

Requirements gathering featuring myself as the business user is a very interesting experience; Documentation is still important! (Otherwise it takes you forwever to find the right report.​

The page link: http://yanaberkovich.com/powerBI.html ​

The link to the lightning session

Let’s talk about workflows

Writing the word Workflow in the SharePoint online section of the office store, resulted 92 (!) different apps. So let’s check what we have got.

First of all why do you need the workflow? what should it be doing?

If the answer is everything well, there are the most famous in the market – Nintex workflow, they do come with a price tag. Nintex Workflow for Office 365 enjoy, I know I did when my client could afford them.

There are less expensive tools that are created to give similar functionalities such as: UMT360 Workflow Visualization, K2 blackpearl for SharePoint (never worked with them so I cannot form an opinion just mentioning).

Another great solution, is easier to implement and has several workflows built in, for almost end user to be able to configure: Infowise Smart Action Pro Infowise are very well positioned company, they have several working and user friendly solutions, I had the privilege to work with their tools and if they cater to your requirements would highly recommend.

For the rest of the tools available, I have never worked with directly, so lets divide them into categories:

  1. HR/Payroll related workflows: KasPer Pro HR with Self Service
    1. Track vacations Holiday Authorization Management, Leave Request Pro
    2. Policies confirmation SP Employee OnBoarding
    3. Expenses tracking – Expense Approval Pro , Expense Management for SMEs
  2. Specific process/occupation related workflows
    1. Clinic – medicine processes – Clinic App
    2. Budget management – Budget Workflow Manager
    3. Travel management – Travel Request System
    4. Replacing CRM – KasCur Pro CRM
    5. There are more
  3. Managing your workflows: Workflow Manager
  4. Workflows with project or project related content: Actionspace: Tasks-Projects-Workflows (SharePoint), Project Documentation
  5. Form Based workflows – (You can use Info path but this has probably less functionalities with better graphic)
    1. FlowForma – Business Process Enablement Tool
    2. TeamWorkPath

Finally, in the army we had a list workflow, set from the 3 stages workflow and some customization using SP designer on a form, to get your commander approval signature for taking a weapon from the weaponry. Later we have “upgraded” the form to infopath so the units symbol could be added. I doubt we can find something like this in the online store but it was a workflow I have created, so it is all about what, and who you actually need to support with the workflow.

(Also published on Quora)

Security Groups in SharePoint What happens when someone leaves?

SharePoint 2013 doesn’t have dynamic security groups. It is based on the AD groups already in the organization. The users cannot be created in SharePoint only existing users in the organization can be added. The same happens in Office 365, you need to add the user for the entire organization either as a guest (free account), contact or organizational user.

This is from the Office 365 main menu and not internally to SharePoint

The users are added in this screen according to the different groups:

Now, the users can be added into specific groups or sites in SharePoint or any other Office 365 application. If a user deleted from here, and no longer exist as a user in the organization, he will disappear from the sites and groups preventing be ghost users in a SharePoint site. (At least according to what I have seen, I am sure there are some exceptions perhaps someone can comment?)

So What happens on premise? SharePoint 2013? Someone leaves the organization and than what?

Back in Share Point 2010 and before the so called “Ghost users” were very common phenomena especially when cutting the sites permissions from the main site which was easily done but extremely hard to govern.

There are 3rd party tools, based usually on Power-Shell commands that creates the dynamic groups ability in SharePoint. Those solutions are sometimes necessary when the organizational AD is poorly managed, the 2 teams (SharePoint and Active Directory) don’t have the best internal communication between them or the governance policy was defined separately. In smaller organizations, sometimes a delete Power-Shell function is used when someone leaves brutally deleting all the permissions by site collection. Not dynamic or efficient but works and leaves no tails of orphaned users. Remove-SPUser . Same can be applied for adding one.

The other way is to use the AZUE AD dynamic groups, which is very efficient for hybrid SP solutions (partially in the cloud and partially on premise). You still need to manage the users in AD and the groups.

Office 365 groups Life-cycle

The Group for Office 365 is more of a new way to call a Team Site.

Although it is a distribution list, just like and Outlook group, it has the following features just like any other SharePoint site:

· Document Library for storing and working on group files and folders

· OneNote notebook for taking project and meeting notes

· Planning tool for organizing and assigning tasks and getting updates on project progress

You can find more information right here in Microsoft’s site:

http://en.share-gate.com/blog/gr…

Groups have a similar life-cycle to any other SharePoint site:

To determine whether the groups are the right solution for your business need and organization, you can use this wonderful blog post:

http://www.neroblanco.co.uk/2016…

As in any SharePoint site, prior to opening the group, there are few things that have to be established for the process:

Good luck and share the group experience with me!

(was published in Quora )