This view tells us the % of orders that use shipper 1 by employee. The ratio analysis divides a SUM by a COUNT of the same field and then uses a CAST to turn it into a percentage on a floating point scale.
CREATE VIEW OpenPercentage
SELECT LastName, CAST(SUM(CASE ShipVia
WHEN 1 THEN 1 ELSE 0 END)
AS FLOAT)/COUNT(ShipVia),COUNT(ShipVia) AS [Count]
FROM [dbo].[Orders]
JOIN [dbo].[Employees]
ON [dbo].[Employees].[EmployeeID]=[dbo].[Orders].[EmployeeID]
GROUP BY LastNameWhile business intelligence is generally applied at an aggregate level to large data sets, it’s often useful to provide a more streamlined insight into an individual records or to be able to sort and rank them. For instance, a salesperson looking at a specific customer could benefit from basic stats on that account. A marketer trying to define an ideal customer could pull the top entries and look for insights or patterns. Inner queries let you do sophisticated analysis without the overhead of traditional BI or OLAP technologies like Analysis Services. Read More
Many consider bull sharks to be one of the world’s deadliest creatures. Despite their awesome prowess and speed, bull sharks are nearly blind in the deep, dark waters of the ocean. Rather than identifying friend, foe and food by inspection, they literally bump into and bite things they are interested in to determine what they are. What if they bite into something bad like barrel of oil or one of the few creatures they can’t defeat in a fight? There are consequences to this approach and the shark is really just hoping that the interesting object will behave well.
Read More
One of the most effective ways to better understand an organization to visualize events over time to see how they are trending and how they compare to one another. Here’s an example that illustrates a multi-year comparison. This could be used to tell a story of what has happened and help decide a plan of action.
Order data from the Northwind database.
Most databases are optimized for quickly saving and retrieving individual records that drive an application. This can create some challenges when it comes to reporting in a way that delivers insight into the data. Now there are many complex technologies like ETL data warehousing and analysis cubes that try to solve this problem, but often require far more effort and overhead than it’s worth. Many of the things these complex tools do can actually be done with a simple view. Read More
In Part 1, we used UNION to combine multiple queries in order to create a view for a Executive Dashboards. It displayed the top five countries as well as an additional row to give you some context by adding up the rest of the countries. In Part 2 of this article, we will use a CASE statement to do a logical transformation of the Country field in order to get the same result. Read More
We often see situations where different members of the user community will need different ways to do the same thing. One example is generating an Excel (or CSV) file. While it would initially seem that this is a single feature, there are myriad different reasons for someone to do this. Salesforce.com, for instance, gives you three different interfaces that essentially do the same thing but are optimized for different situations. Software applications that require advanced reporting and data exports would be wise to follow their example. Here are the three main reasons users generate Excel or CSV files: Read More
We recently added the ability to combine the bottom slices into a single “Other” slice. This worked so well that a customer wanted to use it for many other parts of their system. Now, our “Other” feature works well for a pie chart because there are a very small number of slices anyway. We needed something that could work on billions of records and quickly summarize the top 5 countries, while also giving the sum of all remaining countries as a single row.
In the late 90’s application service providers started leveraging the availability of abundant bandwidth and the popularity of the web to deliver applications in an ASP model. Essentially the application would be hosted on a shared server and managed by the ASP. While this was a step in the right direction, there were still significant challenges with this approach. These models evolved into what is now knows as Software as a Service or SaaS. SaaS architectures introduced multi-tenancy which allowed pools of high powered resources to be shared in a different way. Rather than customers having their own web server and database, a shared infrastructure served dozens or sometimes thousands of customers. Read More
Copyright 2021 Izenda, Inc. | Privacy Policy | Sitemap