Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

«November 2015»

DirectQuery in Power BI Desktop

In the latest Power BI Desktop a new Preview features was released that now allows you to connect using DirectQuery to either SQL Server or Azure SQL Databases.  DirectQuery is a really neat feature that allows you to point to the live version of the data source rather than importing the data into a data model in Power BI Desktop. 

Normally when you want to get an updated dataset in the Power BI Desktop you would have to manually click the refresh button (this can be automated in the Power BI Service), which would initiate a full reimport of your data.  This refresh could take a variable amount of time depending on how much data your have.  For instance, if you’re refreshing a very large table you may be waiting quite a while to see the newly added data. 

With DirectQuery data imports are not required because you’re always looking at a live version of the data.  Let me show you how it works!

Turning on the DirectQuery Preview

Now, because DirectQuery is still in Preview you must first activate the feature by navigating to File->Options and settings->Options->Preview Features then check DirectQuery for SQL Server and Azure SQL Database


Once you click OK you may be prompted to restart the Power BI Desktop to utilize the feature.

Using DirectQuery in Power BI Desktop

Next make a connection either to an On-Premises SQL Server or Azure SQL database.

Go to the Home ribbon and select Get Data then SQL Server.


Provide your Server and Database names then click OK. ***Do not use a SQL statement.  It is not currently supported with DirectQuery***


From the Navigator pane choose the table(s) you would like to use.  I’m just going to pick the DimProduct table for this example and then click Load.  You could select Edit and that would launch the Query Editor where you could manipulate the extract.  This would allow you to add any business rules needed to the data before visualizing it.


Next you will be prompted to select what you want to connect to the data. Again, Import means the data

Read more

The Big Data Blog Series

Over the last few years I’ve been speaking a lot on the subject of Big Data. I started by giving an intermediate session called “Show Me Whatcha’ Workin’ With”. This session was designed for people who had attended a one hour introductory session that showed you how to load data, to look at possible applications … Continue reading The Big Data Blog Series
Read more

Time Intelligence Filters in PerformancePoint 2010

  • 10 September 2012
  • Author: Mike Milligan
  • Number of views: 18116

The time intelligence filters provided with PerformancePoint 2010 gives developers an easy way to provide users with a method for specifying time periods using common English terms such as "Last 6 months", "Same Period Last Year", "Rolling 3 months", and so on. These filters can be linked inside your dashboard to control Excel Services reports, SSRS reports, scorecards, and analytic girds and charts. Behind the filters are formulas based on the Simple Time Period Specification (STPS.)

In the text that follows, I hope to demonstrate the use of these concepts:

  1. Setting up and using Time Intelligence with both tabular and multi-dimensional data sources
  2. Using Time Intelligence with KPIs and Scorecards
  3. Using Time Intelligence with Analytic Grids, Charts, Excel Services reports, and SSRS reports
  4. Using both types of Time Intelligence Filters, the standard time intelligence filter and the connection formula
  5. Using the TI Connection formula to provide users with a From Date To Date range functionality

I have modified AdventureWorksDW2008 relational database by adding three views. One to increase our date dimension, one to extrapolate data to the current date, and one to use as a tabular data source. I made some pretty massive changes to the Adventure Works cube to simplify my demonstration process.

You can download those views and the XMLA for the altered SSAS database, here.

Once the cube was processed I had accomplished two things:

  1. My date dimension starts at the beginning of the year (best practice recommendation for working with time intelligence in PerformancePoint; but, not required.)
  2. My fact table has data through the current date.

In order for the time intelligence formulas to work properly, certain things must be set up on the data source connection. We will create two data connections for these examples. One will be a multi-dimensional data source to our cube and the other will be a tabular data source to a view that combines the data I need from the relational database. The process to add the time intelligence to these two data sources is similar; but, different.

Open PerformancePoint and create a new data connection by right clicking the data connection folder in the workspace browser and selecting "New Data Source." Select tabular list and SQL Server table. In the table field select the view included in the project files above named "vw_InternetSalesTabularExample." Select the time tab and select the options checked below in the screenshot.


Next, create the data connection to the OLAP cube. Right click the data connections folder, select "New data source"; but, this time select the "Multidimensional" tab and select "Analysis Services." Below is a screenshot demonstrating what the time tab should look like once complete.


Now that we have both data connections set up, I will first demonstrate using the time intelligence with a tabular source. The tabular data source can only be used with filters, KPIs and scorecards. Right click your PerformancePoint content list in the workspace browser and select "New", "KPI." Name the KPI "Internet Sales."

Within the KPI, rename Actual to MTD and rename Target to MTDLY. Click the data mappings for MTD, change the source, select "SalesAmount" in the measure dropdown and click the "New Time Intelligence Filter" button. Enter the following formula into the dialog:


Now, change the data mappings for MTDLY, change the source, again select "SalesAmount" in the measure drop down, click the "New Time Intelligence Filter" button and enter the following formula into the dialog:


Now, right click your PP content list folder in the workspace browser and select "New", "Scorecard." In the scorecard template dialog, select the "Tabular" tab and "SQL Server Table." Click Ok and the wizard will walk you through the next steps. Select your tabular data source, click next. Click the Select KPI button and choose the Internet Sales KPI we just created. Click Next a couple of times and the click Finish.

Delete the MTD column by right clicking on it. Then right click the MTDLY and select Metric Settings. Rename it to MTDLY vs MTD and select Actual in the "Additional data value" drop down. Click Ok. Drag ProductSubCat above "MTDLY vs MTD." Drag "Order Country" as the parent of the Internet Sales KPI. Click the Edit tab and then the update button. Your screen should look like this:


To create a scorecard using the multidimensional data source; the steps would be pretty much identical.

For the next demonstration we will create a dashboard with an Analytic Grid that uses a standard time intelligence filter. Start out by creating an analytic grid using the SSAS data source that looks like the one in the screenshot below.


Notice the "Date Calendar" is in the background of the grid. Now create a new time intelligence filter using the SSAS data source. We will enter the following Formula/Display Name combinations by clicking the Add Formula button for each one.



Clicking the preview button will show the MDX behind the formulas.


Note: The second row says no results because I ran this on Feb 29th, 2012. There was not a Feb 29th in 2011.

Notice the difference between the monthtodate, yeartodate, quartertodate, and fullmonth formulas and their SSRS compatible counterparts. SSRS can not handle these formulas so I used an alternate syntax to demonstrate how to accomplish the same thing using an alternate syntax.

Now create a new dashboard and drag the Analytic Grid to the design surface. Then drag the TI standard filter to the dashboard and connect it to the grid by selecting Member Unique Name from the TI filter and dropping it onto the drop zone in the analytic grid space. When the connection dialog comes up, make sure you select "Date Calendar" in the "Connect to" drop down.


Deploy your dashboard and test out the filter.

Now, we will create a TI Connection Formula Filter. Right click in your workspace browser and select New, Filter; but, this time select the time intelligence connection formula filter. Add your OLAP data source and click Next and Finish. Next create a new dashboard or a new page on your existing dashboard. We'll use the same analytic grid; but, this time hook up the TI connection formula filter to dashboard. Everything is the same as the screenshot above except this time click the "Connection formula" button and enter the following into the dialog:


This is saying, calculate from the selected date 6 quarters back (1.5 years) and aggregate from there to three quarters out. So if today is 1/1/2012, 6 quarters back would be 7/1/2010 (1.5 years away). That is our start range.

3 quarters back from 1/1/2012 would be 4/1/2011.

Deploy the new dashboard, open SQL Server Management Studio's cube browser and verify the results.





Note: select DATEADD(quarter,-6,'1/1/2012'), DATEADD(quarter,-3,'1/1/2012') returns 7/1/2010 and 4/1/2011. So why did I filter from 7/1/2010 to 6/30/2011 above? Because we are working in quarters. 4/1/2011 starts a new quarter and we include the entire quarter in the results. If this doesn't make sense to you, try an example using month-6:month-3 instead.

Next I will demonstrate how to provide your users with a From Date parameter and a To Date parameter to provide range based queries using the PerformancePoint Reports. You can provide similar functionality by using a single date parameter and using the Multi-Select tree display method; however, you and/or your users may prefer to use range based parameters.

The first step is to copy your existing Analytic Grid and give it a new name. We will work from this copy. Open this copy and click on the Query tab. Locate this line of code and position your cursor just before date.calendar part.

WHERE ( [Measures].[Internet Sales Amount], [Date].[Calendar].DEFAULTMEMBER )

Type FromDate in the parameter text box and click the insert button. Create another parameter in the same fashion by typing ToDate in the textbox and clicking insert. Modify the where clause so it looks like this:

WHERE ( [Measures].[Internet Sales Amount],HIERARCHIZE({<>:<>}) )

Create two new filters, one called TI From Date, and another called TI To Date using the time intelligence connection formula filter.

Now create a new page in your dashboard, drag the copied analytic grid to the design surface and then the two TI connection formula filters. Connect both filters to the analytic chart making sure to connect them to the proper parameters. Use Day:Day in both connection formulas. Publish your dashboard and test.

Next we will create a new report in PerformancePoint that connects to a SSRS report with a date calendar parameter. We can create two dashboard pages to demonstrate this functionality. One using the standard TI filter, and one using the TI connection formula filter. The only caveat you need to be aware of is that the members in the standard TI filter that use the ...toDate or ...Full(Month/Quarter) syntax will not work. You will have to use the alternate syntax describe earlier to get that same functionality.




The YTD (Non SSRS) filter produces the error:

  • Default value or value provided for the report parameter 'DateCalendar' is not a valid value. (rsInvalidReportParameter)


Note: These numbers match the earlier example we did using the Analytic Grid.

Next we will hook the TI filters up to an Excel Services report. The only issue I had when preparing this demonstration was an error that occurred when previewing the dashboard. "Attempted request on an invalid state. Unable to perform the operation" Google to the rescue!

Basically, I had to uncheck the box in SharePoint Central Administration for my Excel Services application that says 'Refresh warning enabled.'

I also received an error when I used the YTD (Non SSRS) filter with Excel Services.


'An error occurred while attempting to set one or more parameters in this workbook. As a result, none of the parameters have been set. Click Ok to return to the workbook.'


TI Standard Filter with Excel Services report


TI connection formula filter with Excel Services report. Numbers match previous examples.

It should be noted that the 'Show Details' and 'Additional Actions' features are greyed out when using the TI filters with a date dimension in the background of the analytic grid and chart. One (not very good) workaround is to put the date dimension on the rows or columns to get this functionality back. The reason this work around is not very good is that your report does not look the same.

Example using analytic grid:

Dashboard page looks like this:image_thumb47

Right clicking a cell has 'Show Details' and 'Additional Actions' grayed out.


I created a copy of the original and dragged the date calendar from the background to the rows underneath the geography to demonstrate what it would take to get the 'Show Details' functionality back.


Not too bad using the YTD (Non SSRS) filter. (Not too good either...)


But, change it to last 10 days, and it becomes very ugly, very fast.


Moving the date calendar above the Geo in the rows helps a bit in some cases.

So nutshell, there are some caveats with working with the time intelligence features in PerformancePoint. Overall, they are a great feature to add some great functionality to your dashboard with minimal effort.

Miscellaneous Facts

Suppose you have a data source that has multiple time dimensions and you want to
use both time dimensions. The solution is to create a new data source for each time
dimension you want to use in your PPS solution. For example, if the cube you are using
has both calendar year and fiscal year dimensions, you can create two data sources
using the same server and cube information with the only difference being the time
dimension selected in the Time tab of each data source. When creating KPIs or filters,
select the data source with the time dimension that makes sense for that object.

Colon (:)
The colon is used to indicate a range of dates. For example, the statement Day-
1:Day-7 selects all the days between yesterday and a week ago inclusively.
The comma is used to combine two members. For example, the statement Day-
1,Day-7 selects today and a week ago today as distinct dates.

You can create two kinds of Time Intelligence dashboard filters:
1. Time Intelligence dashboard filters that include a list of dynamic time periods that you specify
2. Time Intelligence Connection Formula dashboard filters that use a calendar control to specify information as of a particular date. When you create a Time Intelligence Connection Formula dashboard filter, you do not specify a formula until you connect that filter to a report or a scorecard.

Periods-to-date are a NEW type of TI formula added in Office 14. The result of a to-date period is an
aggregation of all time periods to date up to the last completed full period. Incomplete time periods are
automatically excluded. They are evaluated to the lowest degree of granularity in the data source by default. For example, if most granular time period in the data source were days, then the month to date expression will
aggregate all days from the beginning of the month to the last completed full day in the month. (The opposite is true for standard time periods They automatically include incomplete periods

Periods to date are not compatible w/ SSRS (personal experience.)


Here are some links that helped me put this blog post together.

PerformancePoint Relative Date Time Intelligence with Current Date Time

How to use Time Intelligence Filters with Excel Services or How to Pass a Range Selection into your Excel Report

From Date To Date in PerformancePoint Analytical Chart

Time Intelligence Post Formula Filter Template in PerformancePoint Server

PerformancePoint Time Intelligence - BI for the Masses

Create a Time Intelligence Filter by Using Dashboard Designer

Time Intelligence Differences Between Grids and Scorecards

Categories: Blogs
Rate this article:
No rating

Mike MilliganMike Milligan

Other posts by Mike Milligan

Please login or register to post comments.