Using PIVOT function without a CTE

SQL, SSAS

There are some cases when you need to use a PIVOT function in SQL but you cannot use a CTE. One such case is when you create a named query in a SSAS cube.

To do so, place the first part of the CTE query as a subquery of the Non-CTE query as shown here. Remember to add an alias to the subquery. The PIVOT portion of the code remains the same.

PIVOTwithoutCTE

The basic understanding of the PIVOT function can be found here.

Note: It may increase query performance when joining another table to the pivoted table to use join hints. e.g. LEFT OUTER HASH JOIN

Microsoft Dynamics AX 2012 – Multiple Rows In Demand Forecast Table [FORECASTSALES] for each item

Business Intelligence, Microsoft Dynamics AX, SQL

Why are there multiple rows for each item in the Demand Forecast Table in Microsoft Dynamics AX 2012?

This is helpful to know for BI for cubes that aggregate the data in the [FORECASTSALES] table.

Basically the overview line and each exploded line of the same item is stored in the same [FORECASTSALES] table. Therefore, if any measure in this table needs to be aggregated per item, filter out just the overview rows. You can do this by filtering the column [EXPANDID] = 0.

Continue reading →

Management Reporter Security

Business Intelligence, Management Reporter, Microsoft Dynamics AX

Are you confused by how user roles in AX 2012 are integrated with Management Reporter roles? Have problems adding or deleting users in Management Reporter? Get your answers here.

In the last couple of weeks I have been asked by AX developers about Management Reporter Security. Mainly, they were stumped by how assigning different roles to users in Management Reporter does not quite turn out as they had expected. Both times I felt I could only give a partial answer. Thus, I decided to do a little experiment to find out the truth once and for all… or until Microsoft comes up with the next Rollup I suppose!

In this topic, I will cover:

Management Reporter is able to integrate with different flavours of Microsoft Dynamics but in this topic I will cover integration with Microsoft Dynamics AX 2012 only, and only for up to RU5, simply because that is what is available to me for my experiment!
Continue reading →

Conditional Coloured Bar Charts in Excel with Upper and Lower Targets

Business Intelligence, Excel

I was just trying to gauge my BI knowledge level the other day and decided to chart it for visibility.

It should be able to tell me to focus on certain topics if 1) I know 20% or less of the topic and 2) if it reaches 100% – which means I need to expand the depth of the topic.

Here is what I want to achieve:

How do I get the conditional colour formatting of the bars in the chart?
Continue reading →

What is Microsoft Management Reporter for Dynamics AX?

Business Intelligence, Management Reporter, Microsoft Dynamics AX

It is a collaborative and interactive reporting solution from Microsoft for finance data.

View more here:

Management Reporter 2012 Overview

How is it different from standard SSRS?

It is designed to be collaborative. How often do you need discuss a report with other people? This software actually allows you to share via lync or sharepoint, and to insert comments at defined points of the report.

The other massively useful function is the ability to drillthrough all the way to the Dynamics AX entry.

The other functions I think which are useful are:

  • Multilanguage support
  • NO IT involvement required once setup
  • auto-incremental update of data (NO ETL required)
  • Multisource consolidation
  • Integration with Dynamics AX

The documentation for Management Reporter is available now on Technet.

http://technet.microsoft.com/en-us/library/dn435963.aspx

Note:

Microsoft SQL Server 2014 is not currently supported.

Minimum database requirements:

  • Microsoft SQL Server® 2005 Express Edition with Service Pack 4
  • Microsoft SQL Server 2008 or newer is required for the data mart database, system database, and the company  database for the ERP.

Introducing Microsoft Dynamics AX 2012 with SSRS Reports

Business Intelligence, Microsoft Dynamics AX, SSRS

Hi all,

I’ve started a new job with eBECS which is a Microsoft Gold Partner in the ERP Business.

Since I have been there, I have been learning how Dynamics AX is integrated with Microsoft BI Stack since 1) they are from the same company and 2) I need to support BI in an AX environment.

SSRS is now the primary reporting platform for Microsoft Dynamics AX since the 2012 incarnation.

Here’s the quote from Books On Line:

Microsoft SQL Server Reporting Services is now the primary reporting platform for Microsoft Dynamics AX. The default, preconfigured reports that are included with Microsoft Dynamics AX run on the Reporting Services platform.

Good news is that everything you know about SSRS can be applied into the world of Dynamics AX now. All the flexibility of being able to tweak every little detail is available to the report.

On the other hand, it would be useful for AX developers to know more about the SSRS development environment which includes:

  • SSRS services provides two URLs – one for the Report Manager, and one for web services
  • Administrator rights is required for deployment of reports
  • Report Security has to be defined in Report Manager. A new security role is available for this. It is called: DynamicsAXBrowser

For SSRS developers, it is useful to know the following:

  • The No. 1 rule is not to edit the report without saving the project back to the Application Object Tree (AOT). This is because if the AOT is rebuilt, all changes will be wiped out.
  • To create a new report, create a new query in the AX Development Workspace first before creating a new report.
  • Create a new report in Visual Studio 2010 and use the new Microsoft Dynamics AX Template called Report Model.
  • Creating a new report this way requires a whole different process than a normal SSRS report. (Which I will discuss in a separate post)
  • You can find out the SSRS url either from the AX Development Workspace (Tools -> BI Tools -> Report servers, or the usual way – via SSRS Configuration Manager.
  • Save the report project into AOT.
  • Then deploy the reports. There are two ways – via Visual Studio (which provides more output messages which is useful for troubleshooting, or via the AX Development Workspace)

For more information of SSRS in Microsoft Dynamics AX 2012, visit: http://technet.microsoft.com/en-us/library/dd309644.aspx

First take on PowerPivot

Business Intelligence, PowerPivot

What is PowerPivot for Excel?

PowerPivot brings the power of Data Modelling into your PC.

Traditionally, the DataWarehouse is built on a database server (SQL) and a data model is built on top of it (SSAS). Then, a front end is used to present the data to users.

Now, data can come from different sources and pulled into the PowerPivot engine, relationships defined, calculations done and reports presented on Excel.


Continue reading →

Understanding the PIVOT function

70-461, Certification, SQL

The PIVOT function is very useful but not easily understood at first. I thank Itzik Ben-Gan, Dejan Sarka and Ron Talmage and their study guide Exam 70-461: Querying Microsoft SQL Server 2012 for the brilliant explanation.

Here’s their example replicated here. Remember there are 3 columns used in PIVOT. The Grouping column is what you want each row to be aggregated to. The Spreading column becomes the columns after PIVOTing, and the Aggregate columns are the measures.

You need a pivot aggregate function (SUM in this case) and COUNT(*) cannot be used but COUNT(<column>) can.

The reason a common table expression is used to select only the necessary columns is because all other columns beside the Spreading and Aggregate columns will become the Grouping element which may not necessary be what you want.

Continue reading →

Get list of dates using recursive Common Table Expression (CTE)

Datawarehouse, SQL

Many times we need a list of consecutive dates or time at runtime mainly for a recursive ETL process. This code provides a list almost instantaneously with only the start and end dates being the only input. The output is in Date format but is easily extended to a DateTime format.

For best results, you can use this in a CTE as part of the solution so that it does not take up space in the database.

The MaxRecursion option ensures that the recursion does not go on forever, however the Per Minute and Per Second versions will definitely overrun the MaxRecursion option.

Note: The code for each one are similar, the only difference is the first parameter of the DATEADD function which determines the recursive detail.

Daily
Monthly
Yearly
Hourly
Per Minute
Per Second
Continue reading →

Top 10 Tips when synchronising your database and Mailchimp

Datawarehouse, MailChimp

Okay, sooner or later someone is going to need to synchronise all those people who registered on your company website with an email service provider to do a bit of marketing. In my case, the latest email provider to come my way is MailChimp. MailChimp is a very popular email service provider, which was used primarily by small businesses who could not afford the typical email giants like Experian and CheetahMail. However, the industry has changed and now they service many major companies.

To do so, you will need to use the MailChimp API. The latest as of the time of writing is v2.0 and can be found here: http://apidocs.mailchimp.com/api/2.0/

On to the tips!

1. Try not to update incrementally using dates
2. Do a full list compare between the MailChimp lists and your own database
3. Re-subscribing requires 2 actions
4. Use 1 or 0 for boolean parameters
5. Remember localisation
6. Remove left and right space paddings and tab characters for email address before uploading or comparing email
7. Do not forget to handle stray emails
8. Use Webhooks to unsubscribe people at real-time
9. Remember to close the loop by unsubscribing people from your database too
10. Keep records of invalid emails – and do something about them

Continue reading →