February 22, 2016 at 6:54 am
Hi,
I have an issue whereby my date dimension whenever I display, it shows as full format with days like Monday. January 1, 2001.
How to make it in different format ?
I would like to show it only as mm/dd/yyyy like 01/01/2001.
I browse the net but cannot find good article. Some say it should be handle at presentation layer like in Excel or other viewer. Is that the only way?
Thanks,
toni
February 22, 2016 at 4:07 pm
I'm not sure if you're using Multi-dimensional or Tabular. For multi-dimensional, there is a format_string property. I don't recall what it's called in the GUI but it should be obvoius. Set this to the desired format. The underlying data type must be a date or datetime for a date format to work. Also, if there are any errors in the formula it will not apply the format and will not give any errors. I had this recently and needed some extra backslashes. I kept thinking it was something else because there were no errors.
Tabular should be similar. I don't remember that off the top of my head but it should be similar.
February 23, 2016 at 2:53 am
Hi Brian,
Thanks.
My cube is Multidimensional. I did specify it in Format string.
The date is created from the standard date dimension and in Date dimension attribute (which is the key), I have put dd/mm/yyyy in format string properties.
Data type in Key Columns property is Date.
What do you mean by if there is an error in formula ? the format string (dd/mm/yyyy) ? where I should put the backslash ?
Btw, here is where I put the string :
At Basic, format string = dd/mm/yyyy
Thanks,
Toni
February 23, 2016 at 6:02 am
Try "dd/MM/yyyy". Lower case "m" is minute. Other than that your string looks correct to me.
This page has the codes. User defined dates are almost at the bottom.
https://msdn.microsoft.com/en-us/library/microsoft.visualbasic.strings.format(v=vs.110).aspx
February 29, 2016 at 2:05 am
Hi,
we have tried this, but it won't worked. When we browse again, it still show the full date format with minutes to second.
Thanks.
March 5, 2016 at 10:59 am
Hi,
Is the problem because I create this dimension by Wizard and choose "Create Time table from server" ?
I also notice, NameColumn property is "New binding(WChar)
If I click the ellipsis, Binding type is "Generate column" with no options to click.
There is a message at below ->
"This selection creates new column. If you select this options, you must run Schema Generation Wizard prior to deploying the project"
What is all that?
March 9, 2016 at 12:49 am
tonzonline (3/5/2016)
Hi,Is the problem because I create this dimension by Wizard and choose "Create Time table from server" ?
I also notice, NameColumn property is "New binding(WChar)
If I click the ellipsis, Binding type is "Generate column" with no options to click.
There is a message at below ->
"This selection creates new column. If you select this options, you must run Schema Generation Wizard prior to deploying the project"
What is all that?
Wait, are you using a date table in your SQL source to base this dimension on or are you generating it in SSAS using a wizard? I would advise that you don't do the latter. If you do the former then your date key can be whatever the key of your source table is (I would recommend a "smart" date key such as 20160309 for today) and then for that key column you can set the name column as whatever formatted column you have in your SQL table.
Side note: every attribute in an SSAS dimension has a key column(s), a name column, and a value column. More on this here: https://www.mssqltips.com/sqlservertip/3271/sql-server-analysis-server-ssas-keycolumn-vs-namecolumn-vs-valuecolumn/
If you don't have a date table the following SQL will generate one for you with various attributes (it's been such a long time that I can't remember where I stole it from so forgive me for not attributing it to it's creator):
SET STATISTICS TIME ON --this is just to check performance and can be excluded
SET STATISTICS IO ON
--SELECT @@DATEFIRST -- Check First Day of the week (1 = Monday, 2 = Tuesday etc.) Default US English = 7
--SET DATEFIRST n --Change first day of the week if needed
DECLARE @StartDate DATE = '2000.01.01'
SELECT TOP 11323 -- ~30 years
IDENTITY(INT,1,1) AS N
INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
ALTER TABLE #Tally
ADD CONSTRAINT PK_Tally_T
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--INSERT INTO <a date table>
SELECT CONVERT(INT, CONVERT(VARCHAR(10), DATEADD(DAY,N-1,@StartDate), 112)) AS DateKey,
DATEADD(DAY,N-1,@StartDate) AS DateChar,--Format this as you wish
DATEPART(YEAR, DATEADD(DAY,N-1,@StartDate)) AS CalendarYear, -- always best to have calendar dates in a date table for comparison
DATEPART(QUARTER, DATEADD(DAY,N-1,@StartDate)) AS CalendarQuarter,
DATENAME(MONTH, DATEADD(DAY,N-1,@StartDate)) AS CalendarMonth,
DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate)) AS CalendarMonthNumber,
DATEPART(WEEK, DATEADD(DAY,N-1,@StartDate)) AS CalendarWeekNumber,
DATENAME(DW, DATEADD(DAY,N-1,@StartDate)) AS DayChar,
DATEPART(DW, DATEADD(DAY,N-1,@StartDate)) AS WeekdayNumber,
CASE WHEN DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate)) < 4 --April
THEN DATEPART(YEAR, DATEADD(DAY,N-1,@StartDate))-1
ELSE DATEPART(YEAR, DATEADD(DAY,N-1,@StartDate))
END AS FiscalYear,
CASE WHEN DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate)) < 4
THEN DATEPART(QUARTER, DATEADD(DAY,N-1,@StartDate))+3 -- April (use your brain to work it out for other months)
ELSE DATEPART(QUARTER, DATEADD(DAY,N-1,@StartDate))-1
END AS FiscalQuarter,
CASE WHEN DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate)) < 4
THEN DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate))+9
ELSE DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate))-3
END AS FiscalMonthNumber,
CASE WHEN DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate)) < 4
THEN DATEPART(WEEK, DATEADD(DAY,N-1,@StartDate))+40
ELSE DATEPART(WEEK, DATEADD(DAY,N-1,@StartDate))-13
END AS FiscalWeekNumber,
CASE WHEN DATEPART(DW, DATEADD(DAY,N-1,@StartDate)) IN (1,7)
THEN 1
ELSE 0
END AS IsWeekend,
DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY,N-1,@StartDate)), 0) AS MonthStart,
DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY,N-1,@StartDate)) + 1, 0)) AS MonthEnd,
DATEDIFF(DD, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY,N-1,@StartDate)), 0),
DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY,N-1,@StartDate)) + 1, 0)))+1 AS DaysInMonth,
CASE WHEN DAY(EOMONTH(DATEFROMPARTS(DATEPART(YEAR, DATEADD(DAY,N-1,@StartDate)),2,1)))=29
THEN 1 ELSE 0 END AS IsLeapYear
FROM #Tally
DROP TABLE #Tally
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
Then you would use the DateChar column as your name column (or just have it as an attribute in it's own right).
This article gives a good rundown of creating a date dimension in SSAS from a SQL Server source: https://www.simple-talk.com/sql/bi/creating-a-date-dimension-in-an-analysis-server-%28ssas%29-cube/
I think you need to take a more holistic approach to your problem and then it'll be scaleable too. Here is another page where the date format problem is dealt with specifically: http://stackoverflow.com/questions/22528601/how-to-change-the-date-format-on-ssas-2012
The answer given there is to store the format in SQL and then present it in the cube as a name column for your attribute, as I have mentioned, and gives yet another link where this is done.
Hope that helps.
March 17, 2016 at 10:52 am
Thanks for the input.
As mentioned, I'm using Wizard, but today I get rid of it, create my own table like you are saying, and it works.
I can change the Name Column property to some custom field showing short date format.
Thank you very much for the help.
Thanks.
March 17, 2016 at 9:07 pm
PB_BI (3/9/2016)
tonzonline (3/5/2016)
Hi,Is the problem because I create this dimension by Wizard and choose "Create Time table from server" ?
I also notice, NameColumn property is "New binding(WChar)
If I click the ellipsis, Binding type is "Generate column" with no options to click.
There is a message at below ->
"This selection creates new column. If you select this options, you must run Schema Generation Wizard prior to deploying the project"
What is all that?
Wait, are you using a date table in your SQL source to base this dimension on or are you generating it in SSAS using a wizard? I would advise that you don't do the latter. If you do the former then your date key can be whatever the key of your source table is (I would recommend a "smart" date key such as 20160309 for today) and then for that key column you can set the name column as whatever formatted column you have in your SQL table.
Side note: every attribute in an SSAS dimension has a key column(s), a name column, and a value column. More on this here: https://www.mssqltips.com/sqlservertip/3271/sql-server-analysis-server-ssas-keycolumn-vs-namecolumn-vs-valuecolumn/
If you don't have a date table the following SQL will generate one for you with various attributes (it's been such a long time that I can't remember where I stole it from so forgive me for not attributing it to it's creator):
SET STATISTICS TIME ON --this is just to check performance and can be excludedSET STATISTICS IO ON
--SELECT @@DATEFIRST -- Check First Day of the week (1 = Monday, 2 = Tuesday etc.) Default US English = 7
--SET DATEFIRST n --Change first day of the week if needed
DECLARE @StartDate DATE = '2000.01.01'
SELECT TOP 11323 -- ~30 years
IDENTITY(INT,1,1) AS N
INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
ALTER TABLE #Tally
ADD CONSTRAINT PK_Tally_T
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--INSERT INTO <a date table>
SELECT CONVERT(INT, CONVERT(VARCHAR(10), DATEADD(DAY,N-1,@StartDate), 112)) AS DateKey,
DATEADD(DAY,N-1,@StartDate) AS DateChar,--Format this as you wish
DATEPART(YEAR, DATEADD(DAY,N-1,@StartDate)) AS CalendarYear, -- always best to have calendar dates in a date table for comparison
DATEPART(QUARTER, DATEADD(DAY,N-1,@StartDate)) AS CalendarQuarter,
DATENAME(MONTH, DATEADD(DAY,N-1,@StartDate)) AS CalendarMonth,
DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate)) AS CalendarMonthNumber,
DATEPART(WEEK, DATEADD(DAY,N-1,@StartDate)) AS CalendarWeekNumber,
DATENAME(DW, DATEADD(DAY,N-1,@StartDate)) AS DayChar,
DATEPART(DW, DATEADD(DAY,N-1,@StartDate)) AS WeekdayNumber,
CASE WHEN DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate)) < 4 --April
THEN DATEPART(YEAR, DATEADD(DAY,N-1,@StartDate))-1
ELSE DATEPART(YEAR, DATEADD(DAY,N-1,@StartDate))
END AS FiscalYear,
CASE WHEN DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate)) < 4
THEN DATEPART(QUARTER, DATEADD(DAY,N-1,@StartDate))+3 -- April (use your brain to work it out for other months)
ELSE DATEPART(QUARTER, DATEADD(DAY,N-1,@StartDate))-1
END AS FiscalQuarter,
CASE WHEN DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate)) < 4
THEN DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate))+9
ELSE DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate))-3
END AS FiscalMonthNumber,
CASE WHEN DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate)) < 4
THEN DATEPART(WEEK, DATEADD(DAY,N-1,@StartDate))+40
ELSE DATEPART(WEEK, DATEADD(DAY,N-1,@StartDate))-13
END AS FiscalWeekNumber,
CASE WHEN DATEPART(DW, DATEADD(DAY,N-1,@StartDate)) IN (1,7)
THEN 1
ELSE 0
END AS IsWeekend,
DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY,N-1,@StartDate)), 0) AS MonthStart,
DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY,N-1,@StartDate)) + 1, 0)) AS MonthEnd,
DATEDIFF(DD, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY,N-1,@StartDate)), 0),
DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY,N-1,@StartDate)) + 1, 0)))+1 AS DaysInMonth,
CASE WHEN DAY(EOMONTH(DATEFROMPARTS(DATEPART(YEAR, DATEADD(DAY,N-1,@StartDate)),2,1)))=29
THEN 1 ELSE 0 END AS IsLeapYear
FROM #Tally
DROP TABLE #Tally
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
Then you would use the DateChar column as your name column (or just have it as an attribute in it's own right).
This article gives a good rundown of creating a date dimension in SSAS from a SQL Server source: https://www.simple-talk.com/sql/bi/creating-a-date-dimension-in-an-analysis-server-%28ssas%29-cube/
I think you need to take a more holistic approach to your problem and then it'll be scaleable too. Here is another page where the date format problem is dealt with specifically: http://stackoverflow.com/questions/22528601/how-to-change-the-date-format-on-ssas-2012
The answer given there is to store the format in SQL and then present it in the cube as a name column for your attribute, as I have mentioned, and gives yet another link where this is done.
Hope that helps.
Don't rely on the FiscalWeekNumber of that code. It has weeks that are less than 7 days near the first of most calendar years.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 18, 2016 at 1:46 am
Jeff Moden (3/17/2016)
PB_BI (3/9/2016)
tonzonline (3/5/2016)
Hi,Is the problem because I create this dimension by Wizard and choose "Create Time table from server" ?
I also notice, NameColumn property is "New binding(WChar)
If I click the ellipsis, Binding type is "Generate column" with no options to click.
There is a message at below ->
"This selection creates new column. If you select this options, you must run Schema Generation Wizard prior to deploying the project"
What is all that?
Wait, are you using a date table in your SQL source to base this dimension on or are you generating it in SSAS using a wizard? I would advise that you don't do the latter. If you do the former then your date key can be whatever the key of your source table is (I would recommend a "smart" date key such as 20160309 for today) and then for that key column you can set the name column as whatever formatted column you have in your SQL table.
Side note: every attribute in an SSAS dimension has a key column(s), a name column, and a value column. More on this here: https://www.mssqltips.com/sqlservertip/3271/sql-server-analysis-server-ssas-keycolumn-vs-namecolumn-vs-valuecolumn/
If you don't have a date table the following SQL will generate one for you with various attributes (it's been such a long time that I can't remember where I stole it from so forgive me for not attributing it to it's creator):
SET STATISTICS TIME ON --this is just to check performance and can be excludedSET STATISTICS IO ON
--SELECT @@DATEFIRST -- Check First Day of the week (1 = Monday, 2 = Tuesday etc.) Default US English = 7
--SET DATEFIRST n --Change first day of the week if needed
DECLARE @StartDate DATE = '2000.01.01'
SELECT TOP 11323 -- ~30 years
IDENTITY(INT,1,1) AS N
INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
ALTER TABLE #Tally
ADD CONSTRAINT PK_Tally_T
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--INSERT INTO <a date table>
SELECT CONVERT(INT, CONVERT(VARCHAR(10), DATEADD(DAY,N-1,@StartDate), 112)) AS DateKey,
DATEADD(DAY,N-1,@StartDate) AS DateChar,--Format this as you wish
DATEPART(YEAR, DATEADD(DAY,N-1,@StartDate)) AS CalendarYear, -- always best to have calendar dates in a date table for comparison
DATEPART(QUARTER, DATEADD(DAY,N-1,@StartDate)) AS CalendarQuarter,
DATENAME(MONTH, DATEADD(DAY,N-1,@StartDate)) AS CalendarMonth,
DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate)) AS CalendarMonthNumber,
DATEPART(WEEK, DATEADD(DAY,N-1,@StartDate)) AS CalendarWeekNumber,
DATENAME(DW, DATEADD(DAY,N-1,@StartDate)) AS DayChar,
DATEPART(DW, DATEADD(DAY,N-1,@StartDate)) AS WeekdayNumber,
CASE WHEN DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate)) < 4 --April
THEN DATEPART(YEAR, DATEADD(DAY,N-1,@StartDate))-1
ELSE DATEPART(YEAR, DATEADD(DAY,N-1,@StartDate))
END AS FiscalYear,
CASE WHEN DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate)) < 4
THEN DATEPART(QUARTER, DATEADD(DAY,N-1,@StartDate))+3 -- April (use your brain to work it out for other months)
ELSE DATEPART(QUARTER, DATEADD(DAY,N-1,@StartDate))-1
END AS FiscalQuarter,
CASE WHEN DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate)) < 4
THEN DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate))+9
ELSE DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate))-3
END AS FiscalMonthNumber,
CASE WHEN DATEPART(MONTH, DATEADD(DAY,N-1,@StartDate)) < 4
THEN DATEPART(WEEK, DATEADD(DAY,N-1,@StartDate))+40
ELSE DATEPART(WEEK, DATEADD(DAY,N-1,@StartDate))-13
END AS FiscalWeekNumber,
CASE WHEN DATEPART(DW, DATEADD(DAY,N-1,@StartDate)) IN (1,7)
THEN 1
ELSE 0
END AS IsWeekend,
DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY,N-1,@StartDate)), 0) AS MonthStart,
DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY,N-1,@StartDate)) + 1, 0)) AS MonthEnd,
DATEDIFF(DD, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY,N-1,@StartDate)), 0),
DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY,N-1,@StartDate)) + 1, 0)))+1 AS DaysInMonth,
CASE WHEN DAY(EOMONTH(DATEFROMPARTS(DATEPART(YEAR, DATEADD(DAY,N-1,@StartDate)),2,1)))=29
THEN 1 ELSE 0 END AS IsLeapYear
FROM #Tally
DROP TABLE #Tally
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
Then you would use the DateChar column as your name column (or just have it as an attribute in it's own right).
This article gives a good rundown of creating a date dimension in SSAS from a SQL Server source: https://www.simple-talk.com/sql/bi/creating-a-date-dimension-in-an-analysis-server-%28ssas%29-cube/
I think you need to take a more holistic approach to your problem and then it'll be scaleable too. Here is another page where the date format problem is dealt with specifically: http://stackoverflow.com/questions/22528601/how-to-change-the-date-format-on-ssas-2012
The answer given there is to store the format in SQL and then present it in the cube as a name column for your attribute, as I have mentioned, and gives yet another link where this is done.
Hope that helps.
Don't rely on the FiscalWeekNumber of that code. It has weeks that are less than 7 days near the first of most calendar years.
In my defence, I did steal it from someone else and I never usually use the fiscal part of it (annoyingly, most companies I do work for insist on defining their own crazy fiscal months and weeks. Outrageous!). In my not-defence, I should really have checked the code before posting it as you are, as ever, correct Jeff 😉
When I get in front of a SQL instance I'll try to resolve it, unless you have any thoughts?
March 18, 2016 at 7:47 am
PB_BI (3/18/2016)
When I get in front of a SQL instance I'll try to resolve it, unless you have any thoughts?
Yes. Don't use the WK datepart of SQL Server, ever! 😀
It's also pretty rough to do any kind of week other than ISOWEEK or something like it (can easily be calculated for just about any start of the week day). For example, which month does a week belong to at the month boundaries for Fiscal Calendars if you want Week of the Year as a value? And, what will you do when that doesn't match the month if that matters? For that matter, should a month start on a whole week and end on a whole week? Would an ISO like "month" be more appropriate?
Whatever the case, a company needs to clearly define what's going to be what for a Fiscal Calendar or even a "normal" Calendar before one can build a consistent Calendar table for week annotation.
As a bit of a side bar, pre-calculating the Date for each day of the calendar in a CTE will greatly simplify the datepart calculations. Also, people also tend to forget two of the most important columns on such tables and those would be the "DOW occurrence" for each month (allows for very easy {for example} method to find the 3rd Friday of every month and similar calculations) and a "WorkDay sequence number" so facilitate things like SLAs that state things like "available in 3 business days".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply