To test my Tableau knowledge, I attempted the Tableau product certification and got the “Tableau Desktop 8 Qualified Associate” certificate.
In this post, we will a common metric used in inventory management called Inventory Turnover
In simple terms,
Inventory Turnover = Sales / Inventory
why do we want to measure this?
A business manager can analyze this metric to figure out the efficiency of sales and efficiency of buying.
A high over turnover equals strong/efficient sales OR inefficient buying process. It can also show loss in business due to lack of goods supply.
A Low turnover equals inefficient sales or marketing efforts and excess inventory.
How do you benchmark inventory turnover?
usually, it’s bench-marked against Industry average. You don’t want to benchmark a company selling Auto Spare Rates versus a company selling dairy products because company selling dairy products (perishable goods) would have a high turnover ratio since they move inventory fast.
This was a high level discussion of a business metric “Inventory Turnover” commonly analyzed by business managers to keep an eye on their sales and buying efficiencies. of course, the use of the formula would involve interviewing business managers to understand how they measure inventory turnover but whatever the formula may be it should ideally be consistent across the organizations.
Here are some links if you want to research further:
Data Explorer let’s you “Explore” (search) for web-based public data. This is a great way to combine data that you may have in your data-sources with public data sources for data analysis purposes. Sometimes your data might not tell you the reason behind the observed trends, so when that happens – you can try to see if a public data-set might give you the much-needed context. Let me give you an Example before we start hands-on w/ data explorer so that you have better understanding of importance of public datasets. Here’s a sample that I found here. So, Here’s a demo:
An auto company is seeing sales trends of Hybrid cars and SUV’s from the sales data-sources. But what is the reason behind that? company data does not show that. Someone hypothesizes that it might be because of gas prices. So they test out the hypothesis by combining gas prices information available via public data. And turns out gas prices might be the driving force of sales trends! SEE:
if the gas prices increase, then the sale of SUV go down and the sale of Hybrids go up:
You know that public data can be helpful! So how can you search for public data-sets? Well, You can manually search online, ask someone, browse through public data repositories like azure data market (and other data markets), there’s also a public data search engine! OR you can directly search for them via Data Explorer.
Here are the steps:
1) Excel 2010/2013 > Data Explorer Tab > Online Search > type “Tallest Buildings”
3) Now let’s do some filtering and shaping. Here are the requirements:
- Hide columns: Image, notes & key
- clean columns that has heights data
- Show only city name in location
OK, let’s get to this one by one!
4) Hiding Columns:
Click on Filter & Shape button from the Query Settings:
Select Image Column > Right Click > Hide:
Repeat the steps for notes & key column.
Click on DONE
5) clean column that has heights data.
Click on Filter & Shape to open the query editor
A) let’s rename it. Select column > Right Click > Rename to Height > press ENTER
B) let’s remove the values in brackets. Select Column > right click > split column > By delimiter > At each occurrence of the delimiter > Custom and enter “(” > OK
Hide height.2 and rename the height.1 to height
Click on DONE
6) Let’s just have city names in the location column
click on Filter & shape to load query editor:
A) select location > right click > split column > by delimiter > Custom – Enter: ° in the text box like this:
Hide Location.2, Location.3, Location.4 & Location.5
Select Location.1 > Right Click > Split Column > by Number of characters > Number of characters: 2 > Once, as far right as possible > OK
Hide Location.1.2 and rename Location.1.1 to Location
One last thing! making sure that the data type of height is numbers.
Select height > change type > number
Select floors > change type > number
click on DONE. Here’s our filtered and shaped data!
For the purpose of visualization I copied first 20 rows to a separate excel sheet and created a chart:
That’s about it for this post. Here are some related Posts on Data Explorer:
Unpivoting data using the data explorer preview for Excel 2010/2013
Merging/Joining datasets in Excel using Data Explorer add-in
Remove Duplicates in Excel Tables using Data Explorer Add-in
Web Scraping Tables using Excel add-in Data Explorer preview:
Your comments are very welcome!
In this blog post. I’ll help you get started w/ implementing dynamic security with row filters in Tabular Models.
We’ve users that connect to a Tabular Model via Excel for Data Analysis purposes. One of the analysis that they do is Countries VS. Total Margin:
What we want to do is restrict someone from Europe to see data only about France, Germany and United Kingdom
1) Open Tabular Model in SSDT (SQL Server Data Tools)
2) Toolbar > Model > Roles
3) Role Manager > NEW > change Name to Europe and Permissions to Read
4) Under the Row Filters, for the Geography Table, enter the following code:
=[Country Region Name]=”France” || [Country Region Name]=”Germany” || [Country Region Name]=”United Kingdom”
How to edit code for your scenario? change the [country region name] to your column(s) and also change the values
5) Click OK
6) Now let’s test it!
7) Toolbar > Model > Analyze in Excel
8) Select the role Europe
10) From Pivot Table, Select Margin & Countries:
11) As you can see, since the role Europe was selected for testing purpose in step 8 – ONLY France, Germany and UK data is shown in our test! This means that our row filters are working perfectly!
I hope this tutorial helps you get started on implementing dynamic security in Tabular models.
SPEED is one of the important aspect of Data Analysis. Wouldn’t it be great if you query a data source, you get your answers as soon as possible? Yes? Right! Of course, it depends on factors like the size of the data you are trying to query but wouldn’t it be great if it’s at “SPEED OF THOUGHT“?
So Here’s the Problem:
Databases are mostly disk based and so the bottleneck here is the speed at which can get access to data off the disks.
So what can you do?
Let’s put data in a RAM (memory) because data-access via memory is faster.
If it’s sounds so easy, why didn’t people do it earlier? And why are we talking about “In Memory” NOW?
1) BIGGER Data Size/sets and so today with more data, it takes more time to query data from databases. And so researchers have looked at other approaches. One of the effective approach they found is: In-memory
(And I am not ignoring the advances in Database Technologies like Parallel databases, But for the purpose of understanding “Why In-memory”, it’s important to realize the growing size of data sets and a viable alternative we have to tackle the problem: In memory. And also I am not saying that it’s the ONLY way to go. I am just trying to understand the significance of in-memory technologies. We, as data professionals, have lot’s of choices! And only after evaluating project requirements, we can talk about tools and techniques)
2) PRICE of Memory: Was the price of RAM/memory higher than what it is today? So even though it was a great idea to put data in memory, it was cost-prohibitive.
What’s common between Microsoft’s PowerPivot, SAP HANA, Tableau and Qlikview?
1) Tools for Data-Analysis/Business-Intelligence 2) Their Back End data architecture is “In Memory”
So since Data Analysis needs SPEED and In-Memory Technologies solves this need – Data Analysis and Business Intelligence Tools adopted “In-memory” as their back-end data architecture. And next time, when you hear a vendor saying “in-memory”, you don’t have to get confused about what they’re trying to say. They’re just saying that we got you covered by giving you ability to query your data at “speed of thought” via our In-memory technologies so that you can go back to your (data) analysis.
That’s about it for this post. Here’s a related post: What’s the benefit of columnar databases?
your comments are very welcome!
Dr. Steven Levitt’s (Indiana Jones of economics & Author of Freakonomics) work involves finding insights from data. In the keynote, he shared some of the interesting & fun insights that he found from data.
One Example: Dr. Levitt: According to the data, It was 7 times more dangerous to sell crack in Chicago than it was being in combat in Iraq. https://twitter.com/markvsql/status/322707949158006786
He also talked about other insights that he found which could also be found in his book Freakonomics. After getting audience fascinated about what analyzing data can do – he moved to his real world experiences of analyzing data for businesses. And tied all these fascinating insights back to some tips he had for the audience. Here is a brief recap of the tips he shared:
*Above text is linked to tweets.
That’s about it for this post. What do you think about the tips that Dr Levitt shared?
Data preparation (or call it pre-processing) is an essential and time-consuming part of any data analytic’s project. To that end, I was working on a data set needed some changes before I could plot it on an effective data visualization. Here’s what I did:
I was working on a data set that looked like this:
|Date||Abu Dhabi, United Arab Emirates||Adalaj, Gujarat, India||Addison, TX|
BUT: I wanted my data to look like
|Abu Dhabi, United Arab Emirates||1||1||1||3||2||2||2||2||2|
|Adalaj, Gujarat, India||4||4||4||3||2||3||3||2||2|
it has 380 columns and 500+ Rows and so MANUAL copy pasting was NOT an option!
It’s so simple!
Step 1: Select the data > COPY (Shortcut: ctrl + c)
Step 2: Switch to a new/different excel sheet
step 3: Paste Special > Transpose (T)
So After doing this, This is how the Input & output looks:
In this post, We saw how to swap or reverse the axis of a table data in Excel 2010.
One of the key thing I’ve learned is importance of differentiating the concepts of “Data Reporting” and “Data Analysis”. So, let’s first see them visually:
Here’s the logic for putting Data Reporting INSIDE Data Analysis: if you need to do “analysis” then you need reports. But you do not have to necessarily do data analysis if you want to do data reporting.
From a process standpoint, Here’s how you can visualize Data Reporting and Data Analysis:
Let’s thing about this for a moment: Why do we need “analysis”?
We need it because TOOLS are really great at generating data reports. But it requires a HUMAN BRAIN to translate those “data points/reports” into “business insights”. This process of seeing the data points and translating them into business insights is core of what is Data Analysis. Here’s how it looks visually:
Note after performing data analysis, we have information like Trends and Insights, Action items or Recommendations, Estimated impact on business that creates business value.
Data Reporting ≠ Data Analysis
Do you know about Jeopardy! quiz show where a computer named Watson was able to beat world champions? No! Go watch it! Yes? Nice! Isn’t it a feat as grand as the one achieved by Deep blue (chess computer); if not less?
I am always interested in how such advanced computers was built. In case of Watson, It’s fascinating how technologies such as Natural language processing, machine learning & artificial intelligence backed by massive compute & storage power was able to beat two human world champions. And as a person interested in analytic’s and Big Data – I would classify this technology under Big Data and Advanced Data Analytics where computer analyzes lots of data to answer a question asked in a natural language. It also uses advanced machine learning algorithms. To that end, If you’re interested in getting an overview of what went into building WATSON, watch this:
If you’re as amazed as I am, considering sharing what amazed you about this technology via comment section:
Lately, I have been talking to few business folks who do their own data analysis in excel (2010) and sometimes they run into the excel 2010 limit of 1 million rows. And so when I hear that, I talk about Power Pivot and I talk about what It can do and what it cannot and they are just amazed that there’s a FREE add-in that will help them crunch more than 1 million rows!
You can explore more about this amazing add-in here: http://www.microsoft.com/en-us/bi/powerpivot.aspx
And Read more about pros/benefits of PowerPivot: