Monthly Archives: June 2014

Automatic/Manual Mapping while adding new Table

Hello there!

Adding new tables to a project could be a problem in terms of attribute mapping. Sometimes you have a column with the same name in older imported tables and when you add new a table, MicroStrategy will automatic map that column to an already created attribute.

Let me give you an example.

Let’s think that we added a table called “LU_TIME_SALES” and we have a column named as “YEAR” in this table.

We create an attribute pointing to that YEAR column from LU_TIME_SALES.

Now, we want to add a new table called “LU_TIME_INVENTORY” and we also have a column named as “YEAR“. But, now you don’t want to map that column to the previous YEAR attribute,  we want to create another attribute, because this is a different subject. Inventory is different from Sales, so you need to map those years in different attributes.

Every time you add a new table, MicroStrategy will search for every attribute in your project, to see if anyone has a column with the same name of the columns of our new table. If the answer is yes, it will automatic map that table as source for your matching attributes.

If it is what you want, that’s awesome. But sometimes (every time for me), you don’t want that.

To change this behavior you have to do a simple thing:

1. Open your project;

2. Go to Schema -> Warehouse Catalog -> Option -> Schema -> Automatic Mapping -> Choose: Do not map schema objects to the new tables. Just like the images below:

a.

pic1
b.

pic2

c.
pic3

 

That’s it. When you add new tables in your project, MicroStrategy won’t add that table as source for your attributes.

Hope it helps.

 

God bless you.

Workshop MicroStrategy Visual Insight

Hello there!

I’m really glad to announce my first online workshop! And we are going to talk about MicroStrategy Visual Insight!

Here are topics:

1- What is Visual Insight;
2- What is VI Dashboards;
3- Difference of MicroStrategy analytics Enterprise, MicroStrategy Analytics Desktop and express;
4- Importing data;
5- Using Time and Geography attributes;
6- Refreshing imported data;
7- Themes;
8- All panes in Visual Insight;
9- Saving, exporting and printing;
10- Visualisations (All graphs);
11- Using image layout;
12- Using ESRI map;
13- Configuring ESRI maps for Enterprise version;
14- Sorting;
15- Drilling;
16- Local Filtering;
17- Attributes selectors;
18- Thresholds;
19- Subtotals;
20- Derived Metrics;
21- Sharing Dashboards;
22- Converting Dashboard in document;
23- Visual Insight for mobile;

Terms:

  • The course will be presented in Portuguese;
  • We are going to use a Virtual Classroom;
  • Live class will be recorded and available for all students to download in .mp4;
  • Duration: 1 week. With 4 hours of live class on july 5, 2014 at 03:00 PM (15:00 hrs – Horário de Brasília) and exercises and solving doubts during the rest of the week;
  • You can send your own data (Excel or CSV) to be used in the live class;
  • Investment: R$ 70,00 that can be paid using PagSeguro/Paypal or deposit (Itaú e Banco do Brasil)
  • Date: July 5, 2014 to july 12, 2014
  • Up to 10 students;

The next course will be presented in English.

If you would like to subscribe for this Workshop (Portuguese), you can do it by filling the form below. I will be sending you the instructions for the course.

 

Manual map in MicroStrategy architect

Hi there!

Do you know that is possible to map attributes in MicroStrategy Architect using “Manual Mapping“?

If you create attributes and facts, you know that is always a good idea to map new objects using Manual Mapping Method.

pic1

That’s because you may have columns that has the same name in different tables and MicroStrategy will add all those tables as forms definitions to your attribute.

If you create attributes using MicroStrategy Architect, the default mapping method used by it is Automatic.

There is a way to change that!

Just open MicroStrategy Architect, choose Hierarchy View, choose Design and click on this almost hidden button shown in the image below:

pic2

A window will appear and you need to uncheck the first option (Use automatic column mapping), like the image below:

pic3

That’s it! Now you can create new attributes without the Automatic Mapping Method in Architect!

Hope it helps!

God bless you.

Layout examples for dashboards

Hello there!

What if you don’t have an idea for layout to create a dashboard? Where do you look for inspiration?

I constantly look for dashboards pics in http://pinterest.com.  Just try to search for

dashboard

” there. You will see that there are a lot of good examples of there!

What about you? Where do you

look for inspiration?

Post in the comments below!

God bless you!

Performance in Reports (Part II)

Hello there!

Today I’m going to talk about the second part of the series “Performance in Reports”. If you would like to read the first part just click on this link “Report Performance (Part I)“.

I believe that nowadays I always see the query that Microstrategy creates when creating a new report. It recommend you to do so.

Sometimes you see that the query delays so much, and you don’t know what is going on.

Let’s believe that you have created all indexes needed to perform that query and still you want to improve the performance. Have you already heard about aggregated fact table?

Let’s analyse this simple database below:

pic1

As you can see, we have 2 hierarchies and 1 fact table here. Inside hierarchy Employee we have two dimension tables: employee and language. Two tables in geography hierarchy: country and continent. The fact Salary was created using the lowest level of the hierarchies: employee_id and country_id. That facts are commonly called Base Facts, just because it uses the base (lowest level) of your hierarchies.

So, if you want to display language_DESC and continent_DESC in a report? Microstrategy needs a few more steps to accomplish that, as you can see below:

select language_DESC, continent_DESC from Salary_fact a1
inner join employee a2 on (a1.employee_ID = a2.employee_ID)
inner join language a3 on (a1.language_ID = a3.language_ID)
inner join country a4 on (country_ID = a1.country_ID)
inner join continent a5 on (a5.continent_ID = a1.continent_ID)

It needs to go to Employee and Country tables because Salary doesn’t have a bridge to language and continent, only to employee and country.

Now, look at this datagram below:

pic2

I have added another fact called Salary_Fact_Agg that contains language_ID and continent_ID. Now, Microstrategy reduce the steps to get those columns.

select language_DESC, continent_DESC from Salary_fact_Agg a1
inner join language a2 on (a1.language_ID = a2.language_ID)
inner join continent a3 on (a1.continent_ID = a3.continent_ID)

That will make your query run faster than the first one.If you reduce the amount of joins that is required to get the data that you desired, you will increase performance of your report. So, if you change the granularity of your facts aggregating them in a higher level, you create an aggregated fact table.

 PROS

  1. Performance – The amount of joins is reduced and your query is executed faster.

CONS

  1. Database size – Creating more facts will increase the size of your database;
  2. ETL complexity – ETL needs to maintain another fact;

An advice:

Only create aggregated fact table when you notice that you have a lot of queries that are using a higher level dimension than the base facts that you have and the performance isn’t that good. Don’t agg facts at the beginning of your data warehouse creation. You have to test the base facts first and see if it’s really required an aggregate fact table to resolve performance problems.

 

Hope it helps.

God bless you.

Cool URL API events for MicroStrategy Mobile

Hello there!

I’ve already spoke about how to create a manual back button in MicroStrategy Mobile, and now I am going to talk about more cool events that we can create using URL API for Mobile. To do all those examples below you just have to create a link in an image or textfield, for example, and insert the URL of the example in the “Navigate to URL” field.

1. Email a screenshot of document

With this event, you can create a screenshot of your panel and send it using the email from Mobile devices automatically.

Just use this url below:

mstr://?evt=3037&emailSubject=Look!

You can change the subject of the email just changing the &emailSubject parameter value.

2. Display an Information Window

You can use grid/graphs to open an information window to show more info about that specific element that you have tapped. But sometimes you want to display a help information window just tapping an image or text.

You can do it using this url below:

mstr://?evt=2048500&panelName=NameOfThePanel

You just have to change the &panelName parameter value to use your panel stack information window name. You can see the name of the panel with a right click on your panel stack -> Properties and formatting -> General -> And then copy the Name field value.

3. Re-prompt a document

Sometimes users don’t know how to re-prompt a document, that filter button isn’t that intuitive, and now, you can create your own re-prompt button!

Just use this URL below:

mstr://pr

That’s it for now.

Hope it helps.

God bless you.

Microstrategy Mobile Manual Back Button

Hello there!

URL API can do a lot of cool things in Web and even in Mobile environment. One of that is the possibility to create a manual back button that goes to the last report/document that the user was executing.

That’s a very easy thing to do, just:

1. Create text, right click on it and choose “Edit Links…” option.

2. In Navigate to this URL, type:

mstr://?evt=3124

That’s it. Now you can go back to the previous report/document using this object.

You can use this feature in every object that is able to use Hyperlinks.

I hope it helps.

God bless you.