[ad_1]
Microsoft’s Power BI supplies a default date table that’s adequate for many time grouping requirements. Find out how to know if the Auto date table is adequate when using Power BI.
You can base Power BI reports on time components without any specialized knowledge of how Power BI’s time intelligence works. That’s because Power BI automatically identifies date/time columns and builds a date table based on the data. Power BI will use this Auto date table in visuals, tables, measures, slicers and so on, even if you don’t know about it.
However, the more you know, the more you can quickly produce the most accurate and quickest results. For that reason, you should know what a date table is and how Power BI uses it. In this tutorial, you’ll learn about date tables and when Power BI’s default Auto date table — the one Power BI creates internally — is adequate.
I’m using Power BI Desktop on a Windows 10 64-bit system. Throughout the article, I will use the terms date and data to describe the date table and data (facts) tables, and it might be easy to confuse the two.
SEE: Feature comparison: Time tracking software and systems (TechRepublic Premium)
What is a date table?
A date table is a table of dates and other metadata about those dates. You may also have heard the terms calendar table or a date dimension table, when using other data modeling software.
If a model has two or more data tables, most likely Power BI has created a relationship between at least one of them and its internal date table. This relationship allows end users to report and compare data by time periods, such as months and years.
The most important use for a date table is to create relationships between data tables in data modeling software. For example, let’s suppose you store projected sales figures and actual sales figures in different data tables. Thanks to the relationships between the date table and the sales figure data tables, you can compare both in the same report.
If you’ve not seen a date table, it looks like any other table with a row for every date in the related dataset(s). The first column in a date table is a date/time data type column named Date that stores a date, which it gets from a data table, so Power BI knows what dates to include in the date table. If you’re familiar with database terminology, you can think of this column as the primary key because it uniquely identifies each date record.
The remaining columns store metadata about each date: Each row displays the year, quarter, month, month name and day of year for the value in Date (Figure A). Unlike the Date column, metadata columns can and often do hold duplicate values because they are the many sides to a one-to-many relationship between the date table and the data tables.
Figure A
The date table improves the date-and-time search performance, but it’s more critical than that. Without a date table, you can’t use standard time functions in Power BI.
About Auto date table in Power BI
When Auto Date/Time is enabled, Power BI automatically creates a date table. It’s convenient and easy to implement because you, as the designer, don’t have to do anything. This feature creates and populates the date table for you.
If you import and create reports from scratch, you’re using the Auto date table until you create your own, if necessary. If you inherit an existing Power BI report, you can quickly discern if it’s using the Auto date table:
- Click the File tab and choose Options and Settings in the left pane, and then, click Options.
- In the left pane in the Current File section, click Data Load.
- The Auto date/time option in the Time Intelligence section is checked (Figure B), when Power BI is using the internal Auto date table.
Figure B
Power BI recommends that you use Auto when working with calendar time periods and when your modeling needs are simple. When exploring data or creating a quick prototype or ad hoc model, Auto should be powerful enough. But, here are a few things to keep in mind when making your choice:
- The year and quarter columns in the date table relate to calendar periods. The year begins on January 1 and ends on December 31. You can’t change that.
- You can’t add columns to the Auto date table.
- The quarter, month and day columns won’t include the year. The work around is to always start the filter with the year. This is an important design consideration if your data spans more than one year.
- Filters are specific to each table. You can’t base a filter on one table and then use it on others. This is another important design consideration because the designer must apply filters to each date column, as needed.
Don’t use an Auto date table if you need to use Analyze in Excel, use Analysis Services query designers or connect to non-Power BI report designers. This last one is for serious reporting and won’t impact most designers.
A date table in Power BI must meet the following requirements, whether it’s an Auto table or one you create:
- It must have a column named Date that’s a date/time data type.
- The Date column must contain unique values.
- The Date column can’t contain blank or null values.
- The Date column can’t have missing dates — the dates must be contiguous.
It’s best to know up front whether you plan to rely on the Auto date table or create your own. Power BI won’t support more than one date table. If you build visuals on the Auto date table and then create your own date table, Power BI will destroy the Auto date table and all visuals based on it. This is a choice you should make up front, if possible.
Some report authors don’t even know there is a date table working behind the scenes, which puts them at a disadvantage should they end up working with a more complex dataset and need the efficiency of a custom date table.
Stay tuned
Briefly, use Power BI’s default Auto date table when your date grouping needs are simple. If you find that missing values or other situations are returning erroneous information, you’ll need to upgrade things a bit by creating a custom date table, which we’ll explore in a future article.
[ad_2]
Source link
