bc5ac0a306776ec8e7f9d5a84615b3b1
All posts by Ronald Park
4ea2af8f2b3bb377deccb818cd6ecc90
bc5ac0a306776ec8e7f9d5a84615b3b1
Selecting a BI Package – Proof of Concept
The selection of a BI software package can be a difficult process to go through. There are alot of things to consider when making such a decision such as what your specific needs are as an organization, what skill sets your organization has available for support, hardware considerations, and cost. Here are a few ideas that might be useful when selecting your next Business Intelligence solution.
- Determine what hardware and operating systems the package is compatible with. Have the sales team document and lay this out up front, so you know if you have the skillset, software licenses, and hardware to use the package in the first place.
- Further layout what the additional costs will be for procuring software/hardware, purchasing support, and hiring or training a proper skill set. This will help you get to a total cost of ownership. Understand what the annual support fee structure is like.
- Make sure to walk through the development process. Often times, proof of concepts revolve around software capabilities, but it is just as important to look under the hood. A complicated development process can lead to many problems down the road for development and maintenance. Not all development processes are created equal.
- Every BI tool seems to come with a license for each function of the software. Licensing can get pretty complicated, so make sure you understand what each license covers. It really helps to lay it out in some form of spreadsheet, so you can total up how much licensing will cost based on what you select.
- Understand the capabilities of each package, simplify them into common terms across all packages, and attempt to compare these capabilities, apples to apples. Such capabilities might include OLAP, caching, scheduling, delivery, pixel perfect reporting, dashboarding capabilities, report development interface, user interface, architecting process, SQL generation, etc.
- Understand the technology behind the OLAP that is being delivered and determine if this will serve your organization adequately. Is the technology multi dimensional OLAP or relational OLAP? Multi dimensional OLAP performs faster, but also requires that data be stored physically on the platform. Relational OLAP may be slower, but sits directly on your EDW and usually is more flexible when expanding the scope of data that is to be reported. HOLAP which is a hybrid OLAP utilizes relational OLAP, but also allows for caching of data which can improve performance. Beware that caching may be sold as a multi-dimensional OLAP alternative, but there are limitations to caching’s effectiveness. In the past this was mainly limited by the RAM size of your BI server, but as technology advances, caching becomes more and more useful.
- Is it important to develop a proof of concept on your own data? It is definitely nice to see your data within a sales presentation, but building a POC directly on an EDW will most likely not be possible. Most vendors will ask for downloads of data that they will then store locally on their laptop for demo purposes. Because this is normally the case, the only real benefit of doing this is so that the business can see a presentation in terms they can understand. Don’t expect to gain any other type of insight about a product because they are using your data in the POC….unless they actually decide to develop on your EDW.
Well, that’s all I can think of for now. Cheers.
Triggering Narrowcast Jobs from Command Prompt
When I was a project manager, the developers that I worked with setup the Narrowcast jobs to kick off automatically once the ETL loads had completed. Well, I recently had to figure out for myself how to do this, and after piecing all the technologies together and reading through a few MicroStrategy support notes, I felt it would probably be a good thing to document in this blog. The below example was performed using a Windows NT Narrowcast server.
Well, triggering Narrowcast Services, first off can be done through MicroStrategy Command Manager. If it is not installed on your Narrowcast box, first go ahead and install the application there.
You will then need to log on to your Narrowcast Meta data from Command Manager in order to test your connection. First off, your Narrowcast implementation and Command Manager install needs to be at the same support level, so I had to first upgrade my Narrowcast application. Second, you have to setup a system DSN for your Narrowcast Metadata. From there, you can log on to Narrowcast Metadata from Command Manager. Here is some sample Command Manager code for kicking off a Service:
TRIGGER SERVICE “Service Name” IN FOLDER “Applications/My Applications/Services/”;
In Command Manager, I then save this code into a .scp file. In order to kick off this Command Manager file, we then create a windows .bat with the following commands:
if not exist c:\cmdmgr\trigger.txt goto 20
cmdmgr.exe -w SQLServer -u user -p pass -d master -s dbo.PROD -f c:\cmdmgr\job.scp -o c:\cmdmgr\output.txt
del c:\cmdmgr\trigger.txt
goto end
:20
:end
This windows first checks for a trigger file (that will be supplied by the ETL team). If the trigger file exists, then it kicks off the .scf script and then deletes the trigger file. If the trigger file does not exist, then the .bat files does nothing. Shown below is the command manager line explained further:
If you are having problems with the script, just test the cmdmgr.exe line in the .bat file. You should be able to doubleclick the .bat and the service should run almost immediately.
Once you have all the above setup, it is just a matter of scheduling the .bat file to run every so often (15 minutes in our case). I have yet to find a true trigger solution using Narrowcast. I always have to setup a job which runs repeatedly until a flag is set, and then the job kicks off the actual report service. Whether this flag is a database flag or a trigger file, the word ‘trigger’ is misleading because the report service is not really being triggered by the ETL load completion. A easier solution for triggering Narrowcast jobs would be a very nice to have in one of Narrowcast’s later releases.
Just FYI, in order to schedule the .bat file to run every 15 minutes, I went into Settings->Control Panel->Scheduled Tasks, and just created a Scheduled Task using the wizard. Click on the Advanced push button to select a schedule that is more frequent than just once a day.
This was not rocket science, but just thought it would be something nice to document.
Logical views with MicroStrategy
Business intelligence architects have always relied on the Datawarehouse team to create database views or perform datawarehouse changes in order to construct the BI architecture. If something was not architected as desired, then the BI developers were forced to request these changes, and then wait for their completion before moving forward. Since MicroStrategy 8, BI developers have had the ability to create logical tables and table aliases to circumvent this process. This article will go through various examples on how this can be used on an implementation.
Lookup Tables and Reuse of Dimensions – The most obvious reason for requiring logical tables or aliases is for the creation of lookup tables or reuse of dimension tables. First off, the difference between an alias and a logical table is that an alias is an exact copy of a dimension table, and if the dimension table or alias changes then both will change. The logical table on the other hand is a custom SQL statement that utilizes any table available to the BI developer. Lets say we have a fact table F_FACT, that stores 3 products per transaction, but the data model only has one D_PRODUCT table. Aliasing allows us to alias D_PRODUCT into D_PRODUCT_1, D_PRODUCT_2, and D_PRODUCT_3, so that we now have 3 separate tables to join to F_FACT. Now, lets say that the 1st product is a laptop, the 2nd product is a battery, and the 3rd product is an AC Adapter. We could still join F_FACT to the 3 aliased product dims, but we also want only laptops to show up in D_PRODUCT_1, only batteries in D_PRODUCT_2, and only AC Adapters in D_PRODUCT_3. This is where logical tables can come in to play. First, lets create a logical table called D_PRODUCT_LAPTOPS which we create with the following SQL statement, ‘SELECT * from D_PRODUCT where PRODUCT_TYPE = “LAPTOP” ‘. Quite simply, we have created our own dimension table of laptop products without additional support from the EDW team.
Case Study: Using Logical Views to make a Fact table from a Dimension – Lets say we have a dimension table called D_EMPLOYEE with the fields employee_key, employee_id, employee_name, record_start_date, record_end_date, employee_start_date, employee_end_date, and organization.
Now, lets say we want to be able to count Active and Terminated employees from a fact. Our first problem is that D_EMPLOYEE is a type 2 dimension, so we need to pull only the latest record on this dimension. For the metric, we have the option of building the count of active and terminated employees either on the fact table or by building a fact object in MicroStrategy. For this demonstration, we will build the counts on the fact itself.
In order to pull the latest record from D_EMPLOYEE, we will use the following WHERE condition: WHERE RECORD_END_DATE = ‘12/31/9999′ or RECORD_END_DATE = EMPLOYEE_END_DATE which will pull the latest record for each employee.
For the facts, we will add the following fields to the SELECT part of the query: CASE WHEN RECORD_END_DATE = ‘12/31/9999′ THEN 1 ELSE 0 END as ACTIVE AND CASE WHEN EMPLOYEE_END_DATE is not null THEN 1 ELSE 0 END as TERMINATED.
The Final Logical table will look as follows, and now we have a new fact table.
Case Study: The POOL technique – Once you start to use Logical table , you will start to see that this can be a very powerful tool that can accomplish a great deal, as long as performance allows it. Please note that these table Views just serve as queries on the database, and are not materialized in anyway. What I often do after creating a complicated logical view is run it through a query tool like Toad. If it does not return results immediately, then I send the query off to our DBA to see what they can do.
The POOL technique (which I’ve named it) is actually a technique for developing complex SQL reports when there are no data warehouses or BI Tools. The point here is that you can use this technique to develop a complex piece of SQL, and then use that SQL in a MicroStrategy logical view, so you can build metrics and attributes on top of that SQL. This technique is also great for developing Freefrom SQL queries in MicroStrategy.
The concept is simple. First develop a query that gets you as far as you can with the base tables, and then use that query as the base table for a new query. Repeat until you have what you need. One other point is to try to avoid Sub Selects as much as possible because they can seriously slow down performance.
Lets use the table D_EMPLOYEE above and the logical view F_EMPLOYEE as our basis. The report we want to create is a list of active employees, their current organization, and the number of organizations they’ve been in. If the number of organizations is greater than 5 then we want to mark this record as a MOVER.
First, we build SQL to get as far as we can. I don’t put the number of Organizations in this query for illustrative purposes, but do you see how to calculate number of Organizations in this query without a sub Select?
Next we use the statement just created as the base table for the next select, and name this base table POOL. We then create a query based on POOL which now calculates the Number of Organizations.
Now we use the SQL statement above for our base table, and call that table POOL2. We do this to add the MOVER field to the report.
We can keep going if we had more complex requirements, but the point is that this technique can be used to develop complex queries, which we can then plop into a MicroStrategy logical tables. Maybe we will create an attribute called MOVER? The options are now far greater with logical tables.