Data boundaries and data quality in a BI reporting system

By Evan Schmidt

The following article discusses problems than can arise when a BI reporting system has data managed within the boundaries of the reporting system itself.

 

Ideally a BI reporting system is a consumer of data rather than a system of record. In most cases the BI system should receive data and should not become the system of record for maintaining data. However, to get the reporting system launched in a reasonable amount of time, it’s often the case that the reporting system does manage some data within the boundaries of the reporting platform.

Here are some of the common types of data and situations where the BI system becomes the system of record:

• Data mapping between systems
• Connecting systems that do not share the same exact values or IDs
• Master Taxonomy Data
•  Geography hierarchies, groupings of attributes, etc.
• Sales and Targets
• Added data that is small in scope and does not change often
• Rules for KPI Indicators
• These may be indicator thresholds
• Metric Definitions
• Rules of qualification for various measurements

 

First, it’s important to realize that BI developers can be lazy when the BI system becomes the system of record for certain items. It’s easy to overlook the following IT like rules for keeping track of data in a BI system:

·         Expose the data in the reporting environment so users can see it

·         Keep track of created date, updated date

·         Keep track of created by user, updated by user

 

Second, it’s important to notice that keeping data in the reporting system can become a bad habit and can easily grow until it’s out of control. Keep the following rules in mind:

·         Do not create unnecessary data sets in the reporting database

·         Keep it small and simple

·         Keep the data organized

 

Finally the best long term solution for a BI reporting platform is to transition the data management responsibilities to new systems such as a targeting system, a master data management system, or a integrate the data into an existing application.

Second, it’s important to notice that keeping data in the reporting system can become a bad habit and can easily grow until it’s out of control. Keep the following rules in mind:

·         Do not create unnecessary data sets in the reporting database

·         Keep it small and simple

·         Keep the data organized

 

Finally the best long term solution for a BI reporting platform is to transition the data management responsibilities to new systems such as a targeting system, a master data management system, or a integrate the data into an existing application.

Downloading data from a cube via MDX

By Evan Schmidt

Here are the steps required to download data from an SSAS cube using MDX within a SQL statement.

 

Avoid double hop authentication issues

Since need to use windows authentication to access the cube you may get an error due to “double hop” authentication. 

To avoid this use one of the following methods:

1)    Run the MDX/SQL on your local instance of SQL server

2)    Run the SQL under a SQL Agent job which where the service is a running under a valid domain account

3)    Set-up Kerberos to avoid the double hop issue

4)    Remote desktop into the host server

 

Create a linked server

EXEC master.dbo.sp_addlinkedserver @server = N’ LINKEDSERVERNAME’, @srvproduct=N’ssas’, @provider=N’MSOLAP’, @datasrc=N’SERVERNAME’, @catalog=N’CUBENAME’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’LINKEDSERVERNAME’,@useself=N’True’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

 

Use open query to execute the MDX

SELECT *

FROM OpenQuery(LINKEDSERVERNAME, ‘SELECT [MEASURES].[Count] on 0 FROM CUBENAME’) as X

 

Short BI Assessment Checklist

By Evan Schmidt

Here is very simple requirements gathering and BI assessment checklist.

Key items to discuss with the client

·  Business goals and objectives for the project:

o What is the job role of the project sponsor and organizational structure of the business group?

o Who are the key stakeholders?

• Users

• Support Staff

• Management

o What decision will be made and what actions can be taken?

o What challenges and risks may arise?

o How will success of the project be measured?

• scope, time, resources

• proof of concept

• iterations

 

·  Systems involved in the project:

o Which systems are used for the business?

o When and why are these system used?

 

·  Current state of reporting:

o How do you measure current performance?

o Which reports are used?

o Which reports are dreamed about?

o How do you measure change in performance over time?

o What are the strategic vs. operational use cases?

o What are some other big and long-term reporting ideas?

One Key to Consulting Success: Make your client number one

By Evan Schmidt

To become a great consultant you must have the ability to build a great reputation within your client organization. Building this reputation will ensure customers spread your name and help ensure your business grows. To build this great reputation it’s critical to make your clients feel good about you and your work.

 

Know Who You’re Trying to Make Happy

·       Who’s Number One?

o    The Client: They hired you

·       Who want to feel smart and in control?

o    The Client: They want to feel good

·       Who wants to feel like they are making all the decisions?

o    The Client: They should feel powerful

 

Serve your Client

·       How can I make the client feel like they are number one?

o    Respect them

§  Describe things in simple terms and don’t dumb them down too much.

o    Trust them

§  Have faith that the client will ask questions if they are confused.

§  Don’t restate the same thing multiple times.

o    Give them control

§  Ask clear simple questions.

o    Prepare them ahead of time

§  Send them emails ahead of time so they can review ahead of time and make their own decisions.

 

Consulting with Charisma

·       How can I spin the product to make the client feel like they are number one and ensure I am addressing their needs?

o    Call out the differences ahead of time

o    Anticipate their concerns and say “I know this raises some concerns”

o    Ask if they have further concerns

o    Listen

o    Recap there response and decisions so they have faith

·         How can I present our knowledge and ideas tactfully?

o    Build examples

o    Show various options

 

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