ROLAP - now a viable option

The time for ROLAP has arrived:

Most operational data stores (ODS) and data warehouses (DW) reside on a rowstore relational database. It's a great tool for data organization, but rowstores and their related indexes don't mesh with the demanding needs of analytics, business intelligence, reporting and self-service oriented cubes. So it was not uncommon to push selected data down from the data warehouse into customized MOLAP cubes. MOLAP cubes offered business analysts great self-service data mobility, usually using a connected EXCEL pivot table, or more expensive tools such as Tableau.

Ideal for the analyst, but pushing data down from a data warehouse or operational data store to a MOLAP cube was lots of added work, risk and most importantly, loss of data timeliness. Once the data warehouse or ODS was updated, yet another process had to be started to update and process the MOLAP cube. Once a day updates were not uncommon for MOLAP cubes. Fast data it was not! And if you have reviewed the literature on large MOLAP cubes, you've found that they can get unwieldy.

MDX was another road block. Using a BI tool such as Excel, analysts were shielded from the sometimes complex MDX commands. But problems arose when analysts wanted to do custom queries against the MOLAP cube. Queries that would be far easier against a traditional data warehouse using SQL.

Columnar databases to the rescue.

With its release of SQL Server 2016, Microsoft has a full featured relational database that can be run as a columnar database. By simply upgrading to SQL Server 2016, and converting your existing rowstore indexes into a columnstore, your data warehouse or ODS can now support a ROLAP cube. Efficient and fast. And no more MOLAP processing.  Here at Realized Design, we have done some limited testing using ROLAP, and have been pleased with the results. For the relational backend, we have used both SQL Server 2016 and the specialized analytic/columnar database EXASOL. In both cases, ROLAP proved comparable to a MOLAP design. But without all the extra processing. And thus far more elegant. You can find a detailed analysis of our work so far with EXASOL here: EXASOL review at RealizedDesign.

But don't just take our word for it. Here are two additional links where individuals have successfully used ROLAP cubes against very large databases. And they have been please with the results.

In 2014, Karen Gulati did exploratory ROLAP work using SQL Servers new column store indexes. See: Harnessing the Power of both worlds.

Also in 2014, Hilmar Buchta did some work with SSAS ROLAP against an MSFT Parallel Data Warehouse  See:  Parallel Data Warehouse (PDW) and ROLAP - Hilmar Buchta. 

So, just when you thought SSAS multi-dimensional was dead, its back!

Next Steps:  Build an SSAS ROLAP Cube using EXASOL

ROLAP Polling Query - use a change tracking table

Setting up the polling query for our real-time ROLAP cube against an EXASOL database, we used an easy, but very bad practice approach.  We set our polling query directly against a fact table. Nice and easy for testing and proof of concept. Production? don't consider using it.

Polling a count against a small or medium size fact table might won't create much of a problem. When the dataset is huge it might take more resources.  So, when the data set is really large, and our goal is very near real time, why not poll a very small table.

Data Change Table

Let's call it TrackDataChange.  A minimalist version will have just a few columns:  the source system that loaded the data, the load date and a primary key ID. The only difficult part is you have to configure your ETL process(es) to insert a single row to your new TrackDataChange table. The new record simply marks that the ETL process has completed.

With the TrackDataChange table in place, and your ETL processes configured to insert a row after inserting new fact table table, your polling query will look like this:

        SELECT COUNT(*) FROM DWTEST.TRACKDATACHANGE;

You'll probably want more than just the basic columns. The key point is to run SSAS's polling query against an independent table dedicated to tracking fact table changes.

Pros

the polling query avoids data tables used for data analysis

the TrackDataChange table is narrow and holds few records

the TrackDataChange table is quick to query

the TrackDataChange table is quick to insert into

Cons

the ETL process is marginally more complex

Date Dimension - DDL/DML to create and maintain

For any data warehouse or business intelligence work, you are going to need a stock process to create and then periodically maintain a date dimension table. The emphasis here is on maintenance.

Periodically, you'll need to add to the date dimension.  Over the years, I've worked out a fairly simple, but sound date dimension create and update process.   Below are the details:

DateKey

Dates never change, so the datekey can have intelligence. It is one of the few dimensions that can break the rule where a dimension key should never have intelligence. The datakey uses the ISO format:  YYYYMMDD. This allows you to assign a datekey to business data using only the date, without having to reference the date dimension.

The scripts

create schema rds;
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[rds].[Date_dim]') AND type in (N'U'))
DROP TABLE [rds].[Date_dim]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [rds].[Date_dim](
 [DateKey] [int] NOT NULL,
 [FullDateAltKey] [date] NULL,
 [DayOfWeekNum] [tinyint] NULL,
 [DayOfWeekName] [nvarchar](10) NULL,
 [DayOfMonthNum] [tinyint] NULL,
 [DayOfYearNum] [smallint] NULL,
 [WeekOfYearNum] [tinyint] NULL,
 [MonthOfYearName] [nvarchar](10) NULL,
 [MonthOfYearNum] [tinyint] NULL,
 [CalendarQuarter] [tinyint] NULL,
 [CalendarYear] [smallint] NULL,
 [CalendarSemester] [tinyint] NULL,
 [FiscalPeriod] [tinyint] NULL,
 [FiscalQuarter] [tinyint] NULL,
 [FiscalYear] [smallint] NULL,
 [FiscalSemester] [tinyint] NULL,
 [FiscalDayOfPeriodNum] [tinyint] NULL,
 [FiscalDayOfYearNum] [smallint] NULL,
 [FiscalWeekOfYearNum] [tinyint] NULL,
 [FiscalPeriodOfYearName] [nvarchar](10) NULL,
 [SimpleCalendarDate] [varchar](20) NULL,
 [SimpleFiscalDate] [varchar](20) NULL,
 [SimpleCalendarMonth] [varchar](20) NULL,
 [SimpleCalendarQtr] [char](5) NULL,
 [SimpleFiscalQtr] [char](5) NULL,
 [SimpleFiscalYear] [char](7) NULL,
 [SimpleCalendarYear] [char](7) NULL,
 [CalendarQuarterDesc] [char](10) NULL,
 [FiscalQuarterDesc] [char](10) NULL,
 [CalendarSemesterDesc] [char](10) NULL,
 [FiscalSemesterDesc] [char](10) NULL,
 [SimpleFiscalPeriod] [varchar](20) NULL,
 [SimpleFiscalPeriodNo] [int] NULL,
 [FiscalPeriodLabel] [nchar](2) NULL,

) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[rds].[Date_dim]') AND name = N'PK_rds_Date_dim_DateKey')
DROP INDEX [PK_rds_Date_dim_DateKey] ON [rds].[Date_dim] WITH ( ONLINE = OFF )
GO

CREATE UNIQUE CLUSTERED INDEX [PK_rds_Date_dim_DateKey] ON [rds].[Date_dim] 
(
 [DateKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

The following section, adds the core date data, Typically, this is added one year at a time.
But, it can be extended to insert as many years as needed.

/* -----------------------------------------------------
   -----------------------------------------------------
   
 Process to add records to the rds.Date_dim
 In production, dates are added one year at a time. 
    Alternatively, you could front load the table with 
    20-30 years into future.

 @intLOOPend= 370     -- added as simple infinite loop break, 
             
 @dtStartDate = 'jan 1, 2014'  -- calendar year start
 @dtEndDate = 'december 31, 2014'; -- calendar year end

 Fiscal dates vary, so they are not included
   -----------------------------------------------------  */

set nocount on;
SET DATEFIRST 7;  -- 7=default=sunday, monday=1
go


declare @intLOOPcnt int, @intLOOPend int , @dtStartDate datetime, @dtEndDate datetime, @dtProcessDate datetime;

-- KEY SETTINGS / INPUTS
Select @intLOOPcnt = 0,@intLOOPend= 4000
, @dtStartDate = 'Jan 1, 2000', @dtEndDate = 'December 31, 2004';

-- init date to be processed
select @dtProcessDate = @dtStartDate;

while @intLOOPcnt < @intLOOPend AND @dtProcessDate <= @dtEndDate
  begin

   /*debug*/ --print 'test' + cast(@dtProcessDate as varchar)
  BEGIN TRY
    -- insert 
        
  insert into rds.Date_dim
   ( dateKey, fullDateAltKey, dayOfWeekNum, DayOfWeekName, dayOfMonthNum, dayOfYearNum
   , weekOfYearNum
   , MonthOfYearName, monthOfYearNum
   , calendarQuarter, calendarYear, calendarSemester
   --, fiscalYear, FiscalDayOfYearNum,FiscalPeriodLabel
   --, fiscalQuarter, fiscalSemester
   )

  select
    dateKey   = cast(CONVERT( varchar(8),  @dtProcessDate , 112) as int)
   , fullDateAltKey = CONVERT( varchar(8),  @dtProcessDate , 112)
   , dayOfWeekNum  = datepart(weekday,@dtProcessDate) 
   , DayOfWeekName  = datename(weekday,@dtProcessDate)
   , dayOfMonthNum  = datepart(day,@dtProcessDate)
   , dayOfYearNum  = datepart(dayofyear,@dtProcessDate)
   , weekOfYearNum  = datepart(week,@dtProcessDate)
   , MonthOfYearName = datename(month,@dtProcessDate)
   , monthOfYearNum = datepart(month,@dtProcessDate)
   , calendarQuarter = datepart(quarter,@dtProcessDate)
   , calendarYear  = datepart(year,@dtProcessDate)
   , calendarSemester  = case when datepart(quarter,@dtProcessDate) in (1,2) then 1 else 2 end 
   --, fiscalYear        = datepart(year,@dtProcessDate)
   --, FiscalDayOfYearNum = datepart(dayofyear,@dtProcessDate) 
   --, FiscalPeriodLabel  = '  '
   ;

  END TRY
  BEGIN CATCH

   print 'CATCH - error: ' + cast(@@error as varchar) + ',  ProcessDate:  '+ cast(@dtProcessDate as varchar);
  END CATCH
      
  -- next item
  select @intLOOPcnt = @intLOOPcnt + 1;
  select @dtProcessDate = dateadd(day,1,@dtProcessDate );

  end
go

This final section simply updates the few remaining values. To keep the build script simple, I separated this out of the main insert. You could include it, but this is run infrequently, and simplicity is more important than having an impressive, but complex insert statement.

-------------------------------------------------------------------------------------------------------------------------
-- OTHER CALENDAR VALUES FOR UPDATE 
-------------------------------------------------------------------------------------------------------------------------
     
update d set 
    -- select top 50 
  SimpleCalendarDate = MonthOfYearName + ' ' + CAST(DayOfMonthNum as varchar) + ', ' + cast(CalendarYear as varchar)
 ,SimpleCalendarMonth = MonthOfYearName + ' ' + cast(CalendarYear as varchar)
 ,SimpleCalendarQtr = 'CY Q' + CAST(CalendarQuarter as CHAR(1))
 ,SimpleCalendarYear = 'CY ' +  cast(CalendarYear as varchar)
 ,CalendarQuarterDesc = 'Q' + CAST(CalendarQuarter as CHAR(1)) + ' CY ' + cast(CalendarYear as varchar)
 ,CalendarSemesterDesc = 'H' + cast(CalendarSemester as CHAR(1)) + ' CY ' + cast(CalendarYear as varchar)

    from rds.date_dim as d 
 WHERE d.DateKey between 20050101 and 20051231;

Data Linage - Credibility for ETL History

Lack of credibility is perhaps the biggest problem with data warehouses. So now I'm noticing some tools to help people trace data in a data warehouse back to the source. Where it came from, how it was changed, etc.  - all this is after-the-fact. Perhaps valuable, but a well designed ETL process already includes this type of information. A result from the premise that the data in a data warehouse is 100% accurate. It rarely has been, but it should be close.

Big data, Hadoop, web searches, and much of the on-going world of statistics are approximations. Best guesses  -  that can change once you make an adjustment to the incoming data or the rules used for the query.

SSIS - Add row number to incoming source records

Credibility is critical to the success of a business intelligence / data warehouse project. Problems happen, and sometimes the problem is not the SSIS ETL process, but the incoming data. A business manager questions the validity of a number, and the BI team has to trace back to the source. And not just to the source file, but to the actual source record.

It's OK to tell someone you'll need to review the source file. It's better if you can tell someone that the data came from row # from file #, and then pull up the data quickly and easily. To do this, you need to know the actual source row number. SSIS makes this easy. 

Summary

When you are importing data from a file into your target database work table all you need to do is follow these simple steps.

• Package variable:  
• add a variable to capture the row count
• In your Data Flow:
• Add a Row Count task
• Add a Script Task, with an output column
• Add the new column to your destination export

This example was from a project done with SQL Server 2008, but applies to all the current versions.

The Details

Here is a more complete listing of the steps required in SSIS to add a row number to all of your incoming file records. When you are done, your data flow might look something like this:

Package Variable

As part of the package, create a user variable for the record count. For our example, we used User::RecordCntFile, Scope = Package, DataType = Int32, Value =0.  Naturally, if your maximum row count exceeds the capacity of the Int32 container, use Int64.

Row Count Task

Create and then name the Row Count task. We named ours "Row Count."  Using the advanced editor for the Row Count task, make the following change.

• Component Properties: 
• In the Custom Properties section, assign your user variable to the custom property - VariableName.
•  Input Columns:  
• no adjustment or changes for this section are required.
•  Input and Output Properties:  
• no adjustment or changes for this section are required.

Assign user variable in the custom properties

  

Script Task

Add a script task to your data flow and give it a name. Using the Edit Script button, add the following code. The below code happens to be in Visual Basic, but could easily be in C#.

Script Code:

       

Public Class ScriptMain
   Inherits UserComponent
   Private _RowNumber As Integer = 1

   Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
       Row.RowNumber = _RowNumber
         _RowNumber += 1
       End Sub

   End Class

       
 

• Input columns:  
• no adjustment or changes for this section are required.
• Inputs and Outputs:  
• Add a new output column, here named RowNumber, with a datatype of DT_I4 (four byte signed integer) to match our Int32 variable datatype.  If your variable is something other than Int32, change this as necessary.
• Connection Manager:  
• no adjustment or changes for this section are required.

New Output column:  RowNumber

Destination Task

The last step is to include the new column you added in your script task as part of the mappings in your destination task. With this, your data warehouse tables will contain the original source file row number.  And give you a big win the next time someone has to locate and validate the original source data.

Time of Day dimension

There are times when you need a time dimension. Web and operational data is a common use. Over the last month, what were the web statistics by hour of the day. What time of day do we get the most orders? Again, a time of day question.

So, we need a time of day dimension, along with some logic to capture the time of day. I've divided the creation and population of the time of day dimension into two parts. The first part creates and populates a simple five column time dimension. The later part provides several possible enhancements. Every data warehouse is different, so it makes it easier to first build out the core time of day dimension first, and then provides ways to extended it, so that you can extend it the way you need it.

Surrogate Key

While most dimensions are best served with a surrogate key, date and time dimensions are best when the key defines the value. Date and time dimensions never change, with no expectation of change. With absolutely no change, there is no need to use a surrogate key.  Our surrogate key is effectively an integer based compound key derived from the hour, the minute and the second. For example:

timeOfDayKey	fullTimeAltKey	hour	minute	second
0	000000	0	0	0
24639	024639	2	46	39
101324	101324	10	13	24

Data Dictionary

Column	Defintion
timeOfDayKey	Table key, and surrogate key
fullTimeAltKey	Character based time key, will all leading zeros
hour	24 hour clock hour in the day
minute	Minute for the hour
second	Second for the minute in the hour

Table Create

       
  /*  -----------------------------------------------------------
       create the initial table
    
         ----------------------------------------------------------- */
         SET ANSI_NULLS ON
         SET QUOTED_IDENTIFIER ON
         SET ANSI_PADDING ON
         GO
  
         CREATE TABLE [dbo].[dimTimeOfDay](
          timeOfDayKey int not null
         ,fullTimeAltKey char(6)
         ,hour tinyint
         ,minute tinyint
         ,second tinyint
          );
    
      alter table [dimTimeOfDay]
        add constraint PK_dimTimeOfDay PRIMARY KEY  ( timeOfDayKey);
   
      create index idx_dimTimeOfDay ON dbo.dimTimeOfDay (fullTimeAltKey)
        include ( hour, minute, second);


       
 

Initial Data Population

       
/*  -----------------------------------------------------------
        Populate the table
        # Rows:  86400
        ----------------------------------------------------------- */
       
  set nocount on
  declare @dtProcessDate datetime, @dtEndDate datetime, @intCounter int
         ,@intprocessDate int , @strFullTimeAltKey char(6),@intHour tinyint
         ,@intMinute tinyint, @intSecond tinyint
 
  set @dtprocessDate =  dateadd(dd, datediff(dd,0,getdate()),0); 
  set           @intCounter = 0
  select @dtEndDate = dateadd(dd,1,@dtProcessDate)
  select @dtprocessDate as 'process date', @dtEndDate as 'end date'
 
 while @dtProcessDate < @dtEndDate and @intCounter < 100000
   begin
   --print '------------------------------------------------------------'
   -- debug select @intCounter as 'Counter', @dtprocessDate as 'process date', @dtEndDate as 'end date'
 
   -- time parts, w/ explicit conversion from int to tinyint
 select  @intHour     = cast(datepart(hh, @dtProcessDate)  as tinyint)
         ,@intMinute       = cast(datepart(mi, @dtProcessDate)  as tinyint)
         ,@intSecond       = cast(datepart(ss, @dtProcessDate)  as tinyint)
 
  select @strFullTimeAltKey =  
            right( '00' + cast(@intHour   as varchar),2)
          + right( '00' + cast(@intMinute as varchar),2)
          + right( '00' + cast(@intSecond as varchar),2)
 
  select @intprocessDate = cast(@strFullTimeAltKey as int)
 
 -- populate table
 
  insert into dbo.dimTimeOfDay(timeOfDayKey,fullTimeAltKey,hour, minute, second )
  values ( @intprocessDate, @strFullTimeAltKey, @intHour, @intMinute, @intSecond )
   
  -- bump date
  select @dtProcessDate = dateadd(second,1,@dtProcessDate)
  select @intCounter = @intCounter + 1
 
  end

       
 

Possible Enhancements

For presentation purposes, we need something better than these five values. For this post, we have listed out six enhancements. Pick and choose as needed, or add your own.

Presentation Enhancements to the Data Dictionary

Column	Definition
TimeOfDayNameFullMil	00:00:00;  military format, time presentation, including seconds
TimeOfDayNameShtMil	00:00;  military format, time presentation, hour and minute only
TimeOfDayNameFullStd	12:00:00 am/pm;  standard format, time presentation, including seconds
TimeOfDayNameShtStd	12:00 am/pm;  standard format, time presentation, hour and minute only
HourNameStd	12 am/pm; hour only name
MinuteName	00:00 minute with seconds

Table Alter to add Enhancements

       

  alter table dimTimeOfDay
  add TimeOfDayNameFullMil char(8)
  
  alter table dimTimeOfDay
  add TimeOfDayNameShtMil char(5)
  
  alter table dimTimeOfDay
  add TimeOfDayNameFullStd char(11)
  
  alter table dimTimeOfDay
  add TimeOfDayNameShtStd char(8)
  
  alter table dimTimeOfDay
  add HourNameStd char(5)
  
  alter table dimTimeOfDay
  add MinuteName char(5)


       
 

Populate Enhanced columns

       

update dbo.dimTimeOfDay
set

 TimeOfDayNameFullMil = right('00' + cast(hour as varchar),2) + ':' + right('00'    + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2)

,TimeOfDayNameShtMil = right('00' + cast(hour as varchar),2) + ':'
  + right('00' + cast(minute as varchar),2)                      

,TimeOfDayNameFullStd = case 
  when hour = 0 then '12' + ':' + right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2)  + ' am' 
  when hour >= 1 and hour <=11 then cast(hour as varchar) + ':' + right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as     varchar),2)  +' am'
  when hour = 12 then '12' + ':' + right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2)  + ' pm'
  when hour >= 13 then cast((hour-12)as varchar) + ':'          + right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2)  + ' pm'
     else '?' end

,TimeOfDayNameShtStd = case 
 when hour = 0 then '12' + ':'  + right('00' + cast(minute as varchar),2)  + ' am'
 when hour >= 1 and hour <=11 then cast(hour as varchar) +  ':'  + right('00' + cast(minute as varchar),2) + ' am'
 when hour = 12 then '12' +  ':'          + right('00' + cast(minute as varchar),2)   + ' pm'
 when hour >= 13 then cast((hour-12)as varchar) + ':'  + right('00' + cast(minute as varchar),2) +  ' pm'
    else '?' end

,HourNameStd = case 
 when hour = 0 then '12 am'
 when hour >= 1 and hour <=11 then cast(hour as varchar) + ' am'
 when hour = 12 then '12 pm'
 when hour >= 13 then cast((hour-12)as varchar) + ' pm'
    else '?' end

,MinuteName = right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2);

       
 

Power BI Desktop - upgrade

On September 23, 2015, Microsoft released the awaited upgrade to the Power BI Desktop tool.
With the December 21, 2015 update, the added a Beta connector to R.  You can find out more at Visualizing and operationalizing R data in Power BI.

What is amazing, is that there is a very long list of enhancements to the desktop tool. And, especially so, since Desktop is a free tool. Has Microsoft lost it? Or perhaps have they finally decided to once again try to take the lead in BI.

A hidden gem is support to connect to an on-premises SSAS Multi-dimensional database. In earlier versions, only Tabular was supported. And the Navigator connection tool only needs to know the name of the server and the authentication user/pw.


Power BI 2.0 desktop September, 2015 update to read about the upgrade in detail.
Power BI 2.0 desktop December 21, 2015 update to read about the upgrade in detail.

 Power BI Get Data Navigator listing the SSAS Multi-dimensional databases

 

Power BI Relationships Tab - displaying the imported Multi-dimensional table

 

Power BI Waterfall displaying the imported Multi-dimensional table

Power BI Updates for September 22, 2015

The most recent Power BI weekly updates ( Sept 22, 2015) added some much needed improvements to the available list of tile sizes. Until this release, there were only a small number of sizes available.

While the introduction of Power BI 2.0 has been well received, the limited tile sizing was a presentation drawback, This release greatly expands tile sizing flexibility that will give report designers a greater ability to deliver the reports being requested.

In this weeks release, it was also suggest that an update to Power BI Desktop will be release very soon.  For more details go to the Power BI Weekly Service Update report.

Power BI: Comparing the Waterfall, Line and Column Charts

The Waterfall visualization (often called a progressive bar chart) combines a column chart with a line chart. In effect, it lays out the incremental data as individual columns that add incrementally to the total. Visually, each record provides you with a proportionately sized column that is added to the total over time. It is a great visual for showing unit and sales growth.

For our example below, we used the monthly gross profit numbers for the Adventure Works sample database. Using a waterfall chart, the value for each month shows up like a step, sized according to the impact it has on the total. Showing sales, gross profit, or any other incremental item allows you to see not only the overall trend, but also the individual contribution by month. It is a really nice way to display these types of trends.

These three graphs display the same data to compare the Waterfall Chart with the Column Chart and the Line Chart.

Waterfall Chart

Line Chart

Column Chart

You can visit the Power BI tutorial at support.powerbi.com.

Data type Changes when loading Power BI Data

During the data loading process, Power BI Desktop evaluates the incoming data, and then assigns what it thinks is the correct data type. And in most cases, it makes a good choice. But what if it makes the wrong choice? 

For example, what if one of the columns is a text field made up of the month and the year? Such as December, 2015. Power BI Desktop decides that this is a data field, and changes the value to 12/1/2015. Great if that is what you want.

Editing the data types

During the initial load process, one of the first screens will be the Navigator. On the bottom right, select/click on the Edit button.

This will bring up the Query Editor. The Query Editor is one of the most powerful tools available to the Power BI Desktop, Over time, you'll find this tool more and more useful. On the right side of the Query Editor, locate the Applied Steps section. For this example, the last step the query editor performed was the Change Type.

Simply right click on the Change Type item, and select Delete. Or, simply select/click on the X immediately to the left of the Change Type item. Once the change has been made, the Sales Month column is now back to the value we wanted.

While we are in the Query Editor, lets update the data type for the monetary columns. Holding the Ctrl button, select/click on the monetary columns. On the Data Type combo box, select the Fixed Decimal Number value. 

For our example, the incoming column in Excel was configured as Currency. Once we change the Data Type to Fixed Decimal Number, Power BI recognizes the columns as Currency.

  

When you are finished, select/click the Close & Load button. After loading the data, you can review using the Data Tools. The currency columns are now presented as currency values.

Power BI 2.0 - Desktop or Online Portal for File Loads

This article continues in our exploration on how users can benefit from the free version of Power BI.  Perhaps as a way to demonstrate how Power BI can benefit your organization and to justify an upgrade to the Pro version. Or, you just need to make the most of a free tool. This post focuses on the single table limitation placed on reports built using the Online Portal Report Builder.

Analysts spend endless hours creating and maintaining Excel files to build custom reports for management. And as good as Power BI is, that will not change soon. Excel is an excellent tool to explore and manipulate the data. If an analyst can imagine a way to manipulate data, it almost always can be done in Excel. This free-form ability of Excel works as a great compliment to the free version of Power BI.

Getting started - which tool:  Power BI Desktop or the Online Portal Builder

Since data sources for the free tool are limited, the main determinate is:  can you get the final data set down to a single table? ( see Is the Free version of Power BI 2.0Worth Using?)

        Multiple Data Files as a source?

	Power BI Desktop	Online Portal Report Builder
Single Excel File	Yes	Yes
Multiple Excel Files	Yes	No

Using the free Online Portal Report Builder for Power BI, the design framework expects, and really only supports a single two-dimensional Excel table. Conversely, the Power BI Desktop allows you to work with data from multiple local databases and file sets.  If you can use all the tools in Excel to build out to a single sheet (table) in Excel, the Online Portal is fine. If not, consider the Power BI Desktop.

Viewing the data?

Using the Power BI Desktop, once data has been loaded into the Online Portal, as of Sept 16, 2105, there were no tools available to review the data you just uploaded. If you are the type that needs to see the data, then the Online Portal will be a bit of a hassle. Fortunately, if you use the Power BI Desktop, you can not only have multiple data sources, you can view, and review the data. Including tools to filter the data.

Using Excel Data: Format as Table

Using the Online Portal to import an Excel file from your OneDrive, Power BI looks for a sheet where the style has been setup using Format as Table.  Desktop does not have this limitation. You can have additional supporting pages, with sheet references. You just need the primary/final sheet to be setup as a table using the Format as Table command. On the Home tab of the Excel ribbon bar, before you can use your Excel file in Power BI, you will need to update the "style" of file to a "table."

Over time, you'll find that while the Online Portal is quick, the Desktop is the best way to build out reports for Power BI. 

Is the Free version of Power BI 2.0 Worth Using?

There has been a lot of excitement following the general release of Power BI 2.0 earlier this summer. And part of that focus has been on the entry level price - Free. But free has its limits, and Microsoft is not known for distributing free software, let alone robust free software.

One big advantage of software with a free version is that it allows analysts and staff to work with the application, and ideally, use it while they try to convince their manager to pay for a full version. And Power BI 2.0 is no different. So the question might be, is the free version sufficiently robust that I can do real work with the tool. Can I deploy reports that my boss and others can use. And once I have a report built using the free version, just how easy will it be for me to update the data if I don't have the Pro version?

The table below outlines the current differences between the free version and the Pro version (as of Sept 16, 2015).

	FREE	$9.99 user / month
	POWER BI 2.0	POWER BI PRO 2.0
Data capacity limit	1 GB/user	10 GB/user
Create, view and share your personal dashboards and reports	•	•
Author content with the Power BI Desktop	•	•
Explore data with Natural Language1	•	•
Access your dashboards on mobile devices using native apps for iOS, Windows, and Android	•	•
Consume curated content packs for services like Dynamics, Salesforce, and Google Analytics	•	•
Import data and reports from Excel, CSV and Power BI Desktop files	•	•
Data Refresh
Consume content that is scheduled to refresh	Daily	Hourly
Consume streaming data in your dashboards and reports	10K rows/hour	1M rows/hour
Consume live data sources with full interactivity		•
Access on-premises data using the Data Connectivity Gateways (Personal and Data Management)		•
Collaboration
Collaborate with your team using Office 365 Groups in Power BI		•
Create, publish and view organizational content packs		•
Manage access control and sharing through Active Directory groups2		•
Shared data queries through the Data Catalog		•

Source:  Microsoft (as of Sept 16, 2015)

With the free version, all of the core functionality is available.  And perhaps the best free option is the full featured Power BI Desktop. There is only one version, used by both the free and Pro versions. It allows you to pull data from a nearly complete list of file, database, Azure and other cloud and remote data sources. Unfortunately, the free version of the Online Portal greatly limits your access to data.

Since Power BI Desktop is the same for both the free and Pro version, lets take a look at this tool. You can easily obtain data from multiple sources, and importantly, there is an extensive list of built-in tools to filter the data both vertically (by removing columns) and horizontally (by filtering out rows). The relationship editor allows you to review and adjust how the data tables are joined. You can use the automated matching logic, or make a change to fit your needs. So, while the Online Portal is restricted, for the free tool user, Desktop gives you a lot of flexibility and power.

Data refresh is another area that separates the free version from the Pro version.  The free version only supports a limited number of sources that allow a simple data refresh.  Files in your OneDrive can be used to refresh the free version, but only a manual refresh. 

Another data source available to the free version is the external data service provider. Power BI has a long list of supported sources such as Google Analytics and Salesforce. To see if the one you need is supported this will be a good place to start. Unfortunately, if your data comes from an Azure SQL database, or from an on-premises database your free version options are limited. You'll need to first open the report in Power BI Desktop, refresh the data on your personal workstation, and then upload the full Power BI report file to your Power BI Portal. If your source is only a file, you build your Power BI report using the limited online tools, and upload a new file. So, for initial trials and low budget operations it is very doable. Longer term, you'll want to find a way to move to the relatively inexpensive Pro version.

One limitation with both the free and Pro versions of Power BI 2.0 is where you can publish the final report. With both there is only one place to publish, and that is at the Power BI cloud portal. If you are already making the move to Office 365 you are all set. If not, then management may or may not be ready for cloud based reports. However, this limitation is changing. Pyramid Analytics is introducing a platform where Power BI Desktop reports can be published. ( Power BI Desktop to Pyramid Analytics ). On October 29, 2015, the Microsoft team reported suggested that there will be additional ways/locations to publish Power BI reports, That would be excellent.

So, if you are looking for a great BI tool, and have the ability to publish to the cloud, then Power BI is worth taking a look at for your BI needs. Even if management never approves the monthly cost of Pro, your end users can still access your reports. 

A few months ago, I published an article on the newly released Power BI 2.0 at Redmond Magazine. Called First Look: Power BI 2.0.  If you would like to get started with the new Power BI 2.0, the best place to start is on the Getting Started Page,

Connect PolyBase to your Hive database Table: SQL Server 2016

A straight forward way to connect PolyBase to your Hadoop text-delimited Hive tables is to use the DELIMITEDTEXT format method (non-ORC files). The only difference is that with Hive tables, the file data has been internally migrated from a simple file format into a Hive table. But, while it is treated as a Hive table, it is still a file within Hadoop. And you can access that file using the PolyBase External Table declaration syntax. (For ORC tables see Connect PolyBase to your Hive ORC Table)

Before you can create an External Table in PolyBase, you first need both an External Data Source, and an appropriate External File format. And like any text-delimited External Tables in PolyBase, you'll need to define how the columns are delimited, the column names and data types, and the physical location of the file on the Hadoop server.

To get started, first navigate to the Hive user interface in Hadoop. For this example, we are using Hortonworks.  With the UI open, select/click on the Tables tab. This will show a listing of the Hive tables available.

HIVE table listing - notice the database name:  xademo.

 

With the Table listing open, locate the table. Our target table is call_detail_records. After locating the table, select/click on it. This brings up the Table Metadata page.

Metadata page shows the column names and datatypes stored in HIVE.

The Metadata page defines how Hive has named the column and their data type. We will need this to create our External Table. On the left, locate and select/click on the View File Location action. This takes you to the folder where the file is located, as well as providing the actual file name stored in the Hive database. 

File Browser - File Folder Location.

In our example, the file and folder name to be used for the External Table is:

  /apps/hive/warehouse/xademo.db/call_detail_records/cdrs.txt

In the file-folder name, notice the folder xademo.db. This represents the Hive database named xademo. The next folder, call_detail_records is identical to the Hive table name. The last value, cdrs.txt, is the file name used within Hadoop on the server. You will also notice that you are now in the File Browser, and not Hive.

Select/click the file (in our case cdrs.txt). This opens the file in viewing mode, so you can discover the file delimiter being used by Hive. In our case, it is a pipe.

Preview of the file data in Hadoop for the file cdrs.txt

For the Pipe delimiter, we'll need to create a new External File format, with this syntax:

CREATE EXTERNAL FILE FORMAT Pipefile

WITH (

       FORMAT_TYPE = DELIMITEDTEXT

              , FORMAT_OPTIONS (

              FIELD_TERMINATOR = '|'                  

                     ,USE_TYPE_DEFAULT =  TRUE ),

              DATA_COMPRESSION = 'org.apache.hadoop.io.compress.DefaultCodec'

       );

Using the column and data type values discovered earlier, we can now create the External Table using this syntax:

       CREATE EXTERNAL TABLE [dbo].HIVE_asfile_cdrs (

              PHONE         varchar(100) NULL ,

              TYPE_1        varchar(40) NULL ,

              IN_OUT        varchar(40) NULL ,

              REC_LOCAL     varchar(40) NULL ,

              DURATION      varchar(40) NULL ,

              REC_DATE      varchar(40) NULL ,

              TIME_         varchar(40) NULL ,

              ROAMING       varchar(40) NULL ,

              AMOUNT        varchar(40) NULL ,

              IN_NETWORK    varchar(40) NULL ,

              IS_PROMO      varchar(40) NULL ,

              TOLL_FREE     varchar(40) NULL ,

              BYTES         varchar(40) NULL ,

              TYPE_2        varchar(40) NULL

             )

           WITH (LOCATION='/apps/hive/warehouse/xademo.db/call_detail_records/cdrs.txt',

              DATA_SOURCE = hdp23 ,

              FILE_FORMAT = Pipefile           

              );

With the External Table created, test the connection by running a simple SELECT. If all goes well, your Hive table is now available via a PolyBase connection.