I am building a database for some data to build a cube (SSAS) after. Until now everything is fine and works but I want to modify my time dimension. Since now I have a table with year, month and day and use it as dimension. But for my use it would be nice if the hierarchy is not just like (2016-->5-->20); instead I want to show month.year at the month level (in this example: 05.2016). I had no problems separating the date but I can`t find a solution to show this part or to combine the two columns in SSIS. Is there any possibility to do so or can I create this in SSAS while setting up the cube?
What I´ve found out is that with the cast and datepart command I can show the things I want in the SQL Server Manager but I am a newbie in MS SQL and don`t know how to save the calculations in a new column.
had no problems separating the date...then you can use that column directly as an attribute in SSAS – Lamaks right, I am using the columns year, month, day via ID to my fact table in the cube but I dont get it how to combine year and month so that my hierarchy looks like this: year (2015), month (05.2015), day (20.05.2015). You know what I mean? It`s just a luxury problem because I need to work with the cube later in excel and that would make it easier in this step. – Floop