Blog

Other Tips

Tip: Increase the Font Size of SQL Management Studio

Absolutely stupid tip but it is real. With the increase of high-resolution graphic cards (and the age of course) everything is getting smaller and sometimes it is more healthier and better for our old eyes to read results in bigger fonts. On the other hand it is especially required when you are doing a presentation or sharing your screen with someone and it is like an ant party that happens in this grid 🙂

Recently I’ve experienced such a thing and desired to increase the font size of the SSMS. I am used to zooming in and out by using ctrl+mouse scroll but realised that this technique only applies to the query part of the screen in SSMS.

You can also set the zoom level from the combo on the bottom-left side of the query screen.

But it is not possible to increase the size of the results grid from anywhere in this screen and ctrl+mouse scroll also doesn’t work. The remedy is hidden under Tools => Options 🙂

Just follow menu Tools => Options and in options dialog expand Environment => Font and Colors group. In “Show settings for:” section, select “Grid Results” and adjust the text and font size as desired. (But please no Comic Sans :))

Important: You should have to restart SSMS before this change takes effect.

One more last-minute tip: You can change the whole font of the User Interface. Just follow this:

For more tips like above, you can visit https://www.ssmstipsandtricks.com/.

Power BI Stack

How to add multiple files at once to model…

When there are multiple files to import there is an easy way to import these files at once to Power BI Desktop Application. You should utilize the Folder import method of Get Data functionality.

To do this just follow “File > Get Data > More” and select Folder on right side or just write Folder to search box on top left corner.

Select Folder as Data source to import all files in that folder.
Select Folder as Data source to import all files in that folder.

You will be able to select, load and combine data from different files together and create reports. This is especially useful when an automation/application creates a text/csv output to a folder regularly and you have to combine all data to conduct an analysis. This functionality saves you from painful and lofty ETL process just to load the data to a database and consume it again to create a report. Power BI Desktop Folder import functionality is a life saver.

Scenario 1: Multiple files categorized/aggregated per entity such as Country.

In this scenario assume that our automation exports sales data to a file server folder grouped by Country. So that we have a seperate file for each country.

Now we will select this folder and let Power BI Desktop Folder import functionality to do its best. Just follow “File > Get Data > More” and select or find “Folder” in right section. Click “Connect”

Power BI Desktop will open Folder Browser Dialog. Navigate to the folder where your csv files were exported and click OK.

Power BI Desktop will display an intermediate window which summarizes the content of the selected folder. You can also see file properties of each content.

On bottom of this screen there are three main buttons and five functionalities:

Load: Loads the file structure (without combining all files to one file) to Power BI Desktop but will not import the actual data.
Transform Data: Loads the file structure to Power BI Desktop and opens Power Query Editor to edit/transform it.
Combine & Load: Combine all data and load into model.
Combine & Transform Data: Combine all data and load into model for editing.

Let us continue with Combine and Transform Data. This will open a new window for us to configure combination options for those files we’ve selected.

Let us take the first file as a structure (it would not be a problem when all our files have the same structure) and continue. Power BI Desktop will open Power Query Editor (because we’ve selected Transform) for our customization requests. After we’ve done with the customizations we can simply click on Close & Apply (or just Apply) in Power Query Editor to proceed with data load.

There could be errors raised during this loading process. If any errors occured it will be reported on the same dialog.

When you click on “View Errors” you will be redirected to list of errors where you can check and see the details of those errors.

If everything goes well, you will be able to see this data in Power BI Model.

CSV Files Used in this Post:

Scenario 1Download
Almanca

schlag mich tot / schieß mich tot

Günlük hayatta duyabileceğiniz Almanca söz öbeklerinden biri:

schlag mich tot ya da schieß mich tot

Beni öldür ya da beni vur olarak çevirilebilir ama anlamı ve kullanımı oldukça farklı. Günlük hayatta Türkçe’deki “atıyorum”,”bilmem kaç”,”ne bileyim”,”ne desem yalan”,”yanlışsam düzelt” gibi kullanımlarla benzer anlam taşıyor.

(Es ist meist ein Ausruf, wenn einem etwas nicht einfällt, oder gerade entfallen ist.)

Sie wohnt in der Bahnhofstraße, Hausnummer schlag-mich-tot
(O Bahnhostraße’de numara bilmem kaçta oturuyor.)

Gleich läuft das Spiel: Deutschland gegen Schlagmichtot.
(Almanya ile bilmem kimin oyunu/maçı başlamak üzere.)

Du kannst dieses Paket mit DHL, DeutschePost, Hermes und schlag mich tot liefern
(Bu paketi DHL, DeutschePost, Hermes ya da ne bileyim neyle gönderiyorsan gönderebilirsin işte.”)

Weitere Infos:
https://www.redensarten-index.de/suche.php?suchbegriff=~~schlag+mich+tot&bool=relevanz&sp0=rart_ou

(Ayrıca bu siteyi acayip tavsiye ederim, kraldır Redewendung konusunda)

Power BI Stack

How to use MS Access Database File in Power…

Power BI Online is not capable of importing Microsoft Access Database file (both mdb and accdb). To use MS Access DB as a datasource to conduct analysis, we should have to use Power BI Desktop first.

When you want to import file in Power BI Online Services, you see only supported data types and MS Access Database is not listed, therefore is not supported as a local file to be imported to be used as a datasource.

You should open Power BI Desktop application and follow “Get Data > More > Database > Access Database” menu path and create reports by using it and saving it as PBIX file. Then you will be able to publish this file to Power BI Online or connect to PBIX file by using “Get Data > Files” path in Power BI Online Service and create your reports on top over it.

To do this, you can follow these instructions below:

  1. Open Power BI Desktop Application
  2. Follow “Menu > File > Get Data > More” and it will open a new window with full of supported data sources.


  3. Find “Access Database” from list on the right or by using Search on top left.

  4. Choose your desired MS Access database file.


  5. Now you will see the entire list of tables in that selected database file. Now you can select and import tables as desired.

    (If you got error regarding Access Database Engine Driver incompatibility in this step see Note 1 at the end of this post)

  6. After you are done with the data import, design your report (or just create an empty one) and hit Publish or Save it as PBIX file in your local drive (later to upload it manually from portal)


  7. You will be able to see your newly created dataset in “Datasets” section of your Power BI Online instance.


  8. That’s it. Now you will be able to create your reports over it.

#Note 1:

When you were trying to connect to a Access Database and got following error:
Details: "Microsoft Access: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine 2010 Access Database Engine OLEDB provider may be required to read 'Northwind.MDB'. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987."

It means the driver of the datasource that you are trying to connect and the application that you are using has different system architecture. For instance you have installed Power BI Desktop 64 Bit but trying to connect to Access database with 32 Bit Access Driver vice versa. To keep it simple: Either use Power BI Desktop 64 bit and Access Driver 64 bit together or Power BI Desktop 32 bit and Access Driver 32 bit. (Note that Access Database Driver and MS Access Application are totally different things.)

Another important point is:

If you already installed Microsoft Office as 32 bit, do not install Access Driver 64 bit. You will not be able to use two versions of same product in same box/computer and would probably get an annoying error like this:

ACE engine bittedness error

Best Practice is to install Power BI Desktop inline with the architecture of your Microsoft Office.

However if you want to use 32 bit Power BI Desktop Application, you will experience out-of-memory problems on large datasets.

If your Power BI Desktop Application architecture and Microsoft Office Installation is already 64 bit and you’ve got the connection error, it is highly probable that your computer has Access Database Driver 32 bit. Just download and install 64 bit Microsoft Access Database Engine 2010 Redistributable executable (AccessDatabaseEngine_X64.exe) from following link: https://www.microsoft.com/en-us/download/details.aspx?id=13255

For more information, follow the link in the original message for troubleshooting connectivity issues.

Power BI Stack

Power BI Desktop – Change Views: Report, Data, Model

There is a simple and effective way to toggle between Report, Data and Model views in Power BI Desktop Application. You can use left hand side toolbar menu to achieve this quickly.

Power BI Desktop Report View​
Power BI Desktop Report and Other Views

Default view is Report View. Power BI Desktop Application starts with this view. It is used for designing and viewing a report.

Second view is called Data View. In this view you can browse your underlying data in your data set. Select this view and change Fields on the right side to view sample data.

Power BI Desktop Data View​
Power BI Desktop Data View

Third view is Modeling View. In this view you can do modeling tasks, design the data model of your report, see and define the relationships between entities and change other metadata related information.

Power BI Desktop Model View​
Power BI Desktop Model View
BI Infrastructure

SSIS – Storing Dataflow Results to Object Variable with…

There are several methods to store the your data into Object Variable in SSIS. (See: SSIS – Mapping Full Resultset with Execute SQL Task to Object Variable)
The second method is by Dataflow Task and a Recordset Destination.

1. Drag and Drop Dataflow Task to Control Flow

2. Create a Object Variable

3. Drag and drop a Datasource

4.Drag and drop a RecordSet Destination and Map object variable

Now you are done with getting values to object variable.

BI Infrastructure

SSIS – Mapping Full Resultset with Execute SQL Task…

Execute Sql Task in Control flow allows you to get a full resultset of your query. Full result set could be stored in Object typed variable to use within other places in SSIS.

1- Drag&drop a Execute SQL Task to Control Flow

2- Create a variable with object type:

3-Create a connection to MSDB and use SELECT * FROM sys.tables as an SQL Query and Set Resultset to Full Result Set

4- Go To Result Set Tab and write 0 for ResultSetName and select ObjVariable as Variable Name. (When working with Full result set, Result set name must be 0)

Now you are done with getting values to object variable.

BI Infrastructure

SSIS – Looping Over Object Variables with as ADO…

Foreach Loop container in SSIS allows you to loop over the values of the object variable and store the necessary information as a result set to variables to be used anywhere in SSIS for different purposes.

1- Create a object variable and fill it with information:
See: SSIS – Storing Dataflow results to Object Variable with Recordset Destination
See: SSIS – Mapping Full Resultset with Execute SQL Task to Object Variable
(Both examples above fill Object variable with sys.tables data within the msdb database in SQL server.)

2- Create two more variables to hold the table name (string) and object id (integer)

3- Drag&Drop a Foreach Loop container to Dataflow:

4- Go Collection tab and Select “Foreach ADO Enumerator” for Enumeration and select your ObjVariable as “ADO Object Source Variable” and specify “Enumeration Mode” as “Rows in the First Table”

5- Go to Variable Mappings Tab and add your map your variables to relevant index of your data in the Object Variable

Variable index is zero based and must be index of the column in the dataset. We use Tablename and objectId which indexes are 0,1 in order.

6- Drag&Drop  Script Component to see the values mapped and run the package.