July 9, 2018 at 2:11 pm
Hello, I have created a 1200 Version (2016) tabular cube. And in that cube I have create a number of data partitions for my large fact tables. To focus on one of those data partitions I’m trying to load, lets call it Usage_201806. In that one D/P that I’m trying to get to load local SQL Server data into, I have this SQL statement Inside that data partition, I have this SQL statement inside: Select Colname1, Colname2, etc, from tablename where date = 201806.
In my SSIS package, instead of my fact table loads going straight to a Analysis Services Processing Task, that executes a full process on the cube and loads everything into one large partition, I have in its place an Analysis Services Execute DDL Task. In that task, in the editor, on the DDL tab, the connection is set to the correct target cube, Source Type is Direct Input, and in SourceDirect I placed this script:
{
"refresh": {
"type": "automatic",
"objects": [
{
"database": "Subs Cube",
"table": "Usage",
"partition": "Usage_201806"
}
]
}
}
(Obtained script by the method of going into the deployed target cube with the new partitions I created, and went to table, process, then scripted out to new, etc…)
There are no errors, and when I run it, no errors, but it’s not processing the local data into the data partition. This is a test, once done, I’ll setup many more using TSML and get all the target partitions loaded, once this is good.
What do I need to look at, alter, or is there a better approach? I’m trying to make this simple, use SSIS pkg tools and minimal script, with each new month, some interaction/alteration to get the next target month to load, at this time no fancy automation.
Thank you
July 9, 2018 at 2:56 pm
Update: I have added an XMLA wrapper around my TMSL code, below, but I get the same result, runs, no errors, but doesn't load any data to the partition:
<Statement xmlns="urn:schemas-microsoft-com:xml-analysis">
{
"refresh": {
"type": "automatic",
"objects": [
{
"database": "Subs Cube",
"table": "Usage",
"partition": "Usage_201806"
}
]
}
}
</Statement>
July 10, 2018 at 7:33 am
Are there any details that I can post to help clarify this, as it seems it should work? I've back checked each step, and it appears square, no errors, and when runs no errors, yet it doesn't load the target data partition, and there is data local that is in the date range of the target tables data partitions.
July 10, 2018 at 8:05 am
quinn.jay - Tuesday, July 10, 2018 7:33 AMAre there any details that I can post to help clarify this, as it seems it should work? I've back checked each step, and it appears square, no errors, and when runs no errors, yet it doesn't load the target data partition, and there is data local that is in the date range of the target tables data partitions.
FYI -
There are no filters on the data from the DW source, and there are no filters on the load to the SQL Server table or View that goes to the Cube model. I checked, and there is data in the target table (Usage) and data for the target date range, 201806.
The target data partition that I created, many of them in that cube, but for this target folder in the model, and the target partition, has this SQL statement:
SELECT [col1].[dbo].[Sub_cube],[col2].[dbo].[Sub_cube],FROM [DBNAME].[dbo].[SUB_CUBE] where convert(char(6), 112) = 201806
And in the DDL Task Editor, DDL tab, source type is direct input, and the source direct is:
<Statement xmlns="urn:schemas-microsoft-com:xml-analysis">
{
"refresh": {
"type": "automatic",
"objects": [
{
"database": "Sub_Cube",
"table": "Usage",
"partition": "Usage_201806"
}
]
}
}
</Statement>
July 10, 2018 at 8:12 am
This is strange syntax:
... convert(char(6), 112) = 201806
Shouldn't there be a column name in there somewhere?
Also, if you are converting to char, you should put quotes around your literal - '201806'
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 10, 2018 at 8:25 am
Phil Parkin - Tuesday, July 10, 2018 8:12 AMThis is strange syntax:
... convert(char(6), 112) = 201806
Shouldn't there be a column name in there somewhere?
Also, if you are converting to char, you should put quotes around your literal - '201806'
I was paraphrasing from other code, where other code is from a more complex and fully automated cube that uses table driven, sporcs, etc, to operate. Where the previous cube, for its tables data partitions, have the first SQL state, for the 1st data partion (not populated) as: select col1 from tablename where 1=0, then the other d/p's are names ex: usage_201807, usage_201806, etc and its SQL state is: select col1 from databasename where convert(char(6),112) = 201806.
So I copied and modified from there. The conversion is apparently on a Date_YearMonth (201806) and is an Integer.
What would you recommend, something like: Select col1 from databasename where date_ym = '201806' ?
Thanks
July 10, 2018 at 8:31 am
quinn.jay - Tuesday, July 10, 2018 8:25 AMPhil Parkin - Tuesday, July 10, 2018 8:12 AMThis is strange syntax:
... convert(char(6), 112) = 201806
Shouldn't there be a column name in there somewhere?
Also, if you are converting to char, you should put quotes around your literal - '201806'I was paraphrasing from other code, where other code is from a more complex and fully automated cube that uses table driven, sporcs, etc, to operate. Where the previous cube, for its tables data partitions, have the first SQL state, for the 1st data partion (not populated) as: select col1 from tablename where 1=0, then the other d/p's are names ex: usage_201807, usage_201806, etc and its SQL state is: select col1 from databasename where convert(char(6),112) = 201806.
So I copied and modified from there. The conversion is apparently on a Date_YearMonth (201806) and is an Integer.
What would you recommend, something like: Select col1 from databasename where date_ym = '201806' ?
Thanks
Not quite. Where possible, avoid implicit datatype conversions by using the same datatypes throughout. Assuming date_ym is in YYYYMM format, just do this:
Select col1
from databasename
where date_ym = 201806
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 10, 2018 at 9:10 am
Phil Parkin - Tuesday, July 10, 2018 8:31 AMquinn.jay - Tuesday, July 10, 2018 8:25 AMPhil Parkin - Tuesday, July 10, 2018 8:12 AMThis is strange syntax:
... convert(char(6), 112) = 201806
Shouldn't there be a column name in there somewhere?
Also, if you are converting to char, you should put quotes around your literal - '201806'I was paraphrasing from other code, where other code is from a more complex and fully automated cube that uses table driven, sporcs, etc, to operate. Where the previous cube, for its tables data partitions, have the first SQL state, for the 1st data partion (not populated) as: select col1 from tablename where 1=0, then the other d/p's are names ex: usage_201807, usage_201806, etc and its SQL state is: select col1 from databasename where convert(char(6),112) = 201806.
So I copied and modified from there. The conversion is apparently on a Date_YearMonth (201806) and is an Integer.
What would you recommend, something like: Select col1 from databasename where date_ym = '201806' ?
ThanksNot quite. Where possible, avoid implicit datatype conversions by using the same datatypes throughout. Assuming date_ym is in YYYYMM format, just do this:
Select col1
from databasename
where date_ym = 201806
After making various minor tweaks in SSIS and SSAS Tab cube, this was the main sticking point, along with changing the DDL script in the cube from Automatic to Full, now I'm in business. Thank you
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply