LinkedIn
email
Custom Icon

Posts by Ziv Himmelfarb

  1. Home
  3. Ziv Himmelfarb

DATABASE VIEWS – WHAT ARE THEY GOOD FOR?

Ziv Himmelfarb
1 Comment
Oracle, MS-SQL, MySQL, DB2, PostgreSQL, and almost any other type of relational database – they all support a database object type called VIEW. In this article I’ll explain what is a view and list a few scenarios where using views can help. WHAT IS A VIEW? In simple terms, a view is a query. It can be almost any query, reading from one or more tables, sort of like a script that is stored inside the database engine. The query text is stored, not the data, and therefore it takes very little space (just the length of your query text). Once created, the view can be used like any table – it can be queried alone or in a complex…

Same Period Previous Year

Ziv Himmelfarb
No Comments
One of my favourite ways to show a value over time is to display it on a column chart alongside the value from the same period a year before.  It allows the users to both see the trend over time and be able to immediately compare to the same period the previous year, without scrolling left and right too much. To do that in Power BI, I’m Assuming you have a Date dimension called ‘Dim Date’, with a date column called ‘date’. Here’s how the ‘Revenue Same Period Previous Year’ measure should be built: Revenue Same Time Previous Year = CALCULATE(SUM(‘Fact Sales'[Revenue]), SAMEPERIODLASTYEAR(‘Dim Date'[Date]),All(‘Dim Date’) ) Hopefully that’s simple enough to just copy&paste, and change column names. Let me know…

Renaming a Column in a Power BI Data Source

Ziv Himmelfarb
3 Comments
Renaming a column in Power BI is easy. All you need to do is double-click on a column name and edit it. If you do that, the only thing that changes, is the name of the column in your Power BI model. It doesn’t change your data source. The change happens in your Power BI model only.  For example, If in the database there’s a column called “NumberOfTickets”, you’re likely to rename it “Number Of Tickets” in Power BI, to make it more readable for your users. But, what if you need to rename a column in the database or file from which you load the data into your Power BI model? Can you safely go ahead and change the…

Power BI Financial Year Reporting

Ziv Himmelfarb
2 Comments
Power BI’s strong data modelling language, DAX, includes many functions that make it easy for report authors to report on revenue/sales/cost or any other numeric KPI sliced by years, quarters, months, weeks etc., and allowing to compare periods like MTD (Month To Date) or YTD (Year To Date) to the same period last year. This is very handy and very common, as many businesses want to be able to compare their performance between similar periods over time and visualise the trends. At the same time, many organisations do not use the standard years we all do, but rather use financial years or fiscal years, that often start and end on different time frames. Two of the more popular options I’ve…

Pentaho Data Integration (PDI, Kettle)

Ziv Himmelfarb
No Comments
ETL Products In my previous article I talked about ETL and ELT, and today I want to talk briefly about a specific ETL product in the market. This article assumes basic knowledge about what a data warehouse is, and what are ETL and ELT. If you are not familiar with these terms, please start by reading this and this. This product is the open-source, community edition of Pentaho Data Integration, also called PDI, or Kettle. There are plenty of expensive ETL products in the market, from the likes of Industry leaders like IBM, Oracle and Microsoft, but not many cheap or free options. An ETL product is the one part of a BI solution where if you are on a small…

Introduction to ETL and ELT

Ziv Himmelfarb
No Comments
Introduction A Data Warehouse is built by collecting data from different data sources, and transforming it to a structure that’s easy to analyse and report on (typically a dimensional model). In order to build a data warehouse, we need to write code, or use tools that allow us to perform ETL: Extract, Transform and Load. These processes Extract data from source systems (databases, APIs, files), Transform the data to the data model we use in the Date Warehouse (usually a dimensional model), and Load it to the Data Warehouse. Another approach is ELT, in which we Extract data from source systems,Load it as-is to a staging area in the Data Warehouse, and then Transform it by running transformations processes inside the data warehouse. E, L…

AWS QuickSight Review

Ziv Himmelfarb
3 Comments
Introduction QuickSight is a BI service by AWS, that allows you to easily connect to many types of data sources, analyse your data, create visualisations and share them with your users and co-workers. It’s a server-less service running in Amazon’s cloud, and everything, including authoring reports, is done in the browser. In other words, this is Amazon’s response to Power BI and other cloud-powered BI services. It has launched at the end of 2016, and is, as far as I know, the cheapest BI product out there. Pricing, for readers is 0.30$ per session of 30 minutes, and capped at 5$ per month per user. For report authors, it’s 24$ per month/user, or 18$ with annual subscription. I’ve recently spent some…

Power BI: Row-level security while sharing with external users

Ziv Himmelfarb
13 Comments
One of the nice benefits of using a cloud based BI service such as Power BI, is the ability to easily share content with users outside the organisation. This is very different than the old school BI solutions that are installed on-premise, and require access to your network. Another Power BI feature is RLS – Row-Level Security, which allows the publisher to share subsets of the data with different users. For example, allow sales representatives in Canada to see only Canadian sales data. In this article, I’ll talk about using both features together- sharing a subset of the data (using RLS) with users outside the organisation. You would think all you need to do is just use both features, however my…
Menu