CEO Exec Forum – Office 365 and Power BI

Recently I gave 2 presentations to the CEO Executive Forum at their semi-annual meeting; this time located in Detroit. The first was on Connectivity and Collaboration, the 2nd on Metrics, Reports and Dashboards. These focus on Office 365 and Power BI, including SharePoint and middleware.

Connectivity and Collaboration

For Connectivity and Collaboration, we focused on how as humans we need to make connections between things, and between things and people. I then described some ways this is possible with Office 365, SharePoint, OneNote and Teams and middleware.

We worked on answering questions like:

Can we automate our business processes in Office 365?
What is the best way to manage documents?
How do I reduce data entry?

Metrics, Reports and Dashboards

During Metrics, Reports and Dashboards I took time to focus on the difference types of numbers to pay attention to, compared metrics to KPI’s and talked about what a dashboard should contain. There was some demonstrations including Power BI.

We worked on these questions:

What business metrics do I care about?
What numbers should I pay attention to?

I want to make the PowerPoint presentations available to the attendees of the conference and anyone else that finds the topics interesting.

Below are the 2 PowerPoint presentations you can download:

Connectivity and Collaboration

Metrics, Reports and Dashboards

Feel free to email or comment with questions or feedback.

Clearing All Rows From All Tables

This couple of SQL statements will delete all the rows from all tables.

  1. It disables referential integrity
  2. DELETES or TRUNCATES each table
  3. Enables referential integrity
  4. Reseeds rows with identity

— disable referential integrity
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
GO

EXEC sp_MSForEachTable ‘
IF OBJECTPROPERTY(object_id(”?”), ”TableHasForeignRef”) = 1
DELETE FROM ?
else
TRUNCATE TABLE ?

GO

— enable referential integrity again
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO

— This will reseed each table [don’t run this exec if you don’t want all your seeds to be reset]
EXEC sp_MSForEachTable ‘
IF OBJECTPROPERTY(object_id(”?”), ”TableHasIdentity”) = 1
DBCC CHECKIDENT (”?”, RESEED, 0)

GO

Thanks to Mauro Cardarelli’s post

Find Which Table(s) Contain a Column and the INFORMATION_SCHEMA Namespace.

To find which table(s) contain the PersonID column try this:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE ‘%PersonID%’

In the AdventureWorks DB this is what you see:


So of course you can search for any column, not just PersonID.

Another interesting tidbit is that any ANSI compliant DBMS provides the INFORMATION_SCHEMA namespaces. From this namespace you have access to the metadata on any DB object. You can look up information on Stored Procedures and Functions using INFORMATION_SCHEMA.ROUTINES or use any of the many different objects within this namespace: COLUMNS, ROUTINES, CHECK_CONSTRAINTS, PARAMETERS, TABLES, etc.

Enjoy