Articles

Power BI Visual Contest

Power BI conducted a best visuals contest (http://community.powerbi.com/t5/Best-Visual-Contest/con-p/best_visual_contest) during the month of September.  There were around thirty entries in the contest.  It was impressive to say the least.

Winners were announced on October 8th, 2015 and included three people’s choice awards, a third place winner, a second place winner, and a grand prize winner:

1st People’s Choice Award

Bullet Chart by SQLBI

Code: https://github.com/danieleperilli/PowerBI-visuals

2nd People’s Choice Award

Power BI Aquarium

 

Code: https://github.com/enlightendesigns/PowerBI-visuals

3rd People’s Choice Award

Breakdown Trees

The Breakdown tree makes it possible to visually display the full drill-down path of a measure. By keeping all levels in the drill-down path visible you will get a good overview of how your numbers break down, from the top level to the details at the bottom.

Code: https://github.com/fredrikheden/PowerBI-visuals

3rd Place Award

Hexbin Scatterplot

The Hexbin Scatterplot for Power BI is a variation on a traditional scatterplot that involves clustering points onto a uniform grid of hexagons. Rather than relying on size to indicate differences in values like a bubble chart, the hexbin plot instead uses variation in bin color similar to a heat map. A higher saturation or darker color indicates higher density when working with two measures. A third measure can be added so that the plot colors change from indicating point density to instead encoding the value of the third measure. Bin radius can also be adjusted.

Code: https://github.com/deldersveld/PowerBI-visuals

2nd Place Award

KPI indicator with status, deviation and history*.

This visualization is all about visualizing Key Performance Indicators. The status is presented as a color indication, comparing the actual and target values. Deviation is presented as distance in percent of actual from target. The history (trend) is presented as a line or a bar chart. It is up to the user to decide the granularity of the data  displayed. Any dimension attributes can be used, but it’s recommended to stick to the ones in your date dimension.

Code: https://github.com/fredrikheden/PowerBI-visuals 

Grand Prize Award

Synoptic Panel by SQLBI

The Synoptic Panel connects areas in a picture with attributes in the data model, coloring each area with a state (red/yellow/green) or with a saturation of a color related to the value of a measure. Starting from any image, you draw custom areas using http://synoptic.design/, which generates a JSON file you import in the Synoptic Panel. You can visualize data over a map, a planimetry, a diagram, a flow chart.

 

Code: https://github.com/danieleperilli/PowerBI-visuals

Opening the application API and allowing customization is one of the features that will push Microsoft ahead of the competition.

See our next article to see how Microsoft utilized these new visualizations!

 

Continue Reading

Power BI Lost Features & Functionality Tracking

With the most recent release of Power BI Desktop / Services and removal of dependencies on Excel, we have lost several features and functionality.  However, the gains and improvements far outweigh the losses.  We gained funnel charts, gauges, donut charts, filled maps, treemaps, waterfall charts, dual axis line and column charts, area charts, custom titles, custom colors, etc.

Really, we need to compare Power BI Services & Desktop with Power Pivot and Power View for both Excel and SharePoint.  I will attempt to list all known limitations of the current iteration of Power BI and tie them to Power BI support improvement suggestions:  https://support.powerbi.com/forums/265200-power-bi

As improvements and updates are made to the Power BI Desktop and Services, we will update and try to keep track of the lost features as we gain them back in future releases.

Below is a table of a comprehensive (hopefully exhaustive) list of Power BI lost features in the current iteration, a short description of the feature/functionality, a link to a site that outlines the feature/functionality, and a link to the improvement suggestion on the Power BI site (if applicable).  We will likely enter in suggestions for those that do not have one (n/a below).

Continue Reading

How to Profile SQL Server Data Using Power BI

With the announcement of General Availability for Power BI 2.0, Microsoft made BI to the masses possible. There were some steps back (chart/map drill down, tile slicers, etc.) but many more steps forward. They removed the reliance on Excel, added formatting options, new visualizations, and more.

With the new release, we wanted to create a dashboard so we started with SQL Server data profiling.  This helps in analyzing your data source for optimizing the data model.  What columns have high or low cardinality?  What is the uncompressed data size?  What are the data types of high cardinality columns?  This helps in analyzing the data to see if data types need to be modified, if columns need to be split, if tables are at a lower granularity than needed, etc.

Here is the sample dashboard created on top of the data model using the AdventureWorks Data Warehouse database AdventureWorksDW2012:

Power BI SQL Metadata

We included the SQL Server logo (this same method with altered SQL could be used for Oracle, Teradata, etc.), the number of schemas for the database, the number of tables, the number of columns, the size in MB for each table, the number of distinct elements and corresponding size by column, the number of distinct elements by table and column in a heat map, and a data grid with corresponding information.

We created a stored procedure to extract this data but it could also be passed in as a query to the SQL Server database.

Download SQL Here

declare @results table
(
ID varchar(36),
SchemaName varchar(250),
TableName varchar(250),
ColumnName varchar(250),
DataType varchar(250),
MaxLength varchar(250),
Shortest int,
Longest int,
Minimum varchar(1000),
Maximum varchar(1000),
NullCount int,
NotNullCount int,
DistinctCount int,
SizeMB decimal(12,2),
FullSQL varchar(1500),
FullSQLBit varchar(1500)
)
INSERT INTO @results(ID,SchemaName,TableName,ColumnName,DataType,MaxLength,Shortest,Longest,Minimum,Maximum,NullCount,NotNullCount,DistinctCount,SizeMB,FullSQL,FullSQLBit)
SELECT
NEWID() as [ID],
OBJECT_SCHEMA_NAME(TB.[object_id],DB_ID()) AS [SchemaName],
Object_Name(c.object_id) as [TableName],
c.name as [ColumnName],
t.Name as [DataType],
case
when t.Name not in (‘varchar’,’nvarchar’,’nchar’) Then ‘NA’
when c.max_length = -1 then ‘Max’
else CAST(c.max_length as varchar)
end as [MaxLength],
0 as [Shortest],
0 as [Longest],
0 as [Minimum],
0 as [Maximum],
0 as [NullCount],
0 as [NotNullCount],
0 as [DistinctCount],
0 as [SizeMB],
CASE WHEN t.Name = ‘bit’ THEN NULL ELSE
‘SELECT ”’+t.Name+”’ as [DataType],
Min(Len(‘ + c.name + ‘)) as [Shortest],
Max(Len(‘ + c.name + ‘)) as [Longest],
case when ”’+t.Name+”’ = ”varbinary” then ”NA” else Min(‘ + c.name + ‘) end as [Minimum],
case when ”’+t.Name+”’ = ”varbinary” then ”NA” else Max(‘ + c.name + ‘) end as [Maximum],
SUM( CASE WHEN ‘ + c.name +’ IS NULL THEN 1 ELSE 0 END) as [NullCount],
SUM( CASE WHEN ‘ + c.name + ‘ IS NOT NULL THEN 1 ELSE 0 END) as [NotNullCount],
COUNT(DISTINCT ‘ + c.name + ‘) as [DistinctCount],
SUM(DATALENGTH(‘ + c.name + ‘)) / 1048576.0 as [SizeMB] FROM ‘ + OBJECT_SCHEMA_NAME(c.object_id) + ‘.’ + Object_Name(c.object_id) END as [FullSQL],
CASE WHEN t.Name = ‘bit’ THEN
‘SELECT ”’+t.Name+”’ as [DataType],
Min(Len(‘ + c.name + ‘)) as [Shortest],
Max(Len(‘ + c.name + ‘)) as [Longest],
Min(‘ + c.name + ‘+0) as [Minimum],
Max(‘ + c.name + ‘+0) as [Maximum],
SUM( CASE WHEN ‘ + c.name +’ IS NULL THEN 1 ELSE 0 END) as [NullCount],
SUM( CASE WHEN ‘ + c.name + ‘ IS NOT NULL THEN 1 ELSE 0 END) as [NotNullCount],
COUNT(DISTINCT ‘ + c.name + ‘) as [DistinctCount],
SUM(DATALENGTH(‘ + c.name + ‘)) / 1048576.0 as [SizeMB] FROM ‘ + OBJECT_SCHEMA_NAME(c.object_id) + ‘.’ + Object_Name(c.object_id) ELSE NULL END as [FullSQLBit]
FROM
sys.[tables] AS TB
INNER JOIN sys.[all_columns] C ON TB.[object_id] = C.[object_id] INNER JOIN sys.[types] T ON C.[system_type_id] = T.[system_type_id] AND C.[user_type_id] = T.[user_type_id] WHERE
tb.name not in (‘DatabaseLog’)
–select * from @results order by MaxLength desc
—————————————-COMBINE ALL (MAX,MIN,COUNT) INTO SINGLE SQL
–LONGEST
DECLARE @id varchar(36)
DECLARE @fullsql varchar(1500)
DECLARE @fullsqlbit varchar(1500)
DECLARE @receiver table(
DataType varchar(250),
Short varchar(250),
Long varchar(250),
Minimum varchar(1000),
Maximum varchar(1000),
NullCount varchar(250),
NotNullCount varchar(250),
DistinctCount varchar(250),
SizeMB decimal(12,2)
)
declare @receiverbit table(
DataType varchar(250),
Short varchar(250),
Long varchar(250),
Minimum varchar(1000),
Maximum varchar(1000),
NullCount varchar(250),
NotNullCount varchar(250),
DistinctCount varchar(250),
SizeMB decimal(12,2)
)
DECLARE length_cursor CURSOR
FOR SELECT ID, FullSQL, FullSQLBit FROM @results –WHERE MaxLength != ‘NA’
OPEN length_cursor
FETCH NEXT FROM length_cursor
INTO @id, @fullsql, @fullsqlbit
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @receiver (DataType,Short,Long,Minimum,Maximum,NullCount,NotNullCount,DistinctCount,SizeMB)
exec(@fullsql)
UPDATE @results
SET Shortest = (SELECT Short FROM @receiver),
Longest = (SELECT Long FROM @receiver),
Minimum = (SELECT Minimum FROM @receiver),
Maximum = (SELECT Maximum FROM @receiver),
NullCount = (SELECT NullCount FROM @receiver),
NotNullCount = (SELECT NotNullCount FROM @receiver),
DistinctCount = (SELECT DistinctCount FROM @receiver),
SizeMB = (SELECT SizeMB FROM @receiver)
WHERE ID = @id
AND DataType <> ‘bit’
DELETE FROM @receiver
INSERT INTO @receiverbit (DataType,Short,Long,Minimum,Maximum,NullCount,NotNullCount,DistinctCount,SizeMB)
exec(@fullsqlbit)
UPDATE @results
SET Shortest = (SELECT Short FROM @receiverbit),
Longest = (SELECT Long FROM @receiverbit),
Minimum = (SELECT Minimum FROM @receiverbit),
Maximum = (SELECT Maximum FROM @receiverbit),
NullCount = (SELECT NullCount FROM @receiverbit),
NotNullCount = (SELECT NotNullCount FROM @receiverbit),
DistinctCount = (SELECT DistinctCount FROM @receiverbit),
SizeMB = (SELECT SizeMB FROM @receiverbit)
WHERE ID = @id
AND DataType = ‘bit’
DELETE FROM @receiverbit
FETCH NEXT FROM length_cursor
INTO @id, @fullsql, @fullsqlbit
END
CLOSE length_cursor
DEALLOCATE length_cursor
SELECT
SchemaName,
TableName,
ColumnName,
DataType,
MaxLength,
Shortest,
Longest,
Minimum,
Maximum,
NullCount,
NotNullCount,
DistinctCount,
SizeMB
FROM
@results

We then renamed columns to make Q&A more user friendly, added measures to their own blank query to delineate dimensions in the main query and measures in the blank query, and hid columns.

At this point, you can create your reports, add text boxes if needed, any images, slicers if needed, and create your visualizations on top of the data.

Once created, we uploaded to our PowerBI.com service online and pin visualizations or images to our newly created ‘SQL Metadata’ dashboard. If you need to keep it up to date because data is changing, download and install the Power BI Personal Gateway application.

Download the .pbix here!

Continue Reading

Twitter Account Created – @powrdax

We now have a Twitter social media presence!

 

@powrdax on TwitterPowerDAX Twitter

@powerdax was not available!  We

Our hope is that we will be able to keep everyone more up to date with Twitter than with articles.  I have such a backlog of articles to share but not very much time to author them.  Twitter will allow us to provide small updates, new and interesting articles, etc. very quickly.

There are so many great people and companies on Twitter and with blogs.  We just hope to bring a different perspective to the market.

Follow us on Twitter and stay updated!

Continue Reading

Power View for Power Pivot / Tabular Data Models

Power View Data ModelPower Pivot or SSAS Tabular data model optimization is important for Power View reporting usability and functionality.

In this posting, we will discuss the following topics:

 

X-Axis Guidelines

Data type and distribution

Utilize a number or date that increases uniformly (primarily for Power BI forecasting but we have seen Power View not normalize the data on the axis due to not being uniform).  Strings are not supported for x-axis values when forecasting.

Power View will typically normalize the date values across the x-axis.  Instead of displaying the date as a date, it will display a partial date (e.g. month year instead of day-month-year).

Advanced Properties

The Power Pivot add-in in Excel allows you to configure advanced properties on the Power Pivot model.  These are configured on the Advanced tab of the Power Pivot model.  This is where you configure:

  • Perspectives (subsets of fields from the model typically defined for particular user groups to improve usability and navigation in large data sets)
  • “Show Implicit Calculated Fields” option (used to show calculated fields that are automatically created when dragging and dropping a field into a Pivot Table that is not an explicitly created calculated field)
  • “Summarize By” option for a column/field (i.e. sum/avg/min/max/etc.).  If you have a field that you do not want to summarize, be sure to set to “Do not summarize” (i.e. Year/Zip or some other numeric column
  • “Reporting Properties” that include the table behavior (details on table behavior below)
  • “Data Category” (ability to define a column/field data type – i.e. geography, image, or URL)
  • “Default Field Set” (set of columns/fields included by default when creating a new table on the Power View canvas and are added automatically when you click on the table.  This is useful if fields need to be included together to make sense to the business user.  The columns are displayed in the defined order but can be changed within each report)

Image Handling

Utilizing images in the Power View report should be done sparingly…and only if it is beneficial to the understanding or usefulness of the visualization / data.  A few things to follow:

  • Column Data Type
    • In order to include the image in the data model itself (JPEG, GIFF or BMP format), the column data type should be binary.
    • In order to save memory, you would include the URL string that points to the location of the image.
  • Data Category
    • Image:  if the column contains the image, which again, must be the Binary column data type
    • Image URL:  if the column contains the URL pointing to the location of the image, which must be the String column data type
  • Default Image
    • See Table Behavior below

Should you utilize an image URL or store the image in memory?  There are pros and cons to each:

Pro Con
In Memory Works w/o Internet
Typically faster		 Consumes Memory
(OK if Not Many Images)
URL Less memory Requires Internet
Connection

If you utilize URL(s), remember to utilize short url(s) if possible (i.e. use a URL shortening tool) as this will utilize less memory.  URL(s) also require the http:// prefix but do not necessarily have to include the www prefix if DNS is configured appropriately.

Data Categories

Data Categories allow a method to add semantic knowledge about the content of a table’s column beyond purely its data type.  For example, one can mark a column’s data type but marking columns as Year or Zip provides a little more description and allows Power View to treat the column appropriately.

Later, in Q&A, the data category helps Power BI choose the proper visual display for the column.  For example, for geographical data categories, Q&A will suggest a map visualization.  For dates, it may suggest a line chart or the play axis of a bubble chart.

Table Behavior

Power View acts like a typical Pivot Table.  It will automatically group what is placed in the report field list and on the visualization you are utilizing.  Typically, this will produce optimal results.  However, there are times when a detail table does not need to be grouped at a higher level (where the individual records are of interest and the data does not need to be summarized at a higher granularity).  In these scenarios, table behavior needs to be configured.

Row Identifier

Specifies a column that contains only unique values, allowing that column to be used as an internal grouping key.  This should be set on the unique column in the table (i.e. the business key).  Setting this enables other properties of table behavior such as “Keep Unique Rows”, “Default Label”, and “Default Image”.  This cCn be expensive if configured for a fact table – see Optimize Tabular / PowerPivot Data Model.

Keep Unique Rows

Specifies which columns provide values that should be treated as unique even if they are duplicates (for example, employee first name and last name, for cases where two or more employees share the same name).  This only works for Power View configuration and not a standard Pivot Table.  You should be mindful of this as it could be misleading to the end users to show the same attribute more than once on a visualization.

Default Label

Specifies which column provides a display name to represent row data (for example, employee name in an employee record).  This property specifies the field to use as a label that appears primarily in a card, or together with the default image in the navigation strip of a tile report. When used with a default image, the default label appears under the image.

Default Image

Specifies which column provides an image that represents the row data (for example, a photo ID in an employee record)…..again, primarily used in tiles and/or cards and is useful especially when there are multiple image columns in the table.

 

Field Behavior

Hierarchies

When including a field from a hierarchy, Power View will include all fields from the hierarchy by default.  If you want to utilize a single field, ensure the hierarchy source fields are not hidden (may be listed under “More Fields”).

Sort By Field

For non linear date fields (i.e. month name / day of week), use the sort by field option to utilize a default sort order and Power View will sort these fields properly.

 

Conclusion

That wraps up the Power View for Power Pivot / Tabular data model best practices.  You can also view the Power View for Multidimensional Best Practices.

View other Power View Best Practices!

Continue Reading

Power View Data Model Optimization

Power View Data Model

Before a Power View report can be built, you have to have a data model built as a data source.  There are several configuration options to help optimize your data model specifically for Power View.  These configurations and/or optimizations are made within your Power Pivot model or in SQL Server Data Tools on the SSAS Tabular model.

Power View data model optimization is very important for some visualizations to work correctly. We will discuss several of these optimizations in different articles covering the following topics:

  • Power Pivot / Tabular Models
    • X-Axis Guidelines
    • Advanced Properties
    • Image Handling
    • Data Categories
    • Table Behavior
  • Multidimensional Models
    • Measure Groups
    • Dimensions
    • Unsupported Features
    • Other Considerations
Continue Reading

DAX Best Practices

DAX

DAX Best Practices 

are vital when utilizing large data models and looking to improve performance.  You can get away with poor practices on small models but you will see performance issues as your data grows.

Data Analysis Expressions (DAX)

is the native query and formula language for Microsoft Power Pivot data models and SQL Server Analysis Services Tabular models. DAX includes some of the familiar Excel functions that you have frequently utilized in Excel formulas, but also includes additional functions designed to manipulate relational data as needed. Much is still to be discovered about DAX optimization. What is known is that it is powerful!

DAX expressions are utilized in writing calculated columns, calculated fields (measures), and in DAX queries performed against a Power Pivot or Tabular data model.

We hope you learn and improve your DAX skills by utilizing our list of best practices. Our articles on DAX Best Practices will include the following topics:

Subscribe to our newsletter and receive new articles as they are posted!  We will likely add to this list of topics as well so don’t miss out!

Continue Reading

Power View Best Practices

Power View Best PracticePower View is an interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting. It is a feature of Microsoft Excel 2013, and of Microsoft SharePoint Server 2010 and 2013 Enterprise Edition.

Power View in SharePoint is browser-based Silverlight application, a feature of the SQL Server Reporting Services Add-in for Microsoft SharePoint Server. Creating, opening, and saving Power View reports (RDLX files) all can take place in SharePoint Server.

It lets you quickly drag and drop your way to interactive reports and analytical views in seconds. Each chart and graph in the report is simple to format and work together to allow cross-filtering so you can drill into the insights that matter.

– Microsoft

There are not very many articles on Power View best practices. We will consolidate those that we have found and add what we have discovered in real business situations and on projects as well. This will be over several articles so stay informed and subscribe to receive updates!  Article topics will include:

1. Best Practices for Power View Data Models

3. Best Practices for Power View Development

4. Best Practices for Power View Management

Continue Reading

The Most Important DAX Concept

Data Analysis Expressions (DAX)

is the native query and formula language for Microsoft Power Pivot data models and SQL Server Analysis Services Tabular models. DAX includes some of the familiar Excel functions that you have frequently utilized in Excel formulas, but also includes additional functions designed to manipulate relational data as needed. Much is still to be discovered about DAX optimization. What is known is that it is powerful!

DAX expressions are utilized in writing calculated columns, calculated fields (measures), and in DAX queries performed against a Power Pivot or Tabular data model.

Before moving to DAX calculations and before you can understand how those DAX calculations actually work, you have to understand the most important DAX concept.  What is it?  Keeping the calculation simple so others can understand?  No.  Optimization is key though, right?  Right!  But not the most important.

The most important thing to understand is context.  Context enables dynamic analysis against a data model in which the formula results in a set or subset of rows or cells or even a singular cell. This allows a formula to be manipulated based on attribute elements or filters applied against the formula (either from within the formula itself or from the third party tool – i.e. Pivot Tables, slicers, Power View filters or cross-filter selection, etc.). Understanding context usage and utilizing it effectively is the most important DAX concept to grasp.  Without understanding context, your solution may not be optimized to perform the best, may provide incorrect results, and may be too restrictive in its use.

Row Context vs Filter Context (vs Query Context?)

row context

Row context is specific to the current row in a source table.  Row context takes a column as an argument which is interpreted as a single value.

 

 

 

 

Query context (per Microsoft filter contextbut called Filter Context by most others) is the same concept of a cell in a Pivot Table.  Query context is the end result of a filtered calculation.  Query context is the cell value after filters, slicers, column and row values are applied to the calculated field.

 

 

Filter context (per Microsoft) is when you “specify filter constraints on the set of values allowed in a column or table, by using arguments to a formula. Filter context applies on top of other contexts, such as row context or query context.”  Filter context is applied by utilizing a DAX functions such as FILTER, ALLEXCEPT or the ALL function.  Essentially, regardless of the Query context due to filters, slicers, column or row values, the Filter context is still applied (or overrides the filter context).

dax filter

 

Calculated Column vs Calculated Field

A calculated column is calculated after data is loaded.  It can be placed in columns, rows, filters, and slicers.  A calculated column is evaluated in row context.

i.e. [Full Name] = [Last Name] & “, “ & [First Name]

Calculated Column

A calculated field is created in the Power Pivot/Tabular measure grid or from Calculated Fields button on the Power Pivot ribbon.  A calculated field can only be placed in the values area of a Pivot Table / Report.  A calculated field can have Filter context applied (or not) in the definition arguments of the calculation.  Query context is applied to a calculated field once it is placed into a visualization or Pivot Table.  Even if there are no row or column values on the pivot table, the query context is essentially ALL.

i.e. [Sales] = SUM ( Sales[Total] )

Measure Grid

Calculated Field

This is somewhat of an easy concept to grasp….but not to understand in practice.  Once you understand this concept, truly understand this concept, doors will open.  The lights will come on.  Insert whatever cliche you want here.

You will utilize tables as filters, utilize tables as cross filters, and utilize DAX filters and functions in ways unimaginable before truly understanding this concept.  There will be a person, a project, or a moment where you just “get it“.  For me, it was a truly profound moment (or series of moments?) on a particular project.  My coworker (my DAX sensei), who truly thinks completely outside of the box at times, started utilizing multiple tables as “levers”.  It was like watching the old Lionsgate gears introduction….the doors opening to a beautiful world of DAX.

 

Keep at it!  We will be posting quite a bit more on DAX.  There are several other sources out there (will provide a list of those that I frequent the most).  You will get it!  We hope to help you understand the Power of DAX!

 

Microsoft Article on Context

Continue Reading

User Friendly Data Models in Power Pivot & Tabular

Organization and user friendly data model development in Power Pivot & Tabular environments take precedence over almost all other concerns.

We will discuss best practices of creating a data dictionary, naming rules, etc. of Power Pivot & Tabular data models in other, related postings.  

In this posting, we will discuss some options of organizing the data.  As stated previously, if the data model is not organized in a friendly manner (i.e. dimensions split from facts, etc.), it may prove to be difficult to navigate.

Creating dimensions separate from facts allows easier navigation of the data model.  Creating your calculated fields in appropriate fact tables makes it easier for the end user to find those measures.  Naming fields appropriately is also key.  

Power Pivot & Tabular data models differ in the development of user friendly solutions.  In a Power Pivot model, you are limited to these practices when making the model more user-friendly.  In some engagements, I have created a new “table” just to house some of my calculated fields.  This allowed me to provide a custom name to the fact table and eased the understanding of where to access custom measures for the end user.

In a tabular model, you have other options of organizing the data.  BIDS Helper allows extra features and functionality that is not available in a Power Pivot model.

BIDS Helper

BIDS Helper offers several features missing in SQL Server Data Tools that were available for multidimensional models but not for Tabular.  These include the following:

Tabular Translations Editor

Per BIDS Helper documentation:

Unlike Multidimensional projects, Tabular projects in SQL 2012 do not support translations. This feature gap makes Tabular models less user-friendly when the users are international since the field list in Excel PivotTables and Power View is not in their native language.

 

Another common scenario where this BIDS Helper feature helps is the following. Imagine you are a developer building a Tabular model for users who speak Hebrew natively. It may be easier to code the model with English table and column names so that your DAX would be more readable. But then you want to show your model to users with Hebrew table and column names. This BIDS Helper feature would let you code in English but display to users in their native language.

 

Warning: While translations of metadata work in Tabular models, they are not officially supported by Microsoft. If you encounter a bug in how Tabular handles translations and open a support case, Microsoft may not provide support.

–

BIDS Helper provides a UI for editing translations on metadata (not on data). The following objects can be translated:

  • Database caption
  • Cube caption
  • Perspective caption
  • Table caption and description
  • Column caption, display folder, and description
  • Measure caption, display folder, and description
  • Hierarchy caption, display folder, and description
  • Level caption
  • Action caption and description

Note: Only metadata is translated (i.e. column names) not the data (i.e. values in the rows in tables) since Tabular models do not support translating data like Multidimensional models do.

This feature allows interesting workarounds to common problems.  For instance, if you would like to utilize the same cube but have different departments that require different nomenclature for the same field, you would typically have one option; create a new calculated column that duplicates the field in question and renames the field to the required nomenclature while creating a different perspective for the department requiring the rename.  With tabular translations editor, you are able to reduce the memory footprint required by not requiring the extra calculated field(s).  Instead, you would create a new translation “language” for each department requiring different nomenclature

* Obviously, it is preferred for the same organization to utilize a synonymous nomenclature across departments.  This may be more useful in the case of a multi-tenant environment where each tenant utilizes different naming conventions.  As well, be careful not to make the maintenance of multiple translations and perspectives a spaghetti nightmare!

Tabular Display Folders

Per BIDS Helper documentation:

Unlike Multidimensional projects, Tabular projects in SQL 2012 do not support display folders. This feature gap makes Tabular models less user-friendly since the field list in Excel PivotTables is longer and less organized than it could be.

–

Warning: While display folders work in Tabular models, they are not officially supported by Microsoft. If you encounter a bug in how Tabular handles display folders and open a support case, Microsoft may not provide support.

BIDS Helper provides a UI for editing display folders on measures, columns, and hierarchies. All display folders are edited in the same place. Right click on the .bim file and choose Tabular Display Folders…

This is a great feature.  Typically, developers only have the naming convention and/or table creation as options to categorize their fields.  This is definitely a limitation of the product as you may want your most used attributes / measures to be first in the list or grouped differently.  This feature allows developers to organize attributes under dimensions or calculated fields / measures in fact tables into categories that make more sense to an end user.  For example, if the date dimension has standard calendar attributes as well as fiscal calendar attributes and time related attributes, those could be organized under three different display folders under the date dimension so as to not clutter the end user’s view and make it easier to navigate to the attribute they actually require.

We will likely add to this posting as more on this in a later post as well).

Continue Reading