Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

migration data model from AS to Power BI

How to migrate data model created in Analysis Services / Azure Analysis Services to Power BI Desktop?

I have a hundreds of measures and do not want to manually recreate all ones

1 ACCEPTED SOLUTION

Hi @Anonymous,

 

I'm afraid we can't do that for now. But I found a workaround indeed. The measures in an Excel model can be imported into PBI Desktop. And we can use VBA to create measures fastly. The workaround is clear now. Please try the demo in the attachment. 

1. Install the DAX studio from here.

2. Connect to the SSAS tabular and run the query below.

select [name], [expression] from $SYSTEM.TMSCHEMA_MEASURES

3. Now, we get all the measure names and their details (part 5 in the image). Copy the details to a workbook in sheet1 starting from cell A1.

migration_data_model_from_AS_to_Power_BI1

4. Copy a few cells in a sheet and create a very simple data model in Excel.

migration_data_model_from_AS_to_Power_BI2

5. Open the Visual Basic window and paste the code below and run it.

Public Sub add_measure()

Dim Mdl As Model
Dim tbl As ModelTable
Dim nRows As Integer
Dim i As Integer
Dim measureName As String
Dim measureContent As String

Set Mdl = ActiveWorkbook.Model
Set tbl = Mdl.ModelTables(1)

nRows = Sheet1.UsedRange.Rows.Count 'get the total rows
For i = 1 To nRows
    measureName = Sheet1.Cells(i, 1)
    measureContent = Sheet1.Cells(i, 2)
    ' MsgBox measureName
    ' MsgBox measureContent
    Mdl.ModelMeasures.Add measureName, tbl, measureContent, Mdl.ModelFormatWholeNumber(1)  'insert a measure to the model
Next i

End Sub

6. Now, we can see all the measures in the model and we can import all of them into Desktop.

migration_data_model_from_AS_to_Power_BI4

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

You can connect to the SSAS models. But I'm afraid you can't import the SSAS model and turn it into Desktop Model directly. BTW, if it's SSAS tabular, the measures are created with DAX which is also used in the Desktop. So you can just copy the measure and paste it in Desktop.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks Dale,

 

Ya, it is SSAS tabular. Can I do copy / paste measures in a bulk? No one by one, because it will take too long.

I know I can document measures with Dax Studio but can I then push them to pbix some how?

 

If I create new pbix file and recreate tables, then

If I change pbix extention to zip, I will be able to find file with a data model:

 

Capture6.PNG

Unfortunatelly I am not able to open it. Can I paste measures there somehow? 

Hi @Anonymous,

 

I'm afraid we can't do that for now. But I found a workaround indeed. The measures in an Excel model can be imported into PBI Desktop. And we can use VBA to create measures fastly. The workaround is clear now. Please try the demo in the attachment. 

1. Install the DAX studio from here.

2. Connect to the SSAS tabular and run the query below.

select [name], [expression] from $SYSTEM.TMSCHEMA_MEASURES

3. Now, we get all the measure names and their details (part 5 in the image). Copy the details to a workbook in sheet1 starting from cell A1.

migration_data_model_from_AS_to_Power_BI1

4. Copy a few cells in a sheet and create a very simple data model in Excel.

migration_data_model_from_AS_to_Power_BI2

5. Open the Visual Basic window and paste the code below and run it.

Public Sub add_measure()

Dim Mdl As Model
Dim tbl As ModelTable
Dim nRows As Integer
Dim i As Integer
Dim measureName As String
Dim measureContent As String

Set Mdl = ActiveWorkbook.Model
Set tbl = Mdl.ModelTables(1)

nRows = Sheet1.UsedRange.Rows.Count 'get the total rows
For i = 1 To nRows
    measureName = Sheet1.Cells(i, 1)
    measureContent = Sheet1.Cells(i, 2)
    ' MsgBox measureName
    ' MsgBox measureContent
    Mdl.ModelMeasures.Add measureName, tbl, measureContent, Mdl.ModelFormatWholeNumber(1)  'insert a measure to the model
Next i

End Sub

6. Now, we can see all the measures in the model and we can import all of them into Desktop.

migration_data_model_from_AS_to_Power_BI4

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Good afternoon

I'm having this error, when I put the vba code.

("Object doesn't support this property or method”)

Tried several ways, but I can not make it work, does anyone have a solution?

 

I believe it is in this line, but I do not understand what is wrong.

("Md1.ModelMeasures.Add measureName, tbl, measureContent, Mdl.ModelFormatWholeNumber(1)    'insert a measure to the model")

 

thank you very much

Hi @Anonymous,

 

I would suggest you start with a new Excel workbook. Maybe the objects are used. Please download the demo from that attachment. You can see it clearly.

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks Dale,

 

The workaround works. Really appreciate your help.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.