Error during install SQL Server 2017 CTP 2.1

I tried to install the CTP version 2.1 of SQL Server 2017 and ran into an error when starting the management studio.

FileMgr::StartLogFiles: Operating system error 2
(The system cannot find the file specified.) 
occurred while creating or opening file 
'd:\dbs\sh\sprel\0510_114453\cmd\31\obj\x64retail\sql\mkmastr\databases\
mkmastr.proj\modellog.ldf'.
Diagnose and correct the operating system error, and retry the operation.

I  don't what I did or how but it seems a glitch in the CTP version. The only thing I can think of is that I changed the data and log folder to the D Drive. I selected all the options in the installation process.

I solved it by creating the required path and copying the Master, Model, tempdb and msdb databases and copy to this location d:\dbs\sh\sprel\0510_114453\cmd\31\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\

I had some problems with security of the folders and files :

 FCB::Open failed: Could not open file 
 d:\dbs\sh\sprel\0510_114453\cmd\31\obj\x64retail\sql\mkmastr\databases\
 mkmastr.proj\model.mdf for file number 1.  
 OS error: 5(Access is denied.).

But this is easily solved by adding the MSSQLSERVER account to the folders security and propagate this to this folders beneath it.

Next problem was that the administrator login/user was not present in SQL Server. I managed to solve that by executing the following steps: Stopping/Starting the SQL Server process (net stop mssqlserver) and run it in single user mode (net start mssqlserver /m), add the administrator account and give it sysadmin rights and then stop and start the SQL Server with normal settings(net start mssqlserver).

Hennie

Setting up Visual Studio Team Services

Introduction

When a team of developers grows larger and larger it is a best practice to introduce Version Control in the project. Version control has some advantages :

• A controlled way of working together on the same code.
• Storing versions in order to understand the differences and to understand what happened during development of the code.
• To see how much work a subproject/sprint was during evaluation.
• As a backup for the code (in a former blogpost I described a way to automatically backup the project)
• Creating multiple branches in the code in order to distinguish subprojects, Development and production code (eg. for hotfixes).

In this blogpost the following steps are described:

• Step 1 : Create a sample project in Visual Studio
• Step 2 : Create a Team Project
• Step 3 : Connect to the Team project environment in Visual Studio.
• Step 4:  Setup a workspace on your local system.
• Step 5:  Add the project to Source control / Team project
• Step 6:  Do some changes in the local VS project.
• Step 7:  Check the online Team foundation Server environment.

Step 1: Create a sample project

First, lets make a solution with some examples in a Visual Studio project. The solution I've created for this blogpost includes a SQLProject, a SSISProject and a Tabular Project.

Step 2: Create a Team Project

The next step is to go to the VisualStudio.com and to create a new team project with New team project. 

Give the Team project a proper project name, give the project a Description, choose the type of version control and choose the type of project type with Work Item process. Choose Create.

Now, the project is created in Team Projects and an overview is given about the project.

Step 3:  Connect to the Team Project environment

The next step is to find the Team Explorer in Visual Studio, and there are several options to choose from. In this case, select the Connect link of the Visual Studio Team Services.

The window "Connect to Team Foundation Server" appears and now the url is needed of name of the Visual Studio Team services account. Select the option "Servers".

Press the Servers button and Add a new server. Login in with your account and press sign in

The following window appears

Connecting with the Visual Studio Team Services and select the Team projects.

Step 4: Setup a workspace

In Visual Studio it is needed that there is a local workspace to work with. This is a local copy of the code of the central repository. Click on Configure Workspace.

Next step is to Map & Get the local workspace with the central repository

When the Map&Get is successfully a message is presented : "The workspace was mapped successfully"

Step 5: Add the project to Source control / Team Projects

Now the workspace and the central repository is connected and now it 's time to add the local solution to the Visual Studio Team Services by clicking on Add Solution to Source Control.

Now, it is important to set the location in Visual Studio Team Services. Press OK when  this is done

Now the projects are marked with a + sign indicating that the files are ready to check in Visual Studio Team Services.

Right click on the solution and click on Check in to upload the code the repository.

Add some comment, check if everything is ok and press Check in

A confirmation window appears and click on Yes.

A confirmation is shown to the usr that the code is successfully checked in

Step 6: Do some changes in the local VS project

Now, let's see what happens when something changes in the code. In the initial situation the code is locked and check in.

For instance let's add a SSIS package to the SSIS project.

Two packages were added to the local Workspace. You can see this below Package1 and Package2.

Because the SSIS Packages are added to the project(file), the project (file) is also marked as changed and therefore this should be uploaded to the repository too.

Select the Project and click on the Source Control option.

An overview is shown of the new and changed packages. Click on Check in.

Step 7: The check the online Visual Studio Team Services environment

The last step is checking the online Visual Studio Team Services environment where the code is now stored.

Conclusion

In this blogpost I've described the setup of Visual Studio and Visual Studio Team Services working together such that version control of your BI project is enhanced.

Getting filtered data from a tabular with Excel

Introduction

Building a PowerPivot model on filtered data of a table from a SQL Database is fairly simple. In the table properties you can change the table view to query view and change the query. I haven't found a similar way when using a tabular with Excel (realtime connection).

The only option I have found so far is using the CommandText in the connection properties, but that changes my look and feel of browsing the cube. The retrieved data from the tabular is just like one plain table. This blogpost will describe the way of adjusting the .odc file in Excel and getting the data as a plain table.

The goal of this blogpost is to show how to filter the factdata based on a dimension value (DimCustomer).

The Datamodel

I've created a simple model in SQL Server and imported it into the Tabular project. It is modeled as follows.

One Facttable with a key to the DimCustomer table and a measure Amount. I added a few records.

Some simple EVALUATES

In SSMS, I experimented with some EVALUATES to get some data from the Tabular. Below the result of this exercise.

--Getting the data from a table
EVALUATE Fact

--A simple  query (and join between tables)
EVALUATE
SUMMARIZE (Fact
,DimCustomer[Name]
,"TotalAmount", SUM(Fact[Amount])
)

--A simple query and a calculated measure is used
EVALUATE
SUMMARIZE (Fact
,DimCustomer[Name]
,"TotalAmount", Fact[TotalAmount]
)

--A calculated measure and a Filter
EVALUATE
FILTER(
 SUMMARIZE (Fact
 ,DimCustomer[Name]
 ,"TotalAmount", Fact[TotalAmount]
 ),
 DimCustomer[Name] = "Hennie"
)

Adjusting the .odc file

From the experiments in SSMS i took one of the DAX Evaluates and inserted that in the CommandText tag in the odc file of Excel.

This is the result in the Connection properties in the Excel file. The CommandType combobox is changed to Default and in the CommandText box a DAX EVALUATE expression is presented.

The result in Excel

And below you can see the fields in the FieldList and as you can see the Look and Feel has changed of the FieldList. 

Conclusion

This blogpost is about how to filter the data in Excel from a Tabular Model.


Best regards,

Hennie

DAX : Selecting a measure with a Slicer

Introduction

Someone asked me a question about selecting a measure in Excel with PowerPivot/Tabular and I founded a blog about selecting a measure written by James Thomas. I have made my own example and wrote this blogpost, about it. In this blogpost I'll describe the following steps:

• Create some sample data.
• Create a helper table.
• Create the datamodel.
• Create the measures.
• Create a pivottable and add the properfields to the pivottable.
• Testing the result.

The source data

I've created three tables: DimCustomer, FactSales and a Measure table.

I added these to the PowerPivot model and the datamodel looks like the following screenshot:

The DAX Expressions

I've created the following DAX Expression (the + 2, etc is for getting some different testdata)

MeasureA:=SUM([Amount]) +2      
MeasureB:=SUM([Amount]) + 3
MeasureC:=SUM([Amount]) +8
MeasureD:=SUM([Amount]) +4
       
MeasureValue:=switch (TRUE;
Min ('Measure'[MeasureID] ) = 1;
FactSales[MeasureA];
Min ('Measure'[MeasureID] ) = 2;
FactSales[MeasureB];
Min ('Measure'[MeasureID] ) = 3;
FactSales[MeasureC];
Min ('Measure'[MeasureID] ) = 4;
FactSales[MeasureD])

Configure the Pivottable

This is how the pivottable is setup. The MeasureName is added to the rows and the MeasureValue measure is in the VALUES area.

The result

Here is the result of the solution. Here you can see that the slicerbutton MeasureA and MeasureC selects only the measure A and C and that is exactly what I want.

Yet, another example of selection with the slicerbutton:

Here you can see the different measures according to the selected slicer buttons.

Conclusion

This blogpost is about selecting the proper measures with a slicer.


Greetz,
Hennie