In this video I will show you what it takes to be considered a SQL expert. I will cover difficult concepts like partitioning, recursive queries and more!
Are you interested in becoming an Architect?
Consider taking my MicroStrategy Architect Course on Udemy!
This tutorial covers the essentials of MicroStrategy Architect. In this video I cover lookup tables, fact tables, attributes, relationships & more.
Please hit the LIKE button if you find the content helpful and SUBSCRIBE to help out the new channel and stay up to date with new MicroStrategy and BI related content!
How Neural Networks Work – Bias
This is the third video in the Neural Network series that covers the subject of bias. This video takes an in depth look at how bias works and why it is needed in Neural Networks.
Feedback is always welcome! Enjoy!
Finally, a tutorial with examples explaining level metrics!
After watching this video you will have a complete in depth understanding of the tricky subject.
Please don’t forget to LIKE the video if you find it helpful and SUBSCRIBE to my channel to master MicroStrategy as I’m always adding all types of new BI related content!
How Neural Networks Work – Activation Functions
This is the second video in the Neural Network series that covers activation functions. This video takes an in depth look at how activation functions work and why they are needed in Neural Networks.
Feedback is always welcome! Enjoy!
Want to learn how Neural Networks work?
This is the first in a series of tutorial videos designed to teach complete beginners how Machine Learning and more specifically Neural Networks operate. I assume those watching this video have no prior experience on the subject of Machine Learning so if you are a complete beginner, this is a great place to start.
Now, this series takes a top down approach to learning where we will peel back the layers of Neural Networks with each video and they will build on top of each other. The goal of this first video is to provide an introduction to Neural Networks and give a basic understanding of how they work without over whelming you with too many technical details.
Feedback is always welcome! Enjoy!
Filter Reports using SQL with ApplyComparison and ApplySimple
No doubt MicroStrategy has powerful filtering capabilities but if you are familiar with SQL, somtimes you might find that you feel a bit constrained in certain situations and thinking you could do things easier with a simple query and there may come a time where you need to filter a report based on the results of a SQL query.
For example, suppose you have a monthly sales report querying data that is stored only at the month level. Now, let’s also assume that you would like the report filter to dynamically determine the current month based on yesterday’s date (today – 1 day). This is common in retail because you are typically always looking at the previous days sales. To keep things simple, let’s assume you have a table named “LU_DAY” with the columns Date, Month, Quarter and Year.
If you wanted to return the current month based on yesterday the SQL query would look something like this “SELECT DISTINCT MONTH_ID FROM LU_DAY WHERE DAY_DATE = Date-1“.
Now, let’s take a look at how we can put this all together in a report using the MicroStrategy Tutorial Project.
Step 1 – Create a new report and add the attribute month and the revenue metric as seen in the below screenshot.
Step 2 – Next add a filter and choose the option “Add an Advanced qualification” as seen in the below screenshot.
Step 3 – This is where the magic happens. Now, we need to use the ApplyComparison function to compare two values in our filter. In this case we will be comparing the Month attribute to the results of our SQL query that will return the current MONTH_ID. Take a look at the resulting function in the below screenshot and then we will walk through each parameter.
First Parameter “#0 IN (#1)” – This tells the ApplyComparison function how to compare the other values being passed as parameters. The #0 and #1 are place holders for the other parameters being passed to the function. So in this case you are telling the function to only return results where the value mapped to #0 is found within the value mapped to #1. It is also worth pointing out that since our query will only return one value we could have also entered “#0 = #1” but hopefully you get the idea.
Second Parameter [Month]@ID – Here we are simply passing in the Month attribute from our report. This value will get mapped to #0 in the first parameter.
Third Parameter ApplySimple(“SELECT DISTINCT MONTH_ID FROM LU_DAY WHERE DAY_DATE = Today()-1”,0) – In this parameter we are nesting another MicroStrategy function ApplySimple which allows us to use SQL pass-through to query the database directly from within our report. The ApplySimple function is a powerful function that can be used in many other places including metrics.
In closing, I do understand that in the tutorial project this was unnecessary because sales is stored at the day level so you could have simply used a normal filter but the purpose of this article is to show how you can incorporate SQL in your filters because there are instances where it is necessary. For example, as I mentioned in the beginning of the article, if the data you are reporting on is ONLY stored at a particular level like month, however, you need to filter the report based on data stored at a lower level like day, this might be an option to consider.
If you have used the MicroStrategy Task API’s reportDataService task, then you may be aware it has a limitation and will not work on reports that have a page-by. In this article I’m going to introduce you to a new plugin I have created using the MicroStrategy SDK named GetReportXMLorJSON.
The plugin zip file can be downloaded from my GitHub repository here. I’m guessing most of you reading this article probably already know how to install plugins but for those that do not, installing the plugin is pretty straight forward. To install the plugin, extract the contents of the zip file and copy the folder to the “plugins” folder located in your MicroStrategy web installation directory. For example, if you are using Tomcat, you would go to your MicroStrategy web server and copy the contents of the zip file to C:\Program Files (x86)\Apache Software Foundation\Tomcat 7.0\webapps\MicroStrategy\plugins. Next, you will need to restart your Tomcat or IIS web server for the changes to take effect.
Once you have the plugin installed, you can test the plugin by using the MicroStrategy TaskAdmin. The TaskAdmin is a nice tool for building and testing SDK URL’s. The URL for the Task Admin is http://localhost:8080/MicroStrategy/servlet/taskAdmin?pg=builder# (make sure you replace “localhost” with the host name or IP address of your web server. Next, click the tab labeled “Builder”. In the drop down box labeled “Task ID”, you should see the new plugin named “getReportXMLorJSON”. For this test we will have the new task return XML. Now, in the task envelope and task content type drop downs choose XML. Your screen should look similar to the below screen shot.
Next, we are going to enter the parameters. The first four parameters, ServerName, UserName, Password and Project are simply used to establish a connection to the project on your Intelligence Server. Next, you will need to enter the ReportID of the report you would like to execute. This can be obtained by viewing the properties of the report. The next parameter must be one of two values, either “xml” or “json” (without quotes). This will determine the format of the report results that will be returned by the task. Only enter a value for port if you know you are using something other than the default port. Finally, you have the option of expanding the page-by if the report has one. This is the key difference between this task and other out-of-the-box tasks that come with MicroStrategy as none of the out-of-the-box tasks that return XML or JSON can support reports with a page-by. I have confirmed this with MicroStrategy support. Lastly, enter values for prompts if required. Below is an example screen shot.
Once you are finished entering the parameters, click the button “Update URL”. This will build the appropriate URL in the below text box. This URL will be used to make the appropriate call to Intelligence Server. To execute the task, click the button labeled “Invoke URL”. This will use the URL to login to Intelligence Server, execute the report and return the results in the format we have specified. If all goes well, you should get XML results similar to the below screen shot with a status code of 200.
In this post I’m going to attempt to elevate your SQL skills to a satisfactory level in 5 steps, each step can be read in about a minute for a total of 5 minutes of your time. The SQL syntax I’m going to use is called T-SQL (Transact-SQL) and is compatible with SQL Server for example. Different databases can use different syntax but for the most part they are pretty similar and you can easily google the equivalent syntax for whatever database you may be working with.
Here is the agenda for the next 5 minutes:
Step 1 – The basics
Step 2 – Filtering
Step 3 – Joining
Step 4 – Grouping
Step 5 – Advanced SQL
Without wasting any time, let’s get started.
STEP 1 – THE BASICS
I’m guessing if you’re reading this you already understand the concept of tables and databases but basically a table is like a spreadsheet made up of columns and rows and a database is a collection of tables. SQL is the language we use to retrieve or query rows from those tables. Now, consider the following customer table:
Columns: CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, ZIPCODE, AGE_YEARS
Say we want to query all the columns and records in the table LU_CUSTOMER. Well, just about all SQL queries begin with the keyword “SELECT” followed by the columns you want to return with each column separated by a comma. In this case we want all the columns so we would type “SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, ZIPCODE, AGE_YEARS“.
The next step is to tell the database which table we want to select from by using the keyword “FROM” followed by the table name. So when you put it all together you get “SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, ZIPCODE, AGE_YEARS FROM LU_CUSTOMERS”. This query will return all the customer records from the LU_CUSTOMER table. (NOTE: If you want to return ALL columns, instead of typing each column name you can simply use an asterisk “*”, for example to query all columns from a table use SELECT * FROM TABLE_NAME)
Now, if you want to sort the results you would add the “ORDER BY” keyword followed by the columns you want to sort by. For example, to order by CUSTOMER_ID, you would use “ORDER BY CUSTOMER_ID”. The default sorting is ascending but you could add “DESC” after the column name to order in descending order. The final query with sorting would look like this:
STEP 2 – FILTERING
Usually when you are querying a table you only want a specific set of records. Now, using our previous SQL query, let’s say you only want customers with the ZIPCODE equal to 85253. Filters are added to a query by using the keyword “WHERE” followed by the condition. So, in our example we would add WHERE ZIPCODE = 85253 to our above query.
The resulting query would be:
Below is an example of query results.
It is also worth mentioning that you can use other operators such as ZIPCODE > 85253, which would return all customers with zip codes greater than 85253. If we wanted multiple zip codes like 85253, 19124 and 07013, we could modify the WHERE statement to use the keyword “IN” like this: WHERE ZIPCODE IN (85253, 19124, 07013).
Now, taking it a step further you can apply multiple filters in a WHERE clause. For example, if we wanted all customers with a zip code of 85253 and a last name of Janssen, our filter would look like WHERE ZIPCODE = 85253 AND CUST_LAST_NAME = ‘Janssen’. It is also worth pointing out that string or text values must be contained within single quotes.
STEP 3 – JOINING
Now, databases will usually have many tables and a single query will usually need data from multiple tables to obtain the desired results. To accomplish this you will need to join tables together using SQL.
Recall the LU_CUSTOMER table from the previous steps and now let’s add a new table that contains customer sales information named CUSTOMER_SLS. This table simply contains the total dollar sales and total units for each customer.
Columns: CUSTOMER_ID, TOT_DOLLAR_SALES, TOT_UNIT_SALES
Suppose we need to write a query that returns a customers zip code, last name and total sales units for all customers with a zip of 85253. Well, customer name is in the LU_CUSTOMER table and unit sales is in the CUSTOMER_SLS table. So, to join these two tables together we first build our select statement we learned in step 1 which would read SELECT ZIPCODE, CUST_LAST_NAME, TOT_UNIT_SALES.
Next, we add FROM LU_CUSTOMER. Now we need to join the two tables together based on a common field. Usually related tables will have an identity column in common. In this case both tables contain CUSTOMER_ID. In order to join these tables we use the keyword “JOIN” followed by the table name and “ON” followed by a link between matching columns.
The resulting query with a join will look like this:
Notice how we set the CUSTOMER_ID from LU_CUSTOMER equal to the CUSTOMER_ID from CUSTOMER_SLS. This is how you tell the database to link the two tables together.
It is worth noting that when you are working with tables that have the same column names, in order for the database to know which column you are referring to you must prefix the column name with “TABLE.” or in our example LU_CUSTOMER.CUSTOMER_ID and CUSTOMER_SLS.CUSTOMER_ID.
Now, what we have done is joined the CUSTOMER_SLS table to the LU_CUSTOMER table based on the CUSTOMER_ID. This query will only return customers that have matching customer id’s in both tables. For example, if customer id 12345 has a record in the LU_CUSTOMER table but does NOT have a matching record in the CUSTOMER_SLS table, then this customer would not be included in this query. This type of join where only matching records are returned is called an INNER JOIN and is the most common type of join.
Now, what if we wanted the query to return all customers even if they don’t have a matching record in the CUSTOMER_SLS table? Well, this type of join is called an outer join. If we wanted the query to function this way, we would simply change the keyword “JOIN” to “LEFT OUTER JOIN” and that’s it.
I could spend a lot more time on joins but we only have 5 minutes! 🙂
The final query with outer join would look like:
Below are some example query results using an outer join, notice the TOT_UNITS_SALES columns are empty (NULL) for some of the records. These customers did NOT have a matching record in the CUSTOMER_SLS table so the value is empty because there is no value.
STEP 4 – GROUPING
Grouping is a feature of SQL that allows you to total or aggregate data. For example, using the above query from the previous step, suppose we wanted to modify the query so it returned the zip code and total units sales for customers with the zip code 85253. This is where you will need to use the keyword “GROUP BY”. A group by does just that, it groups records together so you can perform some type of total or aggregate function. In our case we want to group by zip code, so we would add the statement GROUP BY ZIPCODE below the “WHERE” filter.
The query would look like this:
Now, any fields that are not in the “GROUP BY” must be wrapped in some sort of aggregate function like SUM, AVG or COUNT. In our example, we want the TOTAL unit sales so we used the SUM function with the column TOT_UNIT_SALES like this SUM(TOT_UNIT_SALES). In a nutshell this SQL statement tells the database we only want one record for each zip code and the other field “TOT_UNIT_SALES” will be a total of units sales for ALL records returned by the query. In this case the only records that will be totaled are ones with a zip code of 85253 as specified in the WHERE filter.
Below is an example of query results.
Let’s take this one step further and remove the filter “WHERE ZIPCODE = 85253”. The query will now return total units sales for ALL zip codes. Now, returning total units sales for all zip codes is great but let’s suppose you are only interested in zip codes where the total unit sales is greater than 2000. How would you accomplish this? You might be thinking you would add a filter “WHERE TOT_UNIT_SALES > 2000” but this is wrong because this will filter customer records before they are grouped by zip code.
Remember, the goal of our query is to return the TOTAL unit sales by zip code and we want to filter the results AFTER they are grouped. To accomplish this you need to use the “HAVING” keyword after your “GROUP BY”. The HAVING clause is how you can add filtering to the results of a GROUP BY. In this example you would add HAVING SUM(TOT_UNIT_SALES) > 2000 to the query.
The resulting query would be:
The below screenshot shows sample query results showing all zip codes returned have a total unit sales greater than 2000.
STEP 5 – ADVANCED SQL
It is important to think of SQL queries like spontaneous tables. Understanding this makes it easier to realize that queries can be joined just like tables. That’s right, queries can be joined to tables and other queries.
For example, let’s consider the following query:
In this SQL query I have joined together the results of two separate queries! This is completely valid SQL.
Now, let’s talk about the rules, first each query must be wrapped in parenthesis and aliased. To alias something simply means to give it a name. As you can see outside the parenthesis of each query I have used the name “query1” and “query2” respectively. You might also have noticed that I aliased a column in the second query. After SUM(TOT_UNIT_SALES), I used the alias name “TOTAL_SALES”.
See below screen shot for example results from this query.
As you can see the last column header reads “TOTAL_SALES” which is the alias name I gave this column.
Congratulations you now know satisfactory SQL!
Feel free to leave comments below.
Well, as the saying goes “necessity is the mother of invention” and in this post I’m going to introduce you to a tool I developed out of necessity that will enable developers to easily add MicroStrategy report export functionality to their custom applications or scripts. Some of you might be thinking why do I need this if I’m using Narrowcast or Distribution Services? Well, let me explain the benefits of this utility.
- As I said, this will enable developers to easily add report export functionality to their custom applications/jobs from any machine.
- MicroStrategy tools are NOT required to be installed on the machine where you are performing the export.
- Gives developers the power to export MicroStrategy reports using virtually any programming language or developer tool that can call and pass parameters to an executable. (I will demonstrate this using System Manager later)
- Can be used in shops that are running older versions of MicroStrategy or that lack certain licenses.
- Can be used in situations where Narrowcast or Distribution Services are not options. For example, let’s say you work for a retailer with several hundred stores. Now, suppose you have a requirement that requires you to automatically export (to a folder) daily sales reports in excel for each store. In addition, since stores open and close regularly, the process will need to be dynamic and only export reports for stores that are open for business. How can you accomplish this with Narrowcast? You obviously can’t create a separate Narrowcast job for each store because that won’t be dynamic, and you can’t export one report with all stores because they need to be in separate excel files as store managers should not be able to view other stores sales. To make matters worse, let’s suppose the business also wants each report to have multiple tabs with daily, week to date and month to date sales.
- Works with prompted and non-prompted grid reports.
- Quickly add custom MicroStrategy export to excel functionality without having to know/learn SDK
Let’s get started and take a look at how the utility works. The utility is an executable that takes up to 4 parameters. The parameters are as follows:
Parameter 1: Output Type – This is the type of file you want to export. “xls” = excel 97-2003 format, “xlsx” = excel 2007-2010 format, “pdf” = PDF file and “txt” = text file
Parameter 2: Output File – This is the full file path and name of the new file to be created. For example, “c:\temp\SalesReport.xlsx”
Parameter 3: Report ID – The MicroStrategy report id of the report to export.
Parameter 4 (Optional): Element Prompt Answers – This is used to pass parameters to prompted reports. Now, if you are familiar with SDK, I think you will find that passing parameters to this utility is a bit easier. The format is simple, it is basically just the attribute id followed by colon (:) then each value separated by a comma (,). If you have multiple prompts then separate them with a semi colon (;). For example if you have a report with two attribute element prompts and you want to enter 3 values in the first prompt and two values in the second prompt you would pass the following parameter string: AttributeID1:Value1,Value2,Value3;AttributeID2:Value1,Value2
The application files can be downloaded from GitHub here. I recommend downloading the zip file “MstrReportExport_v1.0.zip” and extract the contents. You should now have 5 files exactly like the below screenshot.
The only two files you are concerned with are Config.config and MstrReportExport.exe. Config.config is the configuration file where you will enter the configuration parameters that will allow the application to connect to your MicroStrategy implementation. This is a simple plain text XML file. Now, open the file in a text editor like notepad and fill in the appropriate parameters. Below is a screenshot of the files contents. It is pretty self explanatory and I have commented each value in the file but let’s review in detail anyway. The first parameters is WebServerType and this simply expects the value “asp” or “tomcat” depending on whether your web server is setup using IIS or Tomcat. Next is WebServer which is simply the host name or IP address of your MicroStrategy web server. Next is the host name or IP address of your Intelligence Server. The next parameter is Project which is the name of the MicroStrategy project that contains the reports you will be exporting. Lastly, enter the MicroStrategy logon credentials UserId & Password. The last optional parameter BaseURL is unnecessary and I recommend leaving it blank.
Now that we have set our configuration values we are ready to begin using the utility. As I said in the beginning of this post, I was tasked with exporting a particular sales report to excel automatically every day for each store. With the consideration that the list of stores can change any given day. How did I do it? Well, enter System Manager. I’m going to explain the System Manager workflow only briefly because I already wrote another post covering System Manager in more detail, including a post on how to automate it. In a nutshell I created a System Manager workflow with a SQL Task that queried the data warehouse for a valid list of stores, I then output those results to a text file. After obtaining the list of valid stores I then iterate through the list, each time I execute the MstrReportExport.exe application and pass the appropriate parameters using the System Manager “Execute Application” task. Below is a screenshot of the workflow with comments explaining the purpose of each task. After the workflow was created I simply scheduled it to run every day using Windows Task Scheduler. See my other post if you need help scheduling a workflow with Task Scheduler.
Now, as you can see we have solved the problem of exporting the reports automatically every day with a dynamic set of parameters. But what about the multiple tabs in each file? Well, simple, inside the report I used a custom group where each section had a filter for daily, week to date, month to date, etc. I then added that custom group to the page-by and viola! That’s right, the application can also handle reports with page by’s. Some of you more experienced SDK developers may have encountered issues working with reports that have a page-by at some point. Below is a screenshot of a sample report that was output by the MstrReportExport utility (with the data masked for privacy reasons of course).
I thought it is also important to mention that this solution is functioning in a live production environment.
As you can see in my particular case I chose to integrate the utility with System Manager but sky is the limit and you can integrate the utility with whatever technology you like. The only requirement is that you have the ability to call an executable and pass parameters but let’s face it I don’t think that will be a limiting factor as just about any programming language or toolset has methods for executing external applications.
If I get positive feedback I will put out additional releases with added functionality like the ability to handle value prompts, documents and export to XML to name a few.
UPDATE: If you need to export multiple separate files using documents and as long as you do not have prompts and are on a version >= 9.3, look into using the burst feature in Distribution Services. It can accomplish the same thing and is built into MicroStrategy. Thanks to my friend Ameya for writing me regarding this feature and explaining how to accomplish my particular use case with burst. This is a much better option in my opinion.
Please leave comments! I’m really looking forward to reading them.