Good morning to everyone! How are my friends and beautiful consulting adventurers doing? See you again in this magical meeting space called #Bestinmicro, a space where I will be writing about different topics related to MicroStrategy, its use, best practices, and features to make the most of the tool.
This week I bring you a little secret that very few people know, so I bet you a toaster that you didn’t know it. Today we will see how to use a file as a data source! But … watch out! Before you claim my toaster, I’ll add the secret spice… the data source, almost in real-time, without having to import files or use ETLs in between!
As many of you already know, MicroStrategy provides several alternatives for reading information from files. Let’s take a quick look.
This option allows you to import different types of files from disk. Excels, CSV, txt, and even JSON files.
When imported, an in-memory cube will be created that will store the current photo and can be used as a data source. Of course, every time the information requires refreshment, it must be done manually to republish the cube, selecting the file and importing it again. That is, this implies
– Whoever republishes the cube must have access to the latest version of the file.
– Every time you want to refresh the cube, you must do it manually.
– There is no guarantee that the information you see in MicroStrategy is the latest information in Excel.
The MicroStrategy for Office add-in allows you to use Microsoft Excel to connect to data in MicroStrategy.
The initial version of the add-in features the ability to import and update MicroStrategy reports and datasets to Microsoft Excel.
Of course, this goes in the opposite direction to what we are proposing here, it would be to access MicroStrategy from Excel, and not the other way around. In addition, this applies only to office. So, let’s go
And this is where I bet the toaster. There is an option, within the import data, which is to import data by URL.
If you look at the description, you will see that these are the same types of files as the option we mentioned before, only this time the data comes from FTP or web. And this is where the magic is.
Do not panic if the first thing you see is that this option is disabled. First, you need to enable data import as shown here:
Then, we will have to enable the source from which we will bring the data. This part is not very intuitive, because clicking on the general square is not the same as clicking right where it says “Set allowed URLs”.
So first, in case you have the file in a SharePoint, a repository, a drive, a Dropbox… it is a matter of adding it to the whitelist.
In this case, if we want to use a file, we will put it in our intelligence server (or some directory where it has access). For example, here I will create a directory “temp” inside C, and inside I will place the file I want to read.
Then, when we go to import from URL, this window will appear, where we will be able to choose between adding the path to the directory or browsing
There we put the directory and we can navigate between the different files available to import and create a cube:
Here comes the most magical magic of all: we can schedule the refresh of these cubes, and it will not be necessary to manually select the file (as if we did it in the import data).
Then, being able to create a schedule every 1, 5, or 10 minutes (depending on the need, and the weight of the cube) we can have updated information coming from a file without the need to be republishing.
You tell me if you are the new owners of a toaster. For my part, it is a functionality that came in handy when using data blending when creating dossiers, and I have also used it with hypercards that are fed with information coming from a SharePoint. So I hope you find it useful, and see you next week for another edition!
* Tu información será utilizada exclusivamente para contactarte en relación al Business Data Master. No hacemos spam ni compartimos datos con terceros.
