Category Archives: Business Intelligence

Screen Scrape with Powershell

I am sure there is all sorts of technical, legal or ethical reasons why you shouldn’t do it, however sometimes its much needed. Powershell is that knight in shining armor which will save your day.

 

In this example I am snatching information from wikipedias SO_3166-1

wiki

as you see in the code is split a very useful information to pin out the information needed, do be aware that your code will fail as soon as the provider changes his format.

web

The code…

$ISO3166 = Invoke-WebRequest -Uri "https://en.wikipedia.org/wiki/ISO_3166-1" -UseBasicParsing

$Countries = ((($ISO3166.Content -split "<table class=`"wikitable sortable`"")[2] -split "</TABLE>")[0]) -split "<tr>" | Select-Object -Skip 2
 
if ($Countries.Count -eq 0) {
 Write-Warning "oh crap no Countries found"
 Continue
}
 
foreach ($Cty in $Countries) {
 
 $country = $null
 $alpha02 = $null
 $alpha03 = $null
 $numeric = $null

 #Decode html
 $country = (($Cty -split "<td>")[1] -split "</td>")[0]
 $alpha02= (($Cty -split "<td>")[2] -split "</td>")[0]
 $alpha03= (($Cty -split "<td>")[3] -split "</td>")[0]
 $numeric= (($Cty -split "<td>")[4] -split "</td>")[0]

 #Special decoding
 $country = (($country -split ">")[1] -split "<")[0]
 $alpha02= (($alpha02 -split "<span style=`"font-family: monospace, monospace;`">")[1] -split "</span>")[0]
 $alpha03= (($alpha03 -split "<span style=`"font-family: monospace, monospace;`">")[1] -split "</span>")[0]
 $numeric= (($numeric -split "<span style=`"font-family: monospace, monospace;`">")[1] -split "</span>")[0]

 #Output results
 $returnObject = New-Object System.Object
 $returnObject | Add-Member -Type NoteProperty -Name country -Value $country
 $returnObject | Add-Member -Type NoteProperty -Name alpha02 -Value $alpha02
 $returnObject | Add-Member -Type NoteProperty -Name alpha03 -Value $alpha03
 $returnObject | Add-Member -Type NoteProperty -Name numeric -Value $numeric
 
 Write-Output $returnObject
}

Unit of Measure in SAP

 

Measures in SAP can be painful to understand, this blogpost dives into the area exposing the logic behind the measures and elaborating how to change from one measure to another. Be warned that there is nothing prohibiting you from converting from meters into kelvin, only your understanding of the usage context will ensure a valid transformation

 

An example of conversion to a SI unit

    1. Simplified formula:

            Measurement unit = (N/D) x SI unit

    2. Complete formula:

            A * measurement unit = (A * N/D * 10**E + K) * SI unit

 

    A = number of measurement units

    N = numerator

    D = denominator

    E = exponent (only required in the case of very large or very small numbers)

    K = additive constant (only required for temperature conversions)

 

 

    Examples

        1. Dimension length

        Measurement unit mm (millimeter)

        SI unit m (meter)

        1 mm = (1/1000) * meter

 

        2. Dimension temperature

        Measurement unit °F (Fahrenheit)

        SI unit K (Kelvin)

        32 °F = (32 * 5/9 * 10**0 + 255,37) * K = 273,15 K = 0 °C

 

Assuming we implemented “Unit of Measure” and “Unit Dimension” as describe in the diagrams attaches to this document then we can transform 1000mg to kg as easy as this

SELECT 1000 AS NETweight,
‘MG’
AS MSEHI

INTO #t

 

SELECT #t.NETweight*1.0/SIConversionDenominator*SIConversionNumerator, ud.SIUnit

FROM #t

INNER
JOIN UnitOfMeasure uom ON uom.MSEHI=#t.msehi

INNER
JOIN UnitDimension ud ON ud.MSEHI = uom.MSEHI

 

And should we want to transform from one measure over a SI to another measure, then we can do like this as explained in this example where I convert the 1000mg to Tonn

 

SELECT #t.NETweight*1.0/uom.SIConversionDenominator*uom.SIConversionNumerator, ud.SIUnit

    , #t.NETweight*1.0/uom.SIConversionDenominator*uom.SIConversionNumerator

                    * uom2.SIConversionDenominator/uom2.SIConversionNumerator

                    ,uom2.InternalUnit

FROM #t

INNER
JOIN UnitOfMeasure uom ON uom.MSEHI=#t.msehi

INNER
JOIN UnitDimension ud ON ud.MSEHI = uom.MSEHI

INNER
JOIN UnitOfMeasure uom2 ON uom2.MSEHI=‘TO’

 

 

 

 

 

 

 

 

PowerShell, a love story

Hi guys

Several times, I have begun the trip into scripting and automatization with PowerShell. For years was on the fence, each time discouraged by the effort and errors thrown at you breaking the curve.

With V3, documentation became easily accessible, introduction videos made enabling me to break the learning with ease.

I am a big fan of scripting trivial and repetitive work. There is tons of such works when doing Data warehousing. Alone generating inferred procedures and putting things into TFS involves tons of repetitive, trivial work so boring that it easily becomes error prone… no more

With my newly acquired PowerShell skills, productivity came, but it did not come alone, it brought a peril. PowerShell running in admin state, really can do everything you do not want it to-do. More than ever is it now important to know exactly what you are doing, and do this on test environments first.

I recommend watching the PowerShell 3.0 Jump Start. It is a bit to nerd loving, but it is still great, you can easily make your own examples out of their teachings.

Another observation is if a thing seems heard, then stop, and seek information. Most things should be easily done, if not you are possibly fighting to take a wrong path.

Prescriptive Analytics

This is the final post in my series on Return on Information. Previously I addressed Descriptive and Predictive Analytics, this time we move into Prescriptive. This type of Analytics is for me especially interesting while it really gives return on the investments in information harvest. This type of analytics helps business managers translate their forecasts and business information into actionable, practicable plans.

Prescriptive Analytics – two approaches

I already spilled in beans in the start of this blog; Prescriptive analytics helps business managers translate their forecasts and business information into actionable, actionable plans. Prescriptive Analytics can be realized after two different main patterns (simulation and optimization). Each of the patterns has its applied usage. The optimization pattern has a wide tactical aim, such as impacts of different forecasts. And a Simulation pattern, which is best when applied to various design scenarios. It can help identify system behaviors under different configurations, and ensures all key performance metrics are met.

Markdown is a great example of this type of analytics.

The intent of markdown is to bridge the prescriptive analytics the actual decision making. It might optimize for different goals, one being that you need your merchandise sold within 8 weeks, still retaining as much revenue as possible. Among many factors is the requirement of optimizing such a scenario a great control over price elasticity, a corporate wide holistic inventory overview, focus on sales frequency, and not least the ability implement prices at the outlets quickly and without any delay

Other examples

In short is prescriptive analytics great applied to pricing, inventory management, operational resource allocation, production planning, supply chain optimization and utility management, sales lead assignment, marketing mix optimization and financial planning.

Imagine within transportation and distribution planning, the ability to analyze based on numerous factors, and still make shift decisions, is absolutely key to operate within that marked.

But also for services, imagine airline ticketing. Their pricing systems use prescriptive analytics to sort through complex combinations and the factors, demand levels and purchase timing to bill potential passengers with the highest price possible, not damaging client retention, or hurt their profit.

Descriptive Analytics

Last in my last post on Return on Information I dealt with Information Sophistication. This time I focus in on how this sophistication could enable us to harvest information from historical facts. Enabling us to create KPI’s, understanding the factors leading to success or failure.

What Descriptive analytics is

In a nutshell Descriptive analytics is effectively a set post-mortem analysis. Almost all management reports falls within this category, they portraits performance factors based on historical data. Their aim is that recipient of the analysis is able to gain some sort of understanding on the reasons behind successes or failures.

Descriptive analytics tend to group up information on relevant dimensions, unlike predictive models that focuses on the individual element. For instance is Descriptive models is great for comparing and creating KPI’s over SBU’s performance.

Predictive Analytics

Last in my last post on Return on Information I dealt with Information Sophistication and how we could overcome the volume of information and reach an optimized scenario, where actions could in operational systems could be acting on a results from Prescriptive advices.


What Predictive Analytics is

Predictive is really looking into the future, based on information trying to figure out the probability of a certain outcome.

There are many different types of models. One of them is a decision tree, where it flip-flops its ways down a number of deterministic questions, leading down to a probability outcome. The most decisive factors at the top of the tree, while less decisive lower down in the tree. In the basis form is most of the Analytical models are limited to represent linear characteristics. Yet there are now more modern tools that introduce machine learning techniques based on artificial intelligence. These tools are capable of including nonlinearities and complex interdependencies.

A good example of predictive Analytics is Predictive Maintenance, heaps actors goes into predicting when equipment failure might happen. Use of predictive maintenance may be decisive keeping production sturdy. It is key to know when to perform scheduled maintenance to avoid production fluctuations and keeping cost down. This is complicated further by the amount of equipment that goes into running a larger operation.

Another good example is Customer Retention. It is key for companies to gain the ability to group customer after their loyalty, in order to targeting them individually when special designed promotions. The clients is scored after key parameters, such as how many purchases he has performed from the company, his net income, areas where he lives, the channel he used to buy etc.

The rational decision-making process

Rational decision-making involves specification of the problem, identification and weighting of involved factors, identification and rating by factor of alternatives and choosing the optimal.

Let’s dive into two of the concepts Probability, and expected value. Probability tells us the likelihood that any particular outcome will occur. A probability of 1.0 represents certainty that an event will occur. A probability of zero represents certainty that an event will not occur. The expected value of every eventuality is summed up by probability and associated with their alternatives also summed by their probabilities. One simple rule for making decision is to always select the alternative with the highest expected value.