Running a business is about helping people

By Evan Schmidt

My view is that the primary goal of a business is to help people.

 

A healthy business should support all of its stakeholders:

Customers: by providing business benefits which far outweigh the costs.

Employees: by rewarding them through compensation, job satisfaction and quality of life.

Owners: by generating a fair profit which encourages future innovation.

Other Stakeholders: by promoting fair business practices which benefit suppliers and communities.

 

In turn, these stakeholders will support the business and the business will thrive. The stakeholders are like the four corner pillars of a great building. To ignore any one of the pillars will create a lopsided business which is likely to crumble during storms.

 

Running a business is about helping people. Making money will follow.

 

Simple but effective scheduling tips

By Evan Schmidt

The following tips are sure to make your next consulting engagement less stressful.          

·       Schedule extra time for meetings. Schedule one hour instead of 30 minutes and give time back rather than going over.

·       Schedule work blocks for critical deliverables.

·       Schedule travel time between distant meetings.

·       Schedule lunch to ensure a healthy break each day.

·       Schedule far in advance. It’s a lot easier to cancel a meeting that it is to create one at the last min.

·       Schedule an online meeting in addition to a conference room. In person meetings are still the best but it’s always nice to have Lync conference session available for screen sharing.

 

Email: Best practices for a BI consultant

By Evan Schmidt

Here are some best practices regarding email which I have learned over the years.  

 

Improve Content

·       Determine the actionable items

o    Use words like: Respond, Provide, Complete, etc.

 

·       Put the main recipient who is responsible for the action item on the TO line

o    Address the main recipient by name  e.g. Bob,

o    Move other names to the CC line.

 

·       Use bullet points to list the most important information or action items

o    Always number questions so they can be easily referred to

 

Increase Efficiency

·       Keep older threads going whenever possible

o    If no one responds then go to your sent items, dig up the old email, and reply all to ‘bump’ the thread

 

·       Review CC list and avoid blindly copying managers

o    Help limit the email load for managers by removing them or putting them on the BCC line

 

·       Respond inline when appropriate

o    When you respond inline try to respond to all questions even if your response is a simple “correct”

 

Document Your Work

·       Send meeting notes to document key outcomes

o    Keep notes short and simple

o    Put action items on top

 

·       Close the loop, and wrap up ongoing threads, by documenting the work you have completed

o    Write a summary of the completed work

o    Add a link to the task on the SharePoint team site

o    Add attachments

 

Quick method to prevent RI errors and lost data

By Evan Schmidt

This mini-blog describes an ETL trick for validating data quality before loading a cube.

 

To build a proper cube with multiple data sources all new data should be joined or mapped to an existing dimension. A robust system may be used to manage these mappings or a quick hack can be used to ensure data quality. The following hack shows how to easily identify RI issues and prevent lost data.  RI issues and lost data will result in cube processing errors and incorrect results.

 

This example shows a list of targets which are typically provided in a spreadsheet and can change often. The list of countries is an existing dimension in the cube.

CountryName	RevenueTarget
Canada	100
US	500

 

CountryID	CountryName
2	Canada
3	France

 

Data quality in the targets table could be enforced by adding a foreign key to the table and requiring a valid Country ID. Alternatively the following code can be used to find RI issues. If an RI problem is encountered then an error is generated.

 

SELECT

   CASE WHEN C.CountryID IS NULL THEN ‘Error Missing Revenue Target Country’ ELSE C.CountryID END AS CountryID

   , T.RevenueTarget

FROM (   

          –List of Targets

          SELECT ‘Canada’ AS CountryName, 100 AS RevenueTarget

          UNION

          SELECT ‘US’ AS CountryName, 500 AS RevenueTarget

   ) T

LEFT JOIN (    

          –List of Countires, Country name must be unique

          SELECT 2 AS CountryID, ‘Canada’ AS CountryName

          UNION

          SELECT 3 AS CountryID, ‘France’ AS CountryName

   ) C ON C.CountryName = T.CountryName

 

 

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value ‘Error missing Revenue Target Country’ to data type int.

How to find a hidden unicode character using SQL Server

By Evan Schmidt

This mini-blog describes how to analyze every character in a unicode text string in order to find hidden characters, variable-byte characters, and unexpected unicode characters. This can be a useful because certain unicode characters can cause some applications to fail unexpectedly. This code will help you analyze the data and find the problem character.

 

 

DECLARE @nstring NVARCHAR(100)

SET @nstring = N’TeSt’

 

DECLARE @position int

SET @position = 1

 

DECLARE @CharList TABLE (

Position INT,

UnicodeChar NVARCHAR(1),

UnicodeValue  INT

)

 

WHILE @position <= DATALENGTH(@nstring)

   BEGIN

   INSERT @CharList

   SELECT @position as Position

      , CONVERT(nchar(1), SUBSTRING(@nstring, @position, 1)) as UnicodeChar

      , UNICODE(SUBSTRING(@nstring, @position, 1)) as UnicodeValue

   SET @position = @position + 1

   END

 

SELECT * FROM @CharList

 

 

The results of this query will display the unicode value for every character in the string:

Position	UnicodeChar	UnicodeValue
1	T	84
2	e	101
3	Ș	536
4	t	116

 

Note the particularly large unicode value which shows this character is clearly not a standard ASCII value.  

Many-to-Many Part 3: Prevent Disappearing Data

By Evan Schmidt

This article describes how to prevent disappearing data when using many-to-many relationships in SSAS.

 

If a dimension is related to a fact via a many-to-many relationship then the bridge table must contain a reference to every fact record or data will disappear. (Note: the Grand Total will always show the full total.)

 

This can be dealt with by configuring the unknown member for the dimension or by explicitly creating an undefined or unknown member to show that no relationship exists.

 

This article describes explicitly creating a member called “No Expertise” in the Expertise dimension. Some will argue that it is poor design to add member which does not represent a real area of expertise into the expertise dimension. It is true that this can cause conflicts but in many cases is just makes good business sense. In this situation it’s would be nice to know how much has been sold by sales people with no area of expertise, and so I would argue that “No Expertise” is a tangible dimension member.

 

To make this happen simply add “(No Expertise)” to the dimension and bridge table. The parentheses are a quick way to influence sort order.

Sales Person	Expertise
Bob	Art History
Bob	Modern Art
Bob	Local Art
Nancy	Modern Art
Trent	(No Expertise)

 

 

The report will show the total revenue driven by people who have a given area of expertise:

Expertise	Distinct Qty	Distinct Rev
(No Expertise)	4	10,000
Art History	3	25,000
Local Art	3	25,000
Modern Art	4	75,000
Total	8	85,000