Miky Schreiber's Blog

My Impressions From the NovaView 5.5 Seminar

Well, I don't know what I expected but I'm a little disappointed. I'll split my review into two parts:

The Analytics - This is the main issue for my organization, so here I expected to see some new & cool features, but all I've seen is only facelift. This is probably why the new version is 5.5 and not NovaView 6.
The Google stuff - While this is not relevant for my organization, this was very cool and promising. I think that this relation between Panorama and Google will carry on and both sides will only benefit from it.

The greatest thing I got from the webinar is ideas of beautiful designs for sites containing Panorama ~~applets~~ views. The site that has been showed in the webinar was beautiful and intuitive. I just can't wait to give my customer a site that looks like it, completely sewed for him. Go on, look at the webinar and take some ideas for your site design.
If you haven't seen the webinar yet, you can download it or watch it.

BI | Panorama

Tuesday, June 17, 2008 7:24:21 AM (Jerusalem Daylight Time, UTC+03:00)

Comments [1] | Trackback

Placing Last Update Date in Panorama Views' Titles

My customer wanted to have the ability to show the last update time of the data in the Panorama's views' titles. He knows that I know to deliver :-)

so in a couple of hours he had it. Using AMO and the view's xml manipulation it's very simple. Just note that changing views without NovaView Desktop is not supported by Panorama so watch out before you execute this program. In your first trials, always save the books dir (by default in c:\program files\panorama\e-bi\books) before you start. Also, be aware that this won't work for automatic views. The user must enter the view's title himself and write the string "Correct For" and the program will know to write the last update time after it. This is the program's code:

  1 using System;
  2 using System.Collection.Generic;
  3 using System.Text;
  4 using AMO = Microsoft.AnalysisServices;
  5 using System.Xml;
  6 using System.IO;
  7
  8 namespace CubeUpdateDate
  9 {
10     class Program
11     {
12         static void Main (string[] args)
13         {
14             CubeUpdateDate cud = new CubeUpdateDate();
15             cud.Go();
16         }
17     }
18
19     class CubeUpdateDate
20     {
21         public void Go ()
22         {
23             DateTime cubeUpdateDate = GetCubeUpdateDate(GetConfigData("ServerName"),GetConfigData("DataBaseName"));
24             UpdateViews(GetConfigData("BookDir"),cubeUpdateDate);
25         }
26
27         private void UpdateViews (string dirName, DateTime cubeUpdateDate)
28         {
29             foreach (string subDirName in Directory.GetDirectories(dirName))
30             {
31                 UpdateViews(subDirName, cubeUpdateDate);
32             }
33
34             foreach (string fileName in Directory.GetFiles(dirName))
35             {
36                 UpdateFile(fileName, cubeUpdateDate);
37             }
38         }
39
40         private void UpdateFile (string fileName, DateTime cubeUpdateDate)
41         {
42             try {
43                 XmlDocument xmlDoc = new XmlDocument();
44                 xmlDoc.Load(fileName);
45                 XmlNodeList titleTags = xmlDoc.GetElementsByTagName("Title");
46                 if (titleTags.Count > 0)
47                 {
48                     string viewTitle = titleTags[0].ChildNodes[0].Attributes[0].Value;
49                     if (viewTitle.Contains(@"Correct For"))
50                     {
51                         viewTitle = viewTitle.Substring(0, viewTitle.IndexOf("Correct For") + 11);
52                         viewTitle += " " + cubeUpdateDate.ToShortTimeString() + " " + cubeUpdateDate.ToShortDateString();
53                         titleTags[0].ChildNodes[0].Attributes[0].Value = viewTitle;
54                         titleTags[0].ChildNodes[0].Attributes[1].Value = viewTitle;
55                         xmlDoc.Save(fileName);
56                     }
57                 }
58             }
59             catch (Exception e)
60             {
61                 Console.WriteLine("Error reading/writing file: " + fileName);
62             }
63         }
64
65         private string GetConfigData (string whichData)
66         {
67             XmlDocument xmlDoc = GetConfigXml();
68             return xmlDoc.GetElementsByTagName(whichData)[0].InnerText;
69         }
70
71         private XmlDocument GetConfigXml()
72         {
73             XmlDocument xmlDoc = new XmlDocument();
74             xmlDoc.Load("config.xml");
75             return xmlDoc;
76         }
77
78         private DateTime GetCubeUpdateDate (string serverName, string dbName)
79         {
80             using (AMO.Server server = new AMO.Server())
81             {
82                 server.Connect("Data Source=" + serverName);
83                 AMO.Database db = server.Databases[dbName];
84                 return db.Cubes[0].LastProcessed;
85             }
86         }
87     }
88 }

The program uses xml config file that looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<Config>
    <ServerName>MyOlapServer</ServerName>
    <DataBaseName>MyDBName</DataBaseName>
    <BookDir>MyBookDirPath</BookDir>
</Config>

The program assumes that all the database has the same update time so it takes the last process time of the first cube in the database. If it's not true in your case you can change it in the method GetCubeUpdateDate.
Enjoy.

update: If you're getting trouble with XmlDocument.Load method because of hexadecimal characters in the view's xml file, look here.

BI | Panorama

Sunday, June 01, 2008 7:20:09 AM (Jerusalem Daylight Time, UTC+03:00)

Comments [0] | Trackback

Panorama's NovaView 5.5 Webinar

On June 10th, Panorama will show us the new version of NovaView - 5.5. The show will be only on the web (that's why it called a webinar). We will see the new reports, flash-based dashboards and the results of the cooporation with Google. You can see the brochure here. I would happy to say that I'll see you there. The only problem is that we won't see each other and that's why I think that a real conference is better than a webby one. On the other hand, it's much simpler and cheaper to do a webinar so I can understand that move. Never mind, I'll see you in other time.

BI | Panorama

Friday, May 30, 2008 6:57:21 PM (Jerusalem Daylight Time, UTC+03:00)

Comments [0] | Trackback

The Leds Map - How it is done

Two weeks ago I showed you the leds map. This time I'll describe how it is done.

The leds map is basically a web page with a lot of java-script and Panorama applets which together bring the user a feeling of Ajax & DHTML based web site.
Look at the picture of the map in the previous post. The leds are simply Panorama applets which show Panorama views. Each view shows only one led. Using the Panorama SDK, I did the following:

Take the led's value from the view and show it in the tooltip
Take the led's color from the view and let the user filter the map according to the desired color(s)
Take the led's view path and after clicking the view, show the related views (the departments' views)

The rest of it is just java-script games and tricks.
The leds map is a beautiful example of what you can do with imagination, thought and good will to give your customer a good working BI tool to work with.

BI | Panorama

Sunday, March 30, 2008 5:27:29 AM (Jerusalem Daylight Time, UTC+03:00)

Comments [0] | Trackback

The Leds Map

A month ago I posted about the necessity of sharing ideas in the BI world. I really think that if we all share our smart ideas then we'll be better in our work.

I want to show you a work I finished few weeks ago. I'm very proud of this work as it will be in the desktop of our CEO and I got many compliments for it.
Note that what you see in the picture is not the real screenshot of the work (It's much more beautiful in the reality...). There's a problem getting out screenshots out of my company, so I did a sketch in Power Point.

This is the functionality of the leds map (my design, if you have any comments):

The leds map is simply a web site, meaning zero-footprint in the client's computer. Some computers in my company has java compatibility problems, so I added a parameter you can send with the site's URL which changes the applet's java version (see more in the next post, which will be more technical).
The leds map has to be small, about a quarter of the screen. That's because it's intended to be a part of the CEO's desktop.
When the map loads, a picture with a turning-around The Thinker statue is shown with a "Loading" message below (our CEO loves that statue...).
After the map has been loaded, the user sees two axis with the leds in them. The two axis can represent any Meta-Measures you'd like: Short-Term Profit Vs. Long-Term Profit, Client's Satisfaction Vs. Company's Profit, etc. This is a point that many people have difficulty to understand, so I'll give an example: The yellow led is in the top-right corner, so that says that the underlying measure is very important in both the meta-measures. Going on with the example, that says that this measure is very important for theClient's Satisfaction and for the Company's Profit. Note that the leds never move. Only their color changes.
When you move the cursor on a measure in the map, a small tooltip appears next to it. The tooltip shows the measure's name and its value (You can see it in the left-bottom led). Design Change: As my team master recommended, now each led has its measure's name above it. The tooltip shows only the value.
When the map loads, only the red leds are shown. In the top-left corner of the screen, there's three radio buttons which filters the shown leds by their colors. In the picture, all the leds are shown because all the radio buttons are enabled.
Clicking on a measure on the map drills-down to the different department's leds, as you can see in the left side of the picture.
Clicking on a department's led makes the map to vanish and instead of it there's a drill-down of the department, meaning that the measures of its sub-departments are shown instead of the map.
After the last drill-down was made, there are two possible actions: Close the new view and return to the map or open the new view in full screen, where you can slice-and-dice and play with the data.

In the next post I'll describe how the leds map was built using the Panorama SDK.

BI | Panorama

Sunday, March 16, 2008 7:55:46 AM (Jerusalem Standard Time, UTC+02:00)

Comments [0] | Trackback

Panorama SDK: Dynamic Change of Views

With Panorama SDK you can do cool stuff as I will show you in the future. Though, There are important (and undocumented) things you must know before you start. A very common task is to change the shown ~~view~~ views. Note that:

If you load the view using the Parameter "Alias" with the full view path (ends with ".xml") you won't be able to change the view. later. Worse: The applet will not return an error. It will just won't respond. So, if you want to enable the dynamic change of the view, in the "Alias" parameter only enter the name of the Briefing Book where the desired view is. Next, add another parameter named "FirstView" and there enter the relative path of the view, meaning that you'll have to remove the name of the server and the briefing book's name. Don't forget to replace the back-slashes (\) in double-back-slashed (\\), otherwise... the applet won't respond. Some examples:

use: AttachParameter("Alias", "http://<myServer>//<ThePanoramaDirectoryPath>//<myBriefingBook>//<myDirectory>//myView.xml"); to show a view with no option to change it later (not recommended). Note that here you don't need to use back-slash because this is just a regual URL.

use:
AttachParameter("Alias","myBriefingBook");
AttachParameter("FirstView","\\<myDirectory>\\myView.xml");
to show a view with an option to replace it later using the CallShowView function.

I recommend always using the second method. That's because you can't know what will be the next demand of your customer. Remember that this is a very common thing to do in the BI world.

BI | Panorama

Monday, February 25, 2008 11:39:22 PM (Jerusalem Standard Time, UTC+02:00)

Comments [0] | Trackback

Do Not Use Special Characters in DW & SSAS

Although SSAS willl let you use them, some other application such as Panorama won't function properly. I'll give one example: When you perform Drillthrough in Panorama, on the fly the engine gererates a web page that will take the user to the next view. The next view will be sliced as the current view, so this web page needs to pass the dimensions parameter (meaning - the current slices). That's why this web page contains this line:

AttachParameters("Slicers","%Slicers%")

A big problem will occur if one of the sliced dimensions will be sliced on a member with a name that contains inverted commas ("). The JavaScript will result an error because there are three inverted commas in the second parameter of the line. This is only one example of what can happen in a BI consumer program if you'll use special characters in member names. So - be careful not to pass these characters from the DW (build the ETL so it will drop these characters) or giving those names in SSAS, such as the All member name.

Tuesday, January 15, 2008 11:08:32 PM (Jerusalem Standard Time, UTC+02:00)

Comments [0] | Trackback

Panorama and big Crossjoins

The Panorama NovaView Desktop program can't always deal with huge crossjoins. The reason is that this program is written in VB6 - very old platform for client programs. One thing you can try is to go to the crosstab properties and in the Advanced tab, click on the "Optimize huge crossjoins". The problem is that this won't always help. The best solutions I've found so far is to go to the Web Access site (or click on the IE button in the desktop program) and there you can choose the size of the chunk of data you'll receive on every click. Starting with 100 rows in the first chunk, this may help you with huge crossjoins.

BI | Panorama

Sunday, January 06, 2008 5:48:11 AM (Jerusalem Standard Time, UTC+02:00)

Comments [0] | Trackback

Showing multiple values in Panorama Dashboards

My team master Yaron asked me to check some things in the Panorama Dashboards:

1. Can have two hands in one gauge.
2. Can I show two values in the text of every gauge.

Here are the answers. I think that the second answer is a beautiful one. In fact, I really enjoyed while I thought how to do this.

1. This is simple: Just use the Goal hand as the second hand. In the KPI Wizard go to the Define Goal step and choose Custom formula. Enter the measure you want to see in the second hand.

2. This is beautiful: In the KPI Wizard, go to the Finish step and to the Title part. Click on the little blue arrow and click on "Edit MDX...". Then, write this MDX:

[My Dimension].[My Hierarchy].CurrentMember.Name + '\n' +
[Measures].[First Measure].Name + ': ' +
Generate({[My Dimension].[My Hierarchy].CurrentMember},[Measures].[First Measure]) + '\n' +
[Measures].[Second Measure].Name + ': ' +
Generate({[My Dimension].[My Hierarchy].CurrentMember},[Measures].[Second Measure])

Note that:

This solution may apply to other BI applications, not only to Panorama.
This way you can show many values and data, not only two values.
What the Generate function doing there? The '+' operator needs to have two strings in both sides, so writing only the [Measures].[First Measure] or [Measures].[First Measure].Value will return a numeric value which will cause error. The Generate function used this way will return a string. It generates for the set (which contains only our member) the value of the measure (in the second argument of the formula) and as mentioned, returns it as string.
'\n' will jump to the next line

BI | MDX | Panorama

Thursday, December 20, 2007 4:18:35 AM (Jerusalem Standard Time, UTC+02:00)

Comments [0] | Trackback

Sort your KPI

I never thought of it until one of my users said it. Sorting the KPI can be a very good idea. Instead of always having the same indicators (gauges, traffic lights, etc.) in the same position of the screen, sorting it can make the viewer expect that the most relevant indicator will be placed in the top-left corner of the screen, the second most-relevant will be placed after it, etc.

Sorting the KPI is a very easy thing. Every end user, even with no clue in MDX can do it by following this:

In the Define KPI wizard, go to the "Select Set" step. Copy the current set. For the example, let's say that the current set is [Products].Members and you want to sort it according to the Sales measure in descending order. Click on the advanced button on the right of the set (The button with the "..." on it) and enter the following MDX statement:

Order([Products].Members, [Measures].[Sales], DESC)

If you want to order in ascending order you can replace the DESC with ASC or not to mention it at all.

BI | MDX | Panorama

Thursday, November 22, 2007 6:24:44 AM (Jerusalem Standard Time, UTC+02:00)

Comments [0] | Trackback

No data in KPI view

If you see "No Data" after you entered a view with defined KPI, most chances that the reason has something to do with privileges, but today I've seen one more thing you can do that will make the KPI go crazy.

One of my users built a view and he removed all the measures but one. After that, when he defined the set of his KPI, he chose the set of the measures. That caused the "No Data" title when he wanted to see his KPI view.

BI | Panorama

Thursday, November 22, 2007 5:45:19 AM (Jerusalem Standard Time, UTC+02:00)

Comments [0] | Trackback

KPI view with no gauges

One of my users had a very weird problem today. When he entered a view with KPI gauges, he could see everything but the gauges. All was there: The titles, numbers, etc. but the gauges itself. The problem occurred in the Panorama Web Access site and also in the dashboard site. I checked with other users and they didn't have this problem (with the same views, of course).

After a few minutes I found the problem: The Explorer process in windows used too much memory and it caused visual problems in the browser. I ended the process, restarted it (Ctrl + Shift + Esc -> File -> New task -> explorer) and everything went back to normal.

BI | Panorama

Thursday, November 22, 2007 5:39:21 AM (Jerusalem Standard Time, UTC+02:00)

Comments [0] | Trackback

Panorama's privileges requirements on the computer

Important note: The user working with Panorama NovaView Desktop must have write privileges on the Panorama folder (The default is: C:\Program files\Panorama). The program saves its data there, so it will cause many troubles if it won't be able to save. For example, when you start the program and click on the globe (work on briefing book from the server) you'll have to enter the panorama's server name every time you start the program (if you don't have the mentioned privilege).

Don't worry: The user watching the views doesn't need to have any privilege on its computer. He only needs the right to see the view or the dashboard page.

BI | Panorama

Thursday, November 22, 2007 5:27:33 AM (Jerusalem Standard Time, UTC+02:00)

Comments [0] | Trackback

The CEO Mode

This very useful option in Panorama can prevent much pain. Often, you don't want your CEO(s) to play with the views you created in Panorama. The beautiful dashboard page you created is what you want them to see and that's it.
In the dashboards site, select the component holding the desired view and in its Toolbar options, check the "Disable Analysis" checkbox. This will prevent the user from slicing and dicing with your view.

BI | Panorama

Wednesday, November 07, 2007 5:10:19 AM (Jerusalem Standard Time, UTC+02:00)

Comments [0] | Trackback

Panorama Hidden Settings Unrevealed

It seems that four days before I wrote my post about Panorama Hidden Settings, Panorama entered all the registry keys into their knowledge base. You can find them here. Strage: I seeked it for a long time and now I see that it was always there, under my nose.

BI | Panorama

Tuesday, October 30, 2007 2:58:13 AM (Jerusalem Standard Time, UTC+02:00)

Comments [0] | Trackback

Showing Member Properties in Panorama

This post is about Panorama because it is the UI tool I'm working with, but this can be made with every BI UI tool.

My customer wanted to get the effect shown by Analysis Services 2005 when browsing a dimension (see the picture below). He wanted to see some properties of the members shown in the rows, along with the usual measures. Unfortunately, Panorama (and I'm sure that also other tools) does not have this option in the GIU. The solution is this code:

Create Member CurrentCube.[Measures].[MyProperty] as
iif(IsLeaf([MyDimension].[MyHierarchy].CurrentMember),
[MyDimension].[MyHierarchy].CurrentMember.Properties("MyProperty"),
Null)

Note that declaring only the third row will cause that every member that is not a leaf will cause an error, which is something we don't want the viewer to see. If the dimension has properties for members in other levels too, you can adjust this decleration. This member can be declared either in the DataBase's Script (after the CALCULATE expression) or inside the session/query (not recommended in Panorama). Now, all you have to do is to show the dimension's members in the rows and this new measure in the columns (after or before the regular measures), and you'll get what you want.

BI | MDX | Panorama | SQL Server 2005 | Analysis Services

Monday, October 01, 2007 4:47:12 AM (Jerusalem Daylight Time, UTC+03:00)

Comments [0] | Trackback

Customize your Dashboard

I'm almost done with my exams, so my writing can continue.

This post is not about how to customize your Dashboard (well, not only about it). Its purpose is to say it loud: Customize your Dashboard!
When the executives of your company (Yeah, I guess you work in a company. Does someone building Bi Portal for himself?) see the customized gauges with their company logo on it, they'll love it. No matter what these gauges will show them, you got their attention and their sympathy for the Dashboards site you made. Now, everything is easier. The bosses are in your hands.

For the Panorama NovaView users:

Follow this link to learn how to do this.
Do NOT start working before you backup your E-BI/KPI folder !
I recommend using Notepad++ or another good XML editor when writing in the XML files. Otherwise, you can mix the whole file and you'll have to start all over again.

BI | Panorama

Monday, September 10, 2007 5:30:52 AM (Jerusalem Daylight Time, UTC+03:00)

Comments [0] | Trackback

Enhanced Security or No Security ?

We've been working for a while to enable SSO in our Panorama's Dashboard site. In a matter of fact, the responsibility for this was under the skilled hands of our system team. After a short time they succeeded and SSO was established in our site. We saw it when we entered the site: Instead of login page we directly entered the dashboard page.
After a few days, when I entered into the settings section of the dashboard site, I saw this:

Yes, that's right. No security at all. This is why we entered directly to the dashboard page instead of the login page...
The system team claims that they never said that the SSO succeeded and we say they did. No one will prove he's right, so there's no one to blame. But blaming is not everything. The important thing here is to learn for the next time: When you think you got a feature - check it. Things not always as they seems to be.

BI | Panorama | Programming | Tips & Tricks

Sunday, August 12, 2007 5:52:53 AM (Jerusalem Daylight Time, UTC+03:00)

Comments [0] | Trackback

Panorama Hidden Settings

For some reason (and don't ask me why), the Panorama NovaView's documentation doesn't contain any information about some of the most important settings. Here some of them, hoping that this will help many users:

In the Panorama Web Access web site, by default a user can save his views (after he made his modifications) only in his private book. To enable him save his views in the Briefing Book (which means - the public book), do the following: Inside the Panorama server, open the Registry Editor (Start -> Run -> regedit) and go to the path HKEY_LOCAL_MACHINE\Software\Panorama\Nova View 5\Admin. Add a new string value named "PublicBookAdmin". As its value, enter all the users you want to give them the option to save their views in the Briefing Book by this template: <User1Domain>\<User1Name>,<User2Domain>\<User2Name>, etc. For example: panoramaDevServer\PowerUser, MSHOME\Miky.
The subscriptions web part will show you only the views that you registered to, but by default no one can register himself to the views. What you need to do is to open the Registry Editor in the panorama server, go to the same path as mentioned above and add a string value named "ShowSubsAndAlerts" with the value 1. After that, every user will be able to right click on any view in the Panorama Web Access web site, click on Register and it will be added to the subscriptions web part for him.
For some users, the loading animations which are shown before every applet appears in the Dashboard website will stay forever, meaning that the user will never see the dashboard itself. I think this has something to do with the Java or Microsoft VM of the user. Anyway, a nice workaround is to cancel this animation. To do this, enter the path C:\Program Files\Panorama\E-BI\Dashboard\include (replace the beginning if you installed to panorama software in a different location) and inside the Config.asp file, change the constant "ShowAnimationWhileLoadingApplets" value to false. This is a good workaroung because anyway, the applets should appear in a second or two. Otherwise - buy a faster server.

As I go on working with Panorama I'll write some more tips & tricks. Stay Tuned.

BI | Panorama

Friday, July 20, 2007 2:20:52 AM (Jerusalem Daylight Time, UTC+03:00)

Comments [2] | Trackback

Project Real

I never thought that I'll do a commercial to Microsoft, but Project Real is a great thing that they did and they should get the credit for it. This project is a full end-to-end BI solution, including ETLs (using SSIS), Analysis Services cubes and mining modules, Reporting Services reports, end-user Panorama views and more.

We work with Panorama as our main GUI tool to show our users the cube's data as tables, charts, dashboards, etc, so this project is really helping us to learn how to implement our project from the first ETL step all the way to the last Panorama step.

Recommended.

BI | Panorama | SQL Server 2005 | Analysis Services | Integration Services

Sunday, July 15, 2007 7:05:54 AM (Jerusalem Daylight Time, UTC+03:00)

Comments [0] | Trackback

Finally, working

For some weeks we were fighting with (or against?) the Panorama software in order to make it work right and show a nice pilot of BI dashboard screen to our managers. After three weeks we managed to show a good opening position by building a nice dashboard screen including a map, graphs, gauges and crosstabs.

I found that the installation of the SQL Server and the Panorama server was not good enough in my company, so I decided to try it for myself. The installation of the SQL Server is quite easy (Next, Next, Next ...), but installing the Panorama server is a complicated process. Paying attention to so many small details, knowing and remembering what to do inside the Windows server, IIS, Windows services, Windows registry and more is not so easy. Finally, after two days I managed to do this. Now I know that some things in the installation in my work place were not so good and I can point them out.

You can see the exciting (for me, at least) result in this picture, as it is a nice dashboard taken from my screen. Many posts about Panorama and SQL Server 2005 will come ahead. Now I can relax - Panorama is on the way...

BI | P

On this page....

BI review & help

Archives

Aggregate Me!

Categories

Profile

more...

Sites I write in

Blogs I Read

Jewish Quote

-Calvin Trillin