Category: Business Intelligence (BI) Development

Analytics, Business Intelligence, Business Intelligence (BI) Development, Power BI

Setting a table as a date table in Power BI to be able to use the delivered Time Intelligence functions

It is common to have the need to perform time-driven analysis on your data. For example, you may need to compare this month’s sales with the sales from the same period a year ago or you may need to calculate the number of days between two dates. Power BI provides a set of Time Intelligence functions that make it easy to perform these types of calculations. But to take advantage of the Time Intelligence functions in Power BI, you must have a date table in your Power BI model and to have that you will need to “mark” a table as a date table.

To mark a table as a date table, it must meet the following criteria:

  • It must have a column of data type “Date” or “Date/time”. This will be referred to as the “date column”.
  • The date column must contain unique date values. For example, you cannot have the value “3/1/2022” (or any other date value) listed twice (or more than once) in the table.
  • The date column must not contain BLANKs or NULLs. Only unique date values should be in the table’s date column.
  • The date column must not have any missing dates or gaps in dates. For example, you cannot have 1/1/2022 and then 1/3/2022 and not have 1/2/2022 in the date values.
  • The date column must span full years. Keep in mind that a year isn’t necessarily a calendar year (January-December), but should cover the entire 12 months, such as all dates between July 1, 2022 – June 30, 2023, inclusive. If you have less than one year’s dates in your table, then the range can be less than a year, but there cannot be any gaps. Also,

Once these rules are met, you can then mark the table as the date table. To do this, you can either right-click on the table in the Data pane and select “Mark as date table” (as shown below):

Or with the table selected in the Data pane, from the Table Tools tab, click on the “Mark as date table” icon. This icon will be grayed out if there are no date columns in the table.

The “Mark as date table” dialog opens (which includes a warning), from which you can turn on the “Mark as date table” flag.

Turn on the flag, and then select / set the date column from the dropdown.

Power BI will then validate your data to make sure all the criteria is met for the chosen column. If you get an error, make the necessary changes and try again.

Thanks for reading!

Business Intelligence, Business Intelligence (BI) Development, Data Modeling, Power BI

How to create an Index column or ID column in Power BI

When working with data, you may need to add an index column (or an ID column) to your data. For example, you may have a list of products (names and descriptions) but you do not have an ID for each product, and you need to assign one to each product, perhaps for data modeling purposes.

To add an index or ID column to each row, you will do this in the Power Query Editor. With your query (table) open, go to the “Add Column” tab where you will see the “Index Column” option. 

You can click the menu item to use its default option – which will add an index column that starts at zero (0) – as shown below.

Or you may click the dropdown (to the right of the Index Column menu item) to choose from 3 available options (From 0, From 1, or Custom).

“From 0” will add an index column that starts at zero (0) as shown above (in the default example).

“From 1” will add an index column that starts at one (1), as shown below:

“Custom” will open a dialog for you to enter the “Starting index” value and the “Increment” between each value. In the example below, the starting index value is set to 1000, and the increment is set to 5.

This means that the first value in the index column will be 1000, the second value will be 1005, the third 1010, and so on. This is shown below.

Once your index column is added, you can rename it and move it as desired. For example, you may rename the column name to Product ID and move the column to the front of the table as shown below.

Thanks for reading. I hope you found this useful.

Business Intelligence, Business Intelligence (BI) Development, Business Intelligence Platform, Performance Tuning, Power BI

Power BI Storage modes

Power BI allows us to connect to many different data sources – from relational databases, NoSQL databases, files, and more – to source data for consumption in Power BI. From the data sourced, you can create additional data (new calculated columns, metrics, transformed data, etc.), build data models, and create reports and dashboards.

There are a few storage modes related to how the data is retrieved, stored, and processed in Power BI. The storage modes are Import, DirectQuery, Live Connection, and Dual. The storage mode is set at the table level for each table in the Power BI data model. I will now describe these modes.

Import

With the Import storage mode, Power BI imports and caches the data from the sources. Once the data import is complete, the data in Power BI will remain the same until is refreshed by the Power BI refresh process for that dataset.

This storage mode allows for the usage of the most Power BI features for data modeling and analysis. For example, Import mode is required for using two of the popular Power BI features, Quick Insights and Q&A. Also, this mode is almost always the best for performance. However, it’s not necessarily the best option in all scenarios. Since the data is imported, the file size can get large and can sometimes take a considerable amount of time to load. But generally, for relatively static, low volume data, it is the preferred choice.

Queries submitted to an imported dataset will return data from the cached data only.

DirectQuery

With the DirectQuery storage mode, no data is cached in Power BI, but the metadata of the source tables, columns, data types, and relationships is cached. Instead, the data is directly queries on the source database when needed by a Power BI query, such as when a user runs a Power BI report that uses the data.

For Since the data is not imported, if all the tables in the data model use DirectQuery, the Power BI file size will be very small compared to a model with imported data.

Live Connection

The Live Connection storage mode is a special case of the DirectQuery mode. It is only available when sourcing from Power BI Service datasets or Analysis Services data models. There are limitations when using this mode. Data modeling is limited to creating measures, and therefore, you cannot apply transformations to the data, and you cannot define relationships within the data. And you can only have one data source in your data model.

Dual

With the Dual storage mode, a table may use Import mode or DirectQuery mode, depending on the mode of the other tables included in the query. For example, you may have a scenario in which you have a Date table that is connected to one transaction table that needs to reflect the data in the source, and is therefore set to DirectQuery mode, and also connected to another transaction table that only has less than 100,000 rows and is set to Import storage mode. By setting the Date table to Dual storage mode, Power BI will use DirectQuery when the query involves the date table and the first transaction table, while using Import mode when the query involves the date table and the second transaction table.

The below table summarizes the Power BI data storage modes:

ImportDirectQueryLive ConnectionDual
-Data is imported and cached in Power BI

-Preferred for static, relatively small datasets

-All Power BI functionality is available – including DAX, Calculated tables, Q&A and Quick Insights

-Can connect to Analysis Services but Live Connection is preferred

-Can have unlimited data sources

-Typically provides the best performance







-Data is queried on the source when needed

-Use for large datasets and when data changes in source need to be updated immediately

-Features such as Q&A, Quick Insights, Calculated Tables, and many DAX queries are not supported

-Limited data transformation functionality

-Parent-child functionality not supported

-For relational databases

-Not supported for Analysis Services

-Performance greatly dependent on the source data source		-A special case of DirectQuery

-Used for connecting to multi-dimensional data sources, such as Analysis Services

-Can be used only with Power BI datasets and Analysis Services

-Can have only one data source

-No data transformation available

-Q&A and Quick Insights not available

-Can create measures



-A combination of Import and DirectQuery

-Power BI will choose the appropriate option based on the storage mode of the tables involved in the query

-Can improve performance



















Summary of Power BI storage modes

 Note: the content in this post is relevant for the PL-300 Analyzing Data with Microsoft Power BI certification exam.

Thanks for reading! I hope you found this information useful.

Good luck on your analytics journey!

Business Intelligence, Business Intelligence (BI) Development, Business Intelligence Platform

Dashboards with hidden pages behave strangely after upgrade to OBIEE 12c

After upgrading from OBIEE 11g to OBIEE 12c, some dashboards behaved strangely.  Things that were wrong include:

  • unable to edit the dashboard from the dashboard page
  • unable to see some of the dashboard pages / tabs
  • click on one tab and it takes you to another tab

It turns out that all the dashboards affected by this behavior were dashboards with hidden pages / tabs.

There is a workaround for this.  The workaround is – move all the hidden pages / tabs to be at the end of the list of dashboards pages in the dashboard properties view.  After doing this, the dashboard will work as expected.

There is also an Oracle patch for this bug.  It is Patch # 23511448.  As of the time of this post, it was still an interim patch and not yet a production patch.  From our perspective, the patch seems to have resolved the issue without causing any new issues.  However, since it’s still an interim patch, apply at your own risk.

Thanks for reading.

Business Intelligence (BI) Development

Upgrading OBIEE 11g to OBIEE 12c – First thing to ensure

Our team is currently in the process of upgrading our OBIEE 11g environments to OBIEE 12c. I have been gathering information about the process and will be sharing information on our experience as we progress.

I wanted to point of the first thing you want to ensure before planning/starting the upgrade from 11g to 12c – this may save you a little time. Or if you have already started, and encountered an error relating to … catalog version is not supported … then this post might be helpful.

You can upgrade the OBIEE catalog from OBIEE 11.1.1.7.x or OBIEE 11.1.1.9.x to OBIEE 12c. This should upgrade without any major issues.
But you may unexpectedly run into the above problem if you had upgraded the OBIEE 11g catalog using patch sets and had not run the full catalog upgrade. This results in the catalog being used as an 11.1.1.7 or 11.1.1.9 environment, but the version stored in the catalog is still older (such as 11.1.1.3 or 11.1.1.5).
Then when you try to upgrade from OBIEE 11g to OBIEE 12c, you get the error because the catalog is still technically not yet on an approved version for upgrade.

To resolve this, you need to run a full catalog upgrade on the OBIEE 11g catalog. This involves modifying the instanceconfig.xml file as follows:

Change the value of the UpgradeAndExit parameter from “false” to “true” as shown in the example below.

upgradecatalog

Restart the presentation services.
After this is complete, edit the files again and change “true” back to “false“, and restart the presentation services again.

You should now be able to upgrade your catalog to an OBIEE 12c version.

I hope this helps. Thanks for reading.

Business Intelligence (BI) Development, Business Intelligence Platform

The CONCATENATE statement, concat() function, and string concatenation operator (&) in QlikView

In QlikView, there is the CONCATENATE statement, the CONCAT() function, and the string concatenation operator (the ampersand – &).  These are all very different and in this post I will explain each of them.

CONCATENATE statement

The CONCATENATE statement is used in conjunction with the LOAD statement.  It appends the rows of one table to another.  It is similar to a UNION ALL statement in SQL.

Let’s take a look at a couple examples using the following tables:

Employee_Location

Employee_ID Employee_Name Employee_Location
1 John NY
2 Mary NJ

Employee_Office

Employee_ID Employee_Name Employee_State
3 Jane FL
4 Evan NY

Employee_Position

Employee_ID Employee_Name Employee_Position
5 Paul Cashier
6 Sonia Salesperson

If we concatenated the Employee_Location and Employee_Office tables using the following load script …

[Employee_Location]:
 LOAD
 [Employee_ID]       as [%Employee ID],
 [Employee_Name]     as [Employee Name],
 [Employee_Location] as [Employee Location]
 FROM [… data source details for Employee_Location …]

CONCATENATE (Employee_Location)
 LOAD
 [Employee_ID]        as [%Employee ID],
 [Employee_Name]      as [Employee Name],
 [Employee_State]     as [Employee Location]  --aliased column
 FROM [… data source details for Employee_Office …]

We would get this result …

Employee_Location

Employee ID Employee Name Employee Location
1 John NY
2 Mary NJ
3 Jane FL
4 Evan NY

Now, if we concatenated the Employee_Location and Employee_Position tables using the following script…

[Employee_Information]:
 LOAD
 [Employee_ID]       as [%Employee ID],
 [Employee_Name]     as [Employee Name],
 [Employee_Location] as [Employee Location]
 FROM [… data source details for Employee_Location …]

CONCATENATE (Employee_Information)
 LOAD
 [Employee_ID]        as [%Employee ID],
 [Employee_Name]      as [Employee Name],
 [Employee_Position]  as [Employee Position]
 FROM [… data source details for Employee_Position …]

We would get this result …

Employee_Information

Employee ID Employee Name Employee Location Employee Position
1 John NY  
2 Mary NJ  
5 Paul   Cashier
6 Sonia   Salesperson

Notice that the concatenation works even if the tables do not have the same number of columns.  This provide more flexibility than the UNION or UNION ALL statements in SQL where you need to add a dummy column to the select list of your first table before performing the union.

Concat() function

The concat() function concatenates all the values of a column into a single delimited string.  The column and the delimiter are specified as parameters in the function.  You also have the option of producing the result string with only distinct values.

For example, if you have the following table …

Product_ID Product_Description Product_Category
1212 Pen Office Supplies
3214 Paper Paper
1345 Sharpener Office Supplies
1177 Eraser Office Supplies
2780 Calculator Electronics
2901 Computer Electronics 
This statement: CONCAT(Product_Category, ‘,’)

Produces: Electronics, Electronics, Office Supplies, Office Supplies, Office Supplies, Paper
Notice there is a space after the comma in the delimiter, and therefore, there is a space after the comma in the output

This statement:   CONCAT(Product_Category, ‘|’)

Produces: Electronics|Electronics|Office Supplies|Office Supplies|Office Supplies|Paper
Notice there is no space in the delimiter, and therefore, no space between the values in the output

This statement:  CONCAT(DISTINCT Product_Category, ‘ | ’)

Produces:             Electronics | Office Supplies | Paper
Notice spaces in the delimiter, and distinct output.

Concatenation operator ( & )

When you need to concatenate 2 strings together, you use the concatenation operator – the ampersand (&).  For example, if you have address values in separate columns as in the table below …

Street City State Zip
123 Main St Orlando FL 32801

… you can output the address as one concatenated string by using the concatenation operator as shown in the script below …

[Street] & ‘, ‘ & [City] & ‘, ‘ & [State] & ‘ ‘ & [Zip]

[Notice a comma and a space is concatenated between Street, City and State; 
and only a space is concatenate between State and Zip]

… and that would produce the following result …
123 Main St, Orlando, FL 32801

Business Intelligence (BI) Development, Business Intelligence Platform

The ways of adding data to your Qlik Sense application

This post covers the basics of navigation and selection for getting data into your Qlik Sense application.  More information about each option will be detailed in upcoming posts.

When you are working with a new app to which data has not yet been loaded, with the app open you can use the “Add data” or “Data load editor” icons shown below the app title area to initiate a data load.

Use the “Add data” icon to add data from a file, database, or Qlik DataMarket using the Quick Data Load (QDL) wizard.
Use the “Data load editor” (DLE) to load data from files or databases, and perform data transformation with data load scripts.
qliksense_newapp_adddata_dataloadeditor

If your app already has data, the area below the app title area will contain existing Sheets, Bookmarks, and Stories.  But you will still be able to access the QDL and the DLE.

For the QDL wizard, you can access it from the Global Menu, and select the “Add data” menu item.
qliksense_globalmenu_adddata

For the DLE, you can access it from the Navigation menu, and select the “Data load editor” menu item.
qliksense_navigationmenu_dataloadeditor

More details about what comes next will be in upcoming posts.

Business Intelligence (BI) Development, Business Intelligence Platform

Disallow online RPD updates in OBIEE

You may want to disable online updates on your OBIEE RPD for performance reasons or because you have a specific development process that prohibits online updates.

To disallow online RPD updates, do the following:
Log into Enterprise Manager. Navigate the tree menu to Business Intelligence -> coreapplication.  Click tabs “Capacity Management”, and “Performance”.

Under the RPD Updates section, check the box for “Disallow RPD updates”.

disallowRPD_updates

This will prevent online RPD updates for all.

If you want to allow a select group of people to have access to perform online updates, such as a lead developer or administrator, then don’t do the above, but instead provide Administrator role to those that should have the access, and remove it from those that should not (and give them BI Author role for example instead).

 

BI Administration, Business Intelligence (BI) Development, Business Intelligence Platform, Data Development

“The connection has failed” Error when trying to Import Metadata into OBIEE

If you get the error “The connection has failed” when you try to Import Metadata into the RPD, this post may help you to resolve it.

The solution is to: Create an Environment Variable called TNS_ADMIN and set its value to the directory of your tnsnames.ora file.
The TNS_ADMIN variable tells Oracle Client where to find the tnsnames.ora file which contains your data source details.

In case you need the details:
Click the Windows Start menu –> Right-Click on Computer –> select Properties
Then click on “Advanced system settings” on the left.
Advanced_System_Settings

Click the “Environment Variables” button.
Then in the Environment Variables window, click New.
Enter the details for the TNS_ADMIN variable.  The value needs to be the path to your tnsnames.ora file, typically located at [ORACLE_HOME]networkadmin. The path will look something like the value shown below (it depends on where Oracle is installed on your system).
TNS_ADMIN_Environment_Variable

 Hope this helps.

Business Intelligence (BI) Development, Business Intelligence Platform

Weird prompt values in dashboard prompts after upgrade

Have you noticed weird prompt values in your reports after upgrading OBIEE from 10g to 11g?  Below is an example of what you might see …

Weird_Prompt_Values

This is usually caused by calculated columns in the report.  You will need to remove those columns, and then add them back to the report.  And then, depending on what your calculated columns were being used for, you may want to consider using Selection Steps instead to accomplish the same logic.

If you know of another way to fix this scenario, please share.