Composite Models

Imagine having your Data Model ready and everyone loves it.
Then you get this question all of a sudden.

“I have made this excel file and I would like to use and combine it with the Sales Dataset in PowerBI.”

The next thing you do is analyze the request and for some reason, maybe you don’t want to flood the Datawarehouse with Excel Files or maybe your ETL team is too busy with requests, but you have to say no.

The business user is quite disappointed because it would really have solved that time-consuming task he/she had to do every week.

What if I told you that all the above can be avoided with a very simple solution?

Composite Models are the answer!

These models are a way to seamlessly combining different data sources in PowerBI.

With composite models you can connect to your Azure Analysis Service (AAS) model or Power BI dataset and enrich them even further by connecting to a second model, adding flat files, or even just the ability to build measures on top of your AAS model.

This is all super exciting for both business users and BI developers as it will create even more analytical opportunities.

Now, I know what you are thinking.
Let’s take a look at this feature!

I love your eagerness but before we dive into it, you should know that this feature has the following Limitations.

The following Live Connect multi-dimensional sources can’t be used with composite models:

  • SAP HANA
  • SAP Business Warehouse
  • SQL Server Analysis Services
  • Power BI datasets
  • Azure Analysis Services

 

As of December 2020, DirectQuery for PBI and Analysis Services is available in Public Preview.
If you would like to use Composite models with PBI Datasets and AAS you should enable this feature.

Now, with that out of the way, let’s get to the exciting part!

In our Example, we will be using a live connection to a PBI Dataset and an Imported Excel File.

First of all we will be connecting to the PBI Dataset we want to use.
Once this is done and you are all ready and eager to add a different source to the model, or to just add your own calculated columns, you will need to go to Transform Data.

You will be prompted with the above information.

As stated previously a DirectQuery Connection is required. This is the reason the DirectQuery for PBI Datasets and Analysis Services was required.

It will also notify you that this change is permanent. You cannot change this back once performed and saved. This is very important to keep in mind.

When we are ready to continue we can click on the Add to Local Model button and a DirectQuery Connection will be made.

You know it will be successful when you see the following text in the bottom right corner.

Now we can do quite a few more things more we couldn’t before to the already existing model.

For example, if you previously right clicked on a table you got the below options.

However, now we can actually create new Columns along with renaming/hiding Tables, Measures and Attributes and create relationships.

This last one will be very handy when adding different sources to your model.

Now we can actually add a different source file to our model. This can go from Excel or Sharepoint files to different PBI Datasets.

Once you add another data source you will once again be prompted with a message.

Of course, when combining different data sources through DirectQuery there is always the possibility that data can be included in queries sent from one data source to the other.

Always be certain of the sources you are pulling your data from.
Once you are sure this is no issue for you, we can continue.

Both models will now exist separately from one another and can be joined through a relationship you define if required.

Different visuals can now be built using data from both (Or more) sources.

All of this is amazing and a great leap forward for both developers and advanced business users.

PowerBI is a tool that keeps improving and each iteration brings in more capabilities to deliver high-quality dashboards to the end-user in a short amount of time.

If you are interested in getting to know more about Composite Models or other Microsoft BI-related aspects you can always contact Lytix.

 

 

One Year at Lytix – Data Consultant Tibo Vandermeersch

I’ve been working at Lytix for one year by now and I thought it was interesting to share what my experience was during the first year as a Data Analytics Consultant at Lytix.

Training

 

After the first day, I and other starters immediately jumped into a training program that Lytix put together. Me just starting out as a Data Analytics Consultant needed this of course.

During this training, I learned and refreshed not only hard skills that are needed to execute my job but also soft skills that are involved with consultancy.
People coming straight out of school will start in a group of starters that all follow this training simultaneously.
Most of the trainings are also given by people from Lytix themselves which give you, as a new person, already a person to contact if you need guidance or expertise in a certain field of knowledge. And people here invite you to do so as well.

The training has definitely proven to come in handy. It’s also a good way to gauge the kind of knowledge and expertise that lives in Lytix.

 

Working experience

 

Even during the training period, I was already deployed at a customer under the guidance of a more senior profile which allowed me to learn more on the job.

At Lytix they try not only to find the right person for the job at the customer but also the right customer for the person doing the job. Of course, it’s not always possible to do this but when it is possible, they think of the most suitable place for you.
I personally have been very happy with the customers I got to work with. I felt comfortable doing my job and also felt like I learned from my customer. You won’t only get feedback from your colleagues at Lytix but you often get it from your customer as well which is something I appreciate a lot.
I like to go about feedback in the following way: “There is no such thing as bad feedback. It’s just that way it’s brought over that can be bad”. Don’t be afraid to ask the customer if you have any working points. Approach it in an open-minded fashion.

Lytix gives you opportunities to learn and get experience pretty fast. This allowed me to work with the data of 7 different customers in one year. Working in these many contexts learned me to go about different kinds of expectations, understanding a dataset you don’t understand yet, different ways of working, asking the right questions in the first phases of the project, ….

 

Development

 

At Lytix there is a lot of room for development. Whether it’s something small or big. As long as it’s job-related they would stand behind your proposition if you have one.

There are regular check-in moments to figure out where your personal interests are going in terms of self-development. Next to that, they could always recommend you something in terms of development that might suit you. Being self-reflective doesn’t always mean you have to find all answers yourself to what you should do. Simply asking someone what they think would be interesting for you is also a form of that.
I am for example someone who learns best by doing something and figuring it out which gets put to good use at Lytix. I sometimes get challenged by my customers and at those moments I feel most that I am growing professionally. This gives you the opportunity to gain more knowledge.
And again. When you work with a customer that plans in a moment for personal feedback with you, I appreciate that so much when they do that. It tells you a lot in how you are fulfilling the expectations but also what you can work on for yourself. They know that I’m young and don’t know everything but they let you know what you can work on based on their experience. This allows you to also prevent mistakes.

There’s a lot to learn at Lytix. Not only about job content but also about yourself. I personally have learned that I’m better at certain things than I initially thought for example.

 

Teamwork

 

Say that you’re at a customer and they ask you about something in which you have zero experience. You can always go ahead and ask someone at Lytix if they can help you out. Eventually, you will get there.

The people at Lytix and Cubis (= Lytix’s sister company) know that not everybody can do everything. That is why it is easy to contact someone with more expertise in a certain subject. At the most recent project, a customer asked which option was better; connect a customer portal with SAP directly (and how would one do it) or connect SAP with the Power BI portal and connect the customer portal to that. Me not knowing a lot about SAP went on a search at our sister company, Cubis, to find someone who could. Within a day a meeting was scheduled and an answer was given. After a couple of days (contract negotiations included) someone from Cubis was able to implement a solution that satisfied the customer.

Next to having easy access to expertise across platforms, there are also fun activities organized to build fun and good, amicable relationships between the employees. This way everyone gets to know each other a little better and you end up knowing who has expertise in which subjects.

Apart from the things mentioned above, Lytix and Cubis have monthly internal status updates and regular knowledge sharing sessions with Lytix’s “Bits & Bokes” where someone from Lytix brings some interesting to know knowledge in a short session of 30 minutes during lunch break.

Tibo Vandermeersch

Tibo Vandermeersch

In case you have questions regarding MS BI, don’t hesitate to contact Lytix for more information!  

Power BI and Spotify – A match made in heaven?

Data isn’t life. They say music is. Well, is it? Wouldn’t it be awesome to simply combine both worlds? Let’s find out if this could turn out to be a perfect marriage.

Today, with approximately 286 million users to date, including 130 million subscribers,
Spotify can be considered as one the world’s most popular music streaming services.
In a remarkable coincidence, I happen to consume their service every once in a while myself. Actually, this is an understatement as I stream music basically on a daily basis:
During travel, development, while exercising or even when writing this blog at home.

What would we like to find out?

In regards to my streaming behavior, I came up with the following questions:

> What artists did I favor the most

> What songs did I listen to the most ?

Can certain events, such as festivals, cause a change in my streaming behavior?

> When did I stream music?

What KPI’s can we define to provide us the answers with?

Of course we need to quantify these questions based on a KPI. I believe there are two ways of measuring whether I like or dislike a specific song. The first one is the time I actually listened to the song, which I will be referring to as the listening time (in minutes). The second KPI is the song count, being the number of times a song started, regardless of me manually pressing the play button or Spotify’s autoplay starting the song.

Where can we gather the data we need?

Spotify offers their users to retrieve data through their Spotify API. Recently, they launched a refined version of this API which, at the moment of writing, is still in BETA. I can only praise Spotify for how well they document their API. They offer a clear process description on how to connect to their API, written down call definitions and even providing developers a test environment console. More information can be found here.

Evaluation time!

As we now have all data in place, we can assemble our very own Spotify dashboards with Power BI. Let’s analyze my streaming behavior of the past year by trying to formulate an answer to our predefined questions. 😊

What artists did I favor the most?

Post Malone seems to be the clear winner based on my streaming behaviour, there’s no doubt there. I tend to have a preference for R&B with Tyga and Blackbear claiming a spot in my top 5 artists.

What is interesting here is that my top 5 artists slightly differs when switching between our two predefined KPI’s. Spot 1-3 are identical, regardless of the KPI we base ourselves upon. However, in terms of song count, Nickelback and Loud Luxury are my respective number 4 and 5 whereas, in terms of play time, Loud Luxury and Blackbear take in those positions. This can have two possible explanations:

1) Songs of Nickelback are simply shorter than Blackbear songs

or

2) I’m excited to hear Nickelback songs’ refrains and get bored afterwards, forcing myself to switch songs once the refrain passed by.

What songs did I listen to the most?

It wouldn’t surprise you I listened more than 150 times to Post Malone’s top songs such as A Thousand Bad Times, Sugar Wraith and Goodbyes.

Can certain events, such as festivals, cause a change in my streaming behavior?

Yes! Last August I attended the Sziget festival in Hungary. According to my streaming history, I enjoyed Post Malone’s performance so much that the following months, Post Malone skyrocketed and became my number one streaming artist.

When did I stream music?

It turns out I’m actually getting value out of my monthly subscription. The past year, I streamed 357 out of 365 days. My listening time stays more or less steady throughout the week. During the work week, I mainly stream music traveling between home and work. And if you’re interested; yes, I’m not an early bird. 😊

Wrap-Up

This article described one way of vizualising your Spotify streaming behavior with Power BI. Of course there are many more insights these reports can be enriched with. I tried to provide a high level summary of how I created this analysis. If you’re interested in the technical details or would like to get help upon solving your own data visualisation enquiries, do not hesitate to get in touch with me or any of my colleagues at Lytix.

All aboard the data train today, CHOO CHOO 🚂 

Lou Segers
Microsoft Analytics Consultant @ Lytics
Follow me on LinkedIn

Snowflake, a DWH as a Service

‘Cloud’ or ‘Clowned’? With the high adoption of cloud computing in all processes, ‘Analytics’ is the one that can benefit from it the most. Analytics as being it a data warehouse that is used for reporting or ad-hoc analysis, Data Lake which serves as a repository for data that contains added value and Big Data in which one server isn’t sufficient to meet your analytical needs. Typical to analytics is combining different sources, a high variation in its use (at some days it is used highly intensive, while at others it is not consulted) and the need for lots of computational power.

As a Cloud PaaS solution Snowflake is taking the Data world by storm. This tool has a lot of benefits but is it really as good as it claims to be? Can it address the challenges listed above? This blog post will go through the most prominent advantages and disadvantages of Snowflake.

What SnowFlake Enables…

But… what is PaaS?

Before we dive into the nitty-gritty of Snowflake it is important we define what a ‘Platform as a Service’ (PaaS), is.

As the name describes, when a platform-based service is offered to be used by customers where they can execute the required developments without having to take care of the underlying infrastructure. This infrastructure is provided and maintained by the owner of the PAAS Solution.

So, everything is set for me to develop! What are the limitations then?

Well, exactly as you say. Everything is set up for you so you cannot change anything in the infrastructure. Any limitations that the Solution has, you will need to either live with or hope that it is solved by the provider.

This is an issue you don’t have when it is developed by yourself. If you need some additional infrastructure developments or changes you can plan and execute it yourself.

It is always best to look at what the PAAS solution can offer you and if it ticks all the boxes you need.

Where does snowflake fit in here?

Snowflake offers a lot of capabilities you can use during your journey through data without having the issue of taking care of your environment. All the technical set-up is done for you, hence it is PaaS.

What makes snowflake so special besides being PaaS?

We will look into a few aspects that make Snowflake shine.

  • Cloud Availability

One of the main selling points of Snowflake is the fact that is it fully cloud-based and not an on-premises system made available in the cloud.

It’s available every major cloud service on the market, meaning Azure, AWS and Google cloud. On top of this Snowflake maintains a Cross-Cloud strategy.

This strategy ensures the following:

  • The possibility to negotiate the best cloud environment without having any data-migration issues.
    • In case your chosen cloud provider faces an outage you can simply switch to another cloud provider and ensure uptime of your systems.
  • Data Storage Capacity

You often hear data is the new currency. Nothing could be more true in the business world. Companies keep collecting data and so their volumes keep growing.

Luckily Snowflake has no limit on their data storage. As the storage and compute layers are separated, you can just dump all relevant business data into SnowFlake which will then treat it as a Data Lake (= read: cheap storage and an infinite storage amount).

Of course you might not need a large amount now, but during the years to come you expect your data volumes to increase. If you are provided with a large volume of value-added data, then you do not need to hesitate and can directly add it to SnowFlake. All data residing within SnowFlake (= your data lake) is directly accessible.

Another interesting feature based on Data Storage is the fact that if you copy a database, it will not take double the space. It will recognize the records that are the same and it will only require space for different or new records.

  • Dynamic Scaling

Snowflake often uses the phrase ‘Pay for what you need’ which is exactly what the dynamic scaling is for. This is best explained with an example;

Imagine you run your daily load in the morning between 3 AM and 4 AM. At the start of the working day, a lot of users are interested in the latest insights which are available within SnowFlake (let’s say: between 8 AM and 10 AM). For the rest of the day, only one super users sends maybe one query now and then.

In more traditional cost-effective Cloud scenario’s (= not SnowFlake), you would start-up your BI system only for the moments which it is planned to use (= 3 AM and 4 AM, and 8 AM and 18 AM). Your BI environment takes a while to spin up and spin down.

SnowFlake doesn’t incur a delay in ‘spinning-up and down’ and is thus directly accessible, even if no cluster is running. We often configure it in such a way that it is immediately spins-up whenever someone sends a query and automatically spins down after one minute of inactivity. Paying only when compute is being used, will save lots of money for your organization!

“SnowFlake directly spins-up when users issue queries at the database, even at an idle state. In addition, SnowFlake is automatically paused after one minute of inactivity. This combination saves a high amount of infrastructural costs which would otherwise be billed.”

  • Instant Adjustable Processing

One of the most interesting features is the ability to change the size of your warehouse. The bigger the size, the faster big queries can be executed. This can once again be changed on the fly depending on the needs of the organization at that point in time. SnowFlake uses T-Shirt sizes (XS, S, M, L, XL, XXL, XXXL) and each size is double the size of the previous (= Medium equals twice the processing power of Small).

  • Query Memory

It is very important to optimize your query usage as much as possible since you are paying per usage. Luckily Snowflake has this covered for you!

If you execute a query now and the data doesn’t change it will be cached in Snowflake’s engine.

This means that the second time you execute the query, it will not cost you anything as you will not be querying your data directly but the cached version.

That’s quite a lot of features! Are there any others?

Yes there are. We won’t be going in detail about all of them but here are some smaller, but also interesting features.

  • Time-Travel Feature: this is the ability to view data from the past.
  • Copy-Clone: You can Copy an existing database that is directly available for use.
  • ANSI-SQL
  • You can Query directly on JSON, XML… files.
  • Streaming
  • Data Sharing
  • Active Directory Integration

Are there any aspects you would say Snowflake is not suited for?

You must keep in mind Snowflake in its core designed for handling Big Data, Data Warehouses and so on. If your interest/use case lies in Data Science we would suggest you look at tools built with this in mind. We would suggest DataBricks and Spark. More info regarding DataBricks can be found on our blog here.

Very interesting! How can I get in touch?

If you need help (development, architecture, strategy, …) on one of your BI implementations, then Lytix will help you with this. Lytix believes in the SnowFlake platform and is an expert in the advantages and disadvantages it has. Our ETL framework ‘XTL’ uses SnowFlake as a database engine and can provide you with a head-start in an implementation.

The XTL Framework & Technology Drivers

This is how we keep our team spirit high!

As a data & analytics consultant, our goal is to spread our KNOWLEDGE on the projects of our clients. Next to applying and increasing our knowledge one of our core values is FUN.
A crisis should not stop this! It’s definitely more challenging for us to organize events in which we adhere to government policies. But we don’t mind a challenge, we accept it instead of ignoring it.


That’s why we chose to come online all together on Thursday evening. We started at 8 pm with an online quiz. We divided our team into a number of groups and competed with each other. Of course, we weren’t allowed to use our good friend Google. The quiz who consisted of 40 questions took about an hour to complete.  In my opinion, it was a success! However, wait for the results 😉

Afterward, we started some online games, such as Saboteur and some card games. We deliberately chose some games in which we could all participate. Paired with a local beer and a joke, it was also the perfect opportunity to catch up with each other.

The Covid19 virus is here and now, we can’t change that. What we can change is the way we deal with it. We are opting for the “all alone together” method, where we will be in contact with each other on a regular basis. Let’s prioritize health and let’s stay safe! Every life matters.

PS: Please don’t ask about our scores on the quiz, it will be better next week. Promised!😀

Blog by – Jorik Spiesschaert