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