Microsoft SQL

Do I need Data Warehouse and SQL skills for Cognos BI?

by

Whether you’re using an IBM Cognos Business Analytics Solution or another vendor’s business analytics solution, you most likely will be needing SQL resources to have queries written.  Many of our clients ask if our consultants know SQL and many of our consultants do know SQL skills for Cognos BI.  However, that does not mean consultants will know your database setup, fields, tables or the integrity of your data.  If your organization is very methodical and has an up-to-date data dictionary that road maps your data warehouse sources, you are very much ahead of the game.  If not, it’s like me driving around your neighborhood to get pizza.  I do have a valid driver’s license, I will obey the traffic laws, but I cannot assure you that the pizza I bring back won’t be from the place you just saw on “dirty dining” or is really the best in town.  Still, do you want to have someone you’re already paying who knows the sources to write the queries, or pay additional for someone to learn the sources and then write the queries?

So with that being said, when planning for a new implementation or development phase, it is best practice to involve your data warehouse resource(s).  Your data warehouse guru should be a part of the design phase, or at least review the design, before the build phase to provide a sanity check of the data assumptions you are planning to use.  Confirm their availability when defining timelines of deliverables.

Each implementation and/or development phase is different, so there are no hard and fast rules.  For some companies your data warehouse guru is in IT, burdened by other projects, and for some others there is a resource within Finance that is available for ad-hoc duties such as this.  The data requirements are a driver too for how much time is needed.  Will there need to have several tables joined?  Does the volume require Views to be set up?  Does the data need to be cleansed? 

In short, your data guru will still be needed but a consultant that knows SQL will be a benefit and add efficiency to your project.

Good Or Great Metadata Modeler?

by

Good Or Great Metadata Modeler

The extremely competitive nature of today’s business world requires business leaders to be constantly evaluating and redefining their processes in order to maintain a competitive advantage. Today’s hot burning question will be tomorrow’s business as usual. Do you have a good or great Metadata modeler?

Is your Framework Manager Model built to answer tomorrow’s questions? Since you are not clairvoyant, the best you can do is to make sure the data is modeled with maximum query power. This includes defining appropriate drill-up/drill-down and drill-through capabilities, providing powerful model filters, and optimizing the model with stored procedures. All of these features are based on identifying and setting up determinants. However, this article will only cover the drilling feature, with additional insight to follow in future articles. But if you are truly clairvoyant, you already know that and need to read no further.

The drill-up and drill-down feature is only available in dimensional data structures such as OLAP. Determinants allow Cognos to generate the appropriate SQL to aggregate facts appropriately, which in turn also prevents double counting. They do not create the necessary metadata to run queries in Analysis Studio or create OLAP functionality. You must also define the corresponding “regular” dimensions to build the hierarchies necessary for drilling.

Drilling allows end users to take hot burning questions into lukewarm territory. This is only the beginning of building a clairvoyant model.

I see additional insight in your near future…so if you want to learn more about a metadata modeler or other software applications, contact us at Sales@LodestarSolutions.com.

Database Documentation Made Easy With APEX SQL

by

Having to create documentation is like having to go to the dentist. You know you have to go and you dread it every six months. Database documentation is similar because everyone dreads it and whenever it is mentioned you can find hundreds of reasons to do other things until your teeth start to hurt.

Well, I have a great dentist for you and your Sql database. The product is called APEX SQL. First, before I give you a review and how to use it, there are few points that I have to make. The product only works if you have a normalized database with proper indices, primary keys/foreign keys, and good object naming conventions. If your database does not conform to a good database standard, then documentation is going to hurt because the nice dentist won’t work on your teeth until you start brushing and flossing on a regular basis. Don’t worry, I will tell you how to make your database conform to a standard good enough to get you in the chair.

You might be thinking there aren’t any real standards out there especially for Sql. You are right, so here are a few rules that I use when creating a database.

  1. 3rd form of Normalization – I try to adhere to this rule to manage the size of my database as well as ensure I only store my data once.
  2. Avoid protected naming – Sql uses prefixes to identify its objects verses objects created by a user. For example you should never prefix your stored procedure with a sp_ because all of the system stored procedures will have the same prefix. The impact is that whenever you call your stored procedure, Sql will look through its own objects first and then to the user objects to execute the object. That might not seem like a big deal, but if your database becomes sizable it will become a big deal. There are enough letters and characters available…use your own.
  3. Primary and Foreign Keys – In a relational database there should be primary and foreign keys assigned. This tells the database how tables are related to one another. It is the core component of creating an ERD (entity relationship diagram).
  4. Indexes – In a relational database, there should be an index on any table that stores large amounts of data. There are different conversations out there about the blessing and curse of indexes. If they are done correctly, they are a blessing because it will speed up the result delivery but may increase the size of your database. The curse is if you index on columns that don’t reduce the need for a full table scan, you have simple made the database larger without speeding up result delivery.

Now that we have some basic standards in place, we can talk about how Apex Sql will help with documentation. It is a server side installation, which means it has to be installed on the same server where the Sql databases exist. Once you have it installed, you can use the wizard and it will go and read every object in the database. If you followed my basic rules, it will create fantastic documentation for you in less time it would take you to rinse out your mouth at the dentist! After it is done, you can use a window scheduler to create your documentation on a routine. How nice it that?! Now, whenever you change your database your documentation is updated automatically.

The best part of Apex Sql is that you can try it before you buy it. Take it for a test run and see how easy it will make your life. Remember that you must have the basics covered in your designed or it won’t work as well.

Everyone will be impressed that your database documentation is always up to date. Get ready for everyone to compliment on your nice smile!

Cognos BI Promptmany Macro Function Error

by

Cognos BI Promptmany Macro Function Error

The promptmany macro function error message is such a letdown. Error QE-DEF-0406 is normally generated when you try to use the function to pass multiple values to a Sql stored procedure. By nature if a stored procedure has parameters, you can only set the parameter equal to a single. This is a problem if you want and need to pass multiple values to a stored procedure. Traditionally, Sql programmers use cursors to handle passing individual values one at a time to a procedure. Of course programmers would rather bite off their arm than willingly write a cursor if there is another viable alternative.

So when you see the promptmany macro function in Cognos, one gets all excited with the prospect of an application allowing you to use an array. Ah Yeah you start dancing cause this will save programming time so you rush to use it in Framework Manager. Framework Manager is the natural selection because you want to pass the prompt values from the report to the stored procedure query subject. From there, Framework Manager will handle passing multiple values to the database. As soon as you go to test your report and use a prompt that requires more than one value you get Error QE-DEF-0406.

According to Cognos, the promptmany macro was not designed to handle an array. The promptmany macro only works in a query (inside a report) and not in Framework Manager. The solution they offer is to write a java script to handle multiple values. The idea is to embed the script into the prompt page of the report and the java script will take over. Good luck with that as I was unsuccessful with the script so I invented my own workaround. My process is much easier and if you are comfortable with Sql this will be a breeze for you.

1. Write a web application using Visual Studio. The web application shows the prompts and then writes the prompt values to a table. When the user click save you generate a Group ID.

2. Use the Group ID in a sub select in your where clause and set your stored procedure parameter equal to the Group ID.

3. Publish the application to your www root directory in IIS

4. Put a link to your web application on your prompt page

Avoid Writing Cognos Report Studio SQL Queries

by

IBM Cognos Report Studio is a powerful tool. It is flexible which makes it powerful and dangerous at the same time. You can create a report based on a Framework Manager Package or go directly to the database. I tend to advise that you should always go through Framework Manager to create your reports instead of building custom Report Studio SQL Queries within the report.

Often, people bypass Framework Manager because they may feel that it is rigid. I will admit that it is rigid and for good reason. Whenever I see custom sql code in a report, it is there for really one reason. The code is compensating for what is lacking in the data, whether it is data structure or data quality. I know some report writers are getting all worked up saying that Framework Manager takes a lot of work to do one thing that can be accomplished faster in a report. I agree, but how scalable is the fast route? If you have another report that needed that same code, you would have to report with the same code that has to be managed and maintained separately. However, if it were maintained in Framework Manager you would have one place to make the change and it would apply to all the reports built on the package. Framework Manager allows you to scale your work and custom sql in a report is more of a one off…or as I like to call it …a run off…they tend to run off with your time.

The point of this conversation is to point out that every tool has its place. It is my position that code in a report is not the best use of Report Studio capability. It merely masks the real issue. Data quality and structure must be addressed at the database level. Any attempt to bypass that work will only lead to more headaches later. Ok one more thing before I get off my soap box, even the most talented report writer would run up and kiss the person that could fix their data. I have been asked to write a post on how to use and leverage Microsoft SSIS to scrub your data and I promise I will get to such a post.

Ok so back to this post. When you get into writing code directly into a report, you have moved into programming and out of report writing. You can write the code in the report, then place that code on the database and call it from Framework Manager by using a stored procedure to build the query subject. After that, you can write your report based on the query subject. This is a much more efficient way to build a report using custom code. The database can handle commands and syntax better than the report and this method is more scalable.

If you lessen the amount of custom work in the report and correct the issues in the database directly, you will see a marked improvement in report runtime and happier report writers.

Microsoft vs Cognos BI implementation – Decisions, Decisions

by

So you're about to start a BI project and have to choose between a specific tool. There are a few major players in the BI world, each of which has pro's and con's and depending on the customer, one may be better than the other. Business needs and flexible functionality should always be a top priority. We will discuss Microsoft vs Cognos BI implementation.

The bottom line is important to many companies in the current economy and can sometimes be an unfortunate final factor in deciding which BI tool to implement. With that said, let's take a look at Microsoft's BI tool SQL Server Reporting Services (SSRS) which is included as part of the licensing for SQL Server purchases. What does this mean to your company who may already have SQL Server in-house? Well, it means that the software is free – woohoo! However, the IT workforce to setup, build, and maintain the BI environment is not. Most importantly, the information for the business users who need it is not readily available with SSRS but rather kept in the hands and control of IT. Don't get me wrong, I'm a full supporter of IT and its core importance in the success of any company. After all, my educational focus was on Management Information Systems and my career is fully IT, techy, computer nerd related. However, any well rounded IT person can tell you that IT exists for the business and putting the right information in the right hands at the right time is key. Management personnel who make business decisions on a day to day basis should have the information needed to make intelligent decisions. It is up to IT to provide the means for management to have access to that information.

Let's go through the generic organizational layouts of a Microsoft (MS) vs Cognos BI software implementation.

Microsoft's product requires that trained IT personnel be on staff to maintain not only the backend data but also create all reports. Why does IT need to create reports you ask? Well, if you can find a few business users who know how to write advanced sql queries, stored procedures, and temp tables then never mind. Otherwise, this means that a staff of BI experts within the IT department accept all reporting requests, prioritize, develop, and then perform QA on business information that they are not familiar with on a day to day basis. Depending on the company, this business hands off approach may work in order to keep a very structured process of providing information. The turnaround time for this process is also much slower and at times issues may arise with company acceptance of information or defining business terms and their calculations.

Now lets take a look at a Cognos implementation. Maintaining backend data will still be kept within the realm of IT as it should be, however the business users are brought into the picture much earlier. Cognos utilizes a data model structure that is located between the backend data and the reporting tools. This is called Framework Manager (FM) and allows a designer to create packages specific to end users needs which contain sources of business information. The FM designer is normally a Business/Systems Analyst type of role that works with IT and end users to create an environment between the two. The Finance department in most companies utilizing Cognos will handle advanced reporting, while other divisions or departments will have access to less advanced reporting studios for ad-hoc information needs. This puts the tool to get information in the hands of the people who need and understand it. Certain administrative tasks can be kept within the IT realm in order to maintain user security and technology standards.

So the real question is, who do you want controlling the information that drives business?

X