What is Data Wrangling? Is it same as ETLing?

While I was reading a newsletter received from Hortonworks, I noticed an article related to data, titled with Data Wrangling. With my experience, dozens of business intelligence implementations, though I have worked with hetorogenious data sets, I have never used this term, even when discussing ETLing. Asked few, this is an unknown to many, hence thought to make this post, just discussing how I see the Data Wrangling and how I see it differently comapring well known ETLing.

ETL, Extract, Transform and Loading, is a common technique we use with Data Integration (DI), Data Warehousing and Business Intelligence. This is more on structured data with well know data sources and mostly with automated tools. This extracts data from various, scattered systems, and prepares data as rich-consumable and loads to the destination, specifically data warehouse. Data Wrangling does the same but few differences.

[image taken from: http://www.datawatch.com/what-is-data-wrangling/]

Data Wrangling works more on unorganized, unstructured, large data set rather a set of structured data. This talks about a manual process that coverts data from one raw form to another format which is more readable and organized for analyzing data. As per the articles read, the term was introduced by Trifacta that offers a tool to help on this process. More on this, the person who does this process is called as Data Wrangler.

Read more on this: https://tdwi.org/articles/2015/01/13/introduction-to-data-wrangling.aspx

Analysis Services Tabular Model Workspace Database Retention: Why it is important?

When creating a Tabular Data Model project, it creates a workspace database in Analysis Services Tabular Instance for maintaining all the data and metadata while it is being developed. This database is an in-memory database and with default setting, it gets dropped from the instance when the project is closed. However this default setting can be changed by setting workspace database retention property, adjusting the behavior of the workspace database. That is why it is important.

Be default, workspace retention is set to Unload from memory. When creating a project, it creates a database in the disk (default location of SSAS instance) as well as a database in-memory using the SSAS Tabular instance. Name of the database is created as ProjectName_UserName_GUID. 

Here is the default setting. See the default setting.

Database created in the disk;

Database created in the SSAS Tabular instance;

With this setting, when the project is closed, in-memory database in the instance is removed. But the database in the disk remains and it is used for creating the in-memory database when the project is opening again. Below image shows the instance once the project is close. Note that in-memory database is dropped.

This setting frees up memory allowing system to use memory for other applications but this might slow down re-opening project as it has to build the database again.

If need, you can keep it in the memory without getting it dropped. This is useful if you continuously working with the project. However, be cautious, it does holds the memory consumed without releasing.

If you rarely open the project (usually, after completion of the development), it is better to set workspace retention to Delete workspace as it deletes the database completely. 

Local or remote workspace database?
If possible, developer should use a local instance for the workspace database because it provides the best performance. However remote server can also be used with following limitations;

• Project cannot be created by importing from PowerPivot template.
• No Backup to disk option with Data Backup property.
• Slower performance because of the latency.

Analysis Services Tabular Mode: Counting non-numeric columns using COUNT function

When creating calculated columns or measures in Analysis Services Tabular Mode, we use DAX expression language for adding them. DAX offers number of functions for implementing them and almost all general functions such as SUM, COUNT, AVERAGE are exist too. However some functions behave differently when compare these function behavior with other tools. Here is an example;

Assume that you need to get a count of two columns related to a loaded data table. Let's say we have loaded Internet Sales table from AdventureWorksDW2014, and need to create Count of Promotion Key and Count of Sales Order Number (of course, there is no business logic on these two counts, it is only for demo purposes), then we add two measures like below;

CountSalesOrderNumber:=COUNT([SalesOrderNumber])

CountPromotionKey:=COUNT([ProductKey])

Once added, you will see that CountPromotionKey works fine but CountSalesOrderNumber does not work. The reason for this is, Count function in DAX only works with numeric and dates. For counting non-numeric values, COUNTA has to use instead of COUNT.

CountSalesOrderNumber:=COUNTA([SalesOrderNumber])

Note that both functions do not count blank values. If you want to get the count including blank values, use COUNTROWS function. In addition to these, COUNTX and COUNTAX are given too. They are for getting the count based on a filter.

=COUNTAX(FILTER('Reseller',[Status]="Active"),[Phone])

For getting the count only on blank values, use COUNTBLANK.

SELECT * and SELECT {columns}: Do they always read same number of data pages?

Everyone knows that SELECT * is not a best practice and we always try to avoid it. Some considerable issues related to it are;

• Unnecessary columns will be transferred from server to client consuming resources.
• Application written may face issues when dropping or introducing columns.

However, when reading the table by SQL Server engine, in most cases, no difference in terms of performance. The reason is, it has to read all data pages for getting records regardless of the number of columns referred in the statement. Have a look on following query and output of it.

As you see, both statements require same number of data pages to be read for satisfying the requests. However this is not always true, have a look on this;

Now it is different. The reason for this is, types of data pages used with this table. This table has an xml column and it is maintained not with data pages, with text/image pages. Because of this, when reading columns excluding columns like xml, max type columns, text, images, engine needs to read only data pages, reducing number of IO operations. This introduces the third reason for not using SELECT *, hence, do not use SELECT * when the table has columns that requires different data page types other than data pages.

SQL Server Brain Basher of the Week #036 - SQL Server

SQL Server 2014 introduced In-Memory OLTP tables and many of you know the benefits you get with it. This feature allows you to create table in memory either as durable or non-durable and it is only for tables. Do we create in-memory databases? Can we create in-memory databases? Here is this week question based on it?

Which SQL Server component create in-memory databases that are visible for our implementation?

• SQL Server Engine
• SQL Server Analysis Services
• SQL Server Integration Services
• SQL Server Reporting Services

This question can be answered quickly if you have worked on Business Intelligence solutions. Yes, it is related to Analysis Services but for a specific type only. Analysis Services allows us to create two types of models; Multidimensional and Tabular. When working with a Tabular data model project, as you have experienced, it needs a workspace database which is created in Analysis Services tabular instance. This database is in-memory database that keep all data and metadata for the project while it is being developed. When the project is closed, it removes from the memory by default, however it can be controlled by setting Workspace Retention property.

Analysis Services: How to stop showing records when unrelated dimensions are used with measures?

It is very common to see an Analysis Services database (or a cube) that holds multiple measure groups. If your cube holds multiple measure groups, then you have many number of dimensions and they might not link with all measure groups. In that case, what if a dimension not linked (or unrelated) is used when analyzing a measure in particular measure group?

By default, if an unrelated dimension is used with a measure, dimension is forced to use its top level for showing the value. Example, if Sales Amount from Sales Summary measure group is analyzed with Reseller dimension which is unrelated, result will be as below;

This is controllable. We can stop showing records, when a measure is being analyzed by an unrelated dimension. All we have to do is, set false for a measure group property called IgnoreUnrelatedDimensions.

Once processed, you can test the same and result will be something like below;

Analysis Services: I see a new measure group when a measure is created with Distinct Count

Have you noticed that a new measure group is automatically created if you have created a new measure with Distinct Count usage? Microsoft Analysis Services create this not because of anything else, it is for improving the performance when it comes to specific analysis such as how many sales reps have sold a particular product.

You can create one with Cube Designer -> Cube Structure.

And once they are created, separate measure group are created;

And I will be able to see how many employees have sold a particular product in each year;

Microsoft SQL Server Code Names for versions and features

Microsoft SQL Server team used/uses various code names for SQL Server versions and some of its features, here are some of them found in the web. I tried to match the best image can find in the web, not exactly sure whether it is the right picture, hence please let me know if I have used wrong pictures.

Version/Feature	Code Name
SQL Server 6.0 Enterprise Manager	Starfighter
SQL Server 6.0 Agent	Starfighter II
SQL Server 6.5
SQL Server 7.0	Sphinx
SQL Server 7.0 OLAP Services	Plato
SQL Server 2000 (32-bit)	Shiloh
SQL Server 2000 (64-bit)	Liberty
SQL Server Reporting Services 2008	Rosetta
SQL Server 2005	Yukon
SQL Server Analysis Services 2005	Picasso
Database Design and Query Tools	DaVinci
SQL Server 2005 Mobile Edition	Laguna
SQL Server 2008	Katmai
SQL Report Designer 2.0	Blue
SQL Server 2008 R2	Killimanjaro
SQL Server 2012	Denali
SQL Server Developers (Data) Tools	Juneau
SQL Server Columnstore Index	Appollo
SQL Server 2014 (In-memory OLTP)	Hekaton

How can I see the pages used by my table?

Not always, but there are instances that you need to see the data pages used by SQL Server for holding data in our tables. There are few ways of seeing them, the easiest way is, using two undocumented functions called sys.fn_PhysLocFormatter and sys.fn_PhysLocCracker. These two were initially released with SQL Server 2008 and they are still available. Not sure why they are still marked as undocumented but information throw from them is really useful.

Here is a test code on it;

USE AdventureWorksDW2014;
GO

-- Checking with fn_PhysLocFormatter function.
-- This shows file id, page id and slot id together.
SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS RID, * FROM dbo.FactInternetSales;


-- If you need file, page and slot separately, 
-- fn_PhysLocCracker function can be used.
SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS RID, RID_c.file_id, RID_c.page_id, RID_c.slot_id, * 
FROM dbo.FactInternetSales
 CROSS APPLY sys.fn_PhysLocCracker (%%physloc%%) AS RID_c
ORDER BY RID_c.file_id, RID_c.page_id, RID_c.slot_id;

Changing from FULL to SIMPLE recovery model reduces the log file size?

A concern related to large log file or ldf file still appears in many forums, it is very common to hear/see a questions like my database log file has grown for GBs, why and how can I reduce it, can I just reduce it by changing the recovery model from FULL to SIMPLE.

First thing you need to understand is, how log file truncation works with FULL and SIMPLE recovery model. FULL Recovery model does not truncate the log file until a transaction log backup is performed. But SIMPLE recovery model automatically truncates the log making the log vacant for next transactions rather expanding or growing the file. There are few exceptions in FULL, read my article Log is getting automatically truncated with FULL Recovery Model  for more info.

Now if your database in FULL recovery model and you see a large log file, can you reduce it by just changing the recovery model. Recommendation is not change as you will lose all you transaction records in log file but if you do not want them you can change. However it does NOT reduce the size of the log file. Once changed, it will truncate the file but will not release the free space unless you perform the SHRINKFILE operation for the log file. So the conclusion is, changing the recovery model from FULL to SIMPLE does not reduce the size but truncates. Shrinking file is required for reducing the file size.

Here is a small test for verification.

This code creates a database and a table. The default recovery model is FULL for this database. It backs up the database once. It makes sure that the backup chain is established.

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
GO

CREATE TABLE dbo.TestTable
(
 Id int identity(1,1) primary key
 , Value1 char(4000) not null
 , Value2 char(4000) not null
);

-- take a backup
BACKUP DATABASE [TestDatabase] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'TestDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Here is the current file size.

This code adds records growing both data and log files.

INSERT INTO dbo.TestTable
 (Value1, Value2) VALUES (REPLICATE('a', 4000), REPLICATE('a', 4000));
GO 1000000

As you see, log file has been grown and it is not getting either truncated or shrunk automatically.

Let's change the recovery model and see.

USE master;
GO
ALTER DATABASE TestDatabase SET RECOVERY SIMPLE WITH NO_WAIT;
GO

If you check the size again, you will see the same as it does not release the free space back to OS. Let's shrink the file and see.

USE TestDatabase;
GO
DBCC SHRINKFILE (TestDatabase_log, TRUNCATEONLY);

All as we wanted.

Can I use Standard Edition of SQL Server for implementing my Business Intelligence solution?

I always get this questions from my clients, companies I consult and people who work on BI implementation. Most of the companies initially purchase Standard Edition of SQL Server because of the cost and gradually upgrade to Enterprise. Can we really implement a BI solution with Standard Edition?

Microsoft offers multiple editions of SQL Server, mainly two premium editions and two core editions. Microsoft Analytics Platform System (Parallel Data Warehouse + HDInsight) and Enterprise are premium editions and Business Intelligence and Standard are core editions. If you have purchased Standard Edition, yes you can still implement a BI solution but with many limitations.

Generally, it is not recommended to have Standard Edition for large BI implementations. For managing large volume, you need Enterprise Features like partitioning, data compression and columnstore indexing that are not available with Standard Edition. Therefore, if it is a either small or middle-scale, then Standard Edition can be used.

There less number of functionalities with Standard Edition Integration Services too. Some of main missing features are Persistence Lookup, data mining query transformations and fuzzy lookup and grouping.

It is very important to consider Analysis Services features. As you know, it supports two types of Models; Multidimensional and Tabular. Standard Edition does not support Tabular Model. This forces you to go for multidimensional model though it is bit complex and time-consumed that Tabular. In addition to that, scalable shared databases, synchronizing, perspective, proactive caching and write-back features are not supported with Standard Edition.

Now you know whether you can implement your BI solution with Standard Edition or not. You may consider Business Intelligence Edition if you need more features specifically on Analysis Services end.

See the comparison between editions with: https://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx#SSIS

SQL Server Brain Basher of the Week #035 - Database size

Microsoft SQL Server supports creating small databases as well as very large databases. This week question is based on database sizes, here is the one;

What is the minimum relational database size and maximum relational database size that can be set when creating a database?

When you create a database, system takes a copy of the model database as a template for creating it. The default size of the data file set with model database is 5MB. This stops you for reducing the size of the data file when creating your database. If you try to reduce it for 1MB, this is what you get;

You can reduce the model database data file to minimally 3MB. Once it is set, you can create your database with a data file sized to 3MB.

Based on above tests, we know that the smallest size that can be set with our data files is 3MB (not sure, but there can be some other way to reduce this, will see, however, there is no necessity for having a database like that unless there is a specific and special requirement).

The largest database size support by SQL Server is 524 PB. Almost all editions support creating database with this size, only exception is Express Edition that support only 10GB size databases.

SQL Server Brain Basher of the Week #034 - Analysis Services Security

Security is the most important area that needs to be addressed with many solution and same goes to your Business Intelligence solutions implemented with Microsoft Analysis Services. Typically, all database management systems provide multiple server roles for managing permission, and allow us to extend it either creating custom server roles or database roles. This week question is based Analysis Services server roles;

How many server roles are exist with Analysis Services and what are they?

You do not want to think about this much. Analysis Services offers only one server level role called as Administrators. This cannot be modified and we cannot create any additional roles, but we can add members to this role providing admin rights to anyone required. However system allows us to create additional database level roles which are limited to specific databases.

You can access Administrators role by getting properties of the instance and selecting Security property.

Database roles can be created by accessing Roles node under the database.

Storing datetime with time zone offset and retrieve with required time zone offset

Nothing new but posting as it is something important when working with multiple time zone offsets. If you have to store requests coming from all around the world with the time local to them, sysdatetimeoffset type has to be used with the column and when convert to a different time zone offset, switchoffset function has to be used. Here is an example;

USE tempdb;
GO

-- creating table for holding date and time with time zone offset
CREATE TABLE dbo.TestTable
(
 Id int primary key,
 DateEntered datetimeoffset(0) not null index ix_TestTable clustered
);

-- inserting values
INSERT INTO dbo.TestTable
VALUES
(1, '2015-11-17T02:10:33.430+10:30') -- receiving from Sydney
, (2, '2015-11-17T05:30:21.110+05:30') -- receiving from Sri Lanka
, (3, '2015-11-17T14:24:22.660+00:00') -- receiving from USA

-- Retrieving all dates for Sri Lanka time zone offset
SELECT Id, CONVERT(datetime, SWITCHOFFSET(DateEntered, '+05:30')) DateEntered
FROM dbo.TestTable;

SQL Server Management Studio - October 2015 Release

Have you tried out latest SQL Server Management Studio? It is available as the October release. It can be used with SQL Server 2016 through SQL Server 2005 and supports side-by-side installation.

You can download it from here.

Here are some of the features/functionalities added with this release (from MSDN)

• New modernized lightweight web installer
• New Always Encrypted column encryption wizard 
• New Column Master Key (CMK) rotation dialog
• New stretch database monitor 
• 
  

Power BI: Showing YTD values

Still not all time intelligence functions are working but some of them can be used with DAX. If you have a requirement for showing YTD, QTD and MTD, and they are not available with your either multi-dimensional or tabular model, DAX can be used just like we use in PowerPivot for creating them. However, if you try to use functions like PREVIOUSYEAR, PREVIOUSDAY, it might not work (it did not work for me) as all functionalities related to time intelligence have not been added to Power BI yet.

Here is the way of adding YTD measure to a model created in Power BI extracting data from SQL Server. Once data is loaded, go to data tab (green colored box) and click on new measure (red colored box) button for creating a new measure. Then use the following expression for calculating YTD.

SalesAmountYTD = CALCULATE(SUM(FactInternetSales[SalesAmount]), DATESYTD(DimDate[FullDateAlternateKey]), ALL(DimDate))

Once added, it can be used with desired graphical element;

Can we use user-defined hierarchies in the model with Power BI?

Power BI is becoming richer and richer with its functionalities and will be surly the best in terms of BI client tools. Since it is still being completed, some important functionalities are still missing and sometime it makes us very uncomfortable to use it. One of the key object we build with models is hierarchy, that is very usable when navigating via a set defined with levels using dimension attributes. This navigation is called as Drilling Down. Most common and famous example is, a hierarchy on date dimension that starts from Year, then Semester, Quarter, Month and Date. Power BI does not recognize these user-defined hierarchies defined in models, hence drilling down was initially impossible. I believe that this is being addressed and it will be available in future releases, until that a workaround is given.

If you have installed AsdventureWorks Tabular Model, it has some hierarchies defined. The following shows a hierarchy defined with Date dimension.

And if you connect with this source using live connection;

And expand date dimension, you will not see the hierarchy.

However, latest version of Power BI allows you to configure a hierarchy within the chart. All you have to do is, drag and drop attributes to Axis box, forming a hierarchy. The following shows how it looks like when it is added.

Now drilling-down is possible with the chart. It is not enabled by default, the icon in the right-top has to be used for enabling it. Once enabled, you can click on the bar, example, bar that represents 2013 and go into semesters related to it. For drilling-up, the icon in the left-top has to be used.

This works fine without any issue and this is the way of implementing drilling-down until we get a version that supports user-defined hierarchies in models.

Understanding Reporting and Analysis in Business Intelligence solutions

Every company implements a business intelligence solution for tracking and improving the business performance through reporting and analysis, setting it as the ultimate goal of business intelligence solution. Some solutions completely focus on analysis only and some focus on reporting only but most focus on both, making sure that solution delivered addresses requirements coming of all level of the company. What is the different between Reporting and Analysis with BI?

Reporting

This is the main communication channel used for getting information from BI solution. Formal reports or operational reports are not recommended through BI solution but production reports, interactive reports (up to some extent) and dashboard reports are common. Most reports show summarized values and they are printable. It is not uncommon to see tabular reports but reports with graphical elements are the most common. When planning, audience needs to be understood, need to have a good knowledge on how reports need to be viewed and delivered. There are mainly two types of reports that can be delivered through a BI solution;

• IT-provided reports
  
  Reports that are developed and managed by developers are called as IT-provided reports. These reports are developed based on requirements given by business users. Business users have no facilities to modify reports but content of the reports can be changed using filters added or using interacting features such as expanding/collapsing enabled in the report.
• Self-service reports
  Self-service reports are authored by business users without getting support from IT. Special tools that are more user-friendly for business users have to be given, empowering business users. Tools should support authoring simple reports such as production report as well as complex reports such as dashboard reports. Though IT has no involvement with this, repository that holds data required with business terms has to be supplied by IT (example, data warehouse, or models) and sometime, IT manages developed reports too.

Analysis

Analysis is detail examination or interpretation of data delivered by BI solution. Smart business users explorer data in the model and perform specific examinations for seeing the insight or find a hidden pattern. Example, these business users will use tools like Excel, Power BI, Tableau for creating their own models or doing their own experiments. General users do the same but not with specialized tool but with reports and dashboards. Generally, following analytical requirements are addressed with BI solutions;

• Interactive analysis
  
  This type of analysis requires key BI functionality such as slicing and dicing for analyzing data with various segments or dimensions. Results of these analysis can be published as reports or can be used for improving business process such as promotions.
• Dashboard and scorecards
  Combination of various KPIs and summarized information with graphical widgets are shown with dashboards and scorecards, allowing users to analyze data with functionalities like drilling down and drilling through.
• Data mining (machine learning)
  This allows users to do various experiments with data using machine learning algorithm for understanding the trends and patterns related business processes and using them for improving the business.

Trying to connect on-prem Analysis Services using Power BI Analysis Services Connector?

Although Power BI Pro allows us to connect with On-Prem Analysis Services Tabular instances for consuming live data into dashboards and report, there are limitations on it. If you try to configure Power BI Connector with On-Prem Analysis Services Tabular instance using an effective account (such as onmicrosoft.com account) without an Active Directory configured domain or using an active directory account without syncing it with Azure Active Directory, you will not be able to configure. Here is what I get when trying to configure Power BI Analysis Services Connector with my Tabular Instance installed in my standalone laptop.

All Prerequisites are listed at https://powerbi.microsoft.com/en-us/documentation/powerbi-analysis-services-connector/.

Here are important things to be remembered;

• Only SQL Server Analysis Services Tabular models are supported for Power BI.
• The connector has to continue running and the computer where it is installed has to remain on.
• Power BI Analysis Services Connector and the Data Management Gateway on the same computer cannot be co-exist.
• The Analysis Services server is domain joined.
• If you use a .onmicrosoft.com email address, you'll need to sync your Active Directory to Azure Active Directory using Azure Active Directory Sync (DirSync). T