SurendraThotaMSBIProfessional

Hi All if you have average measure and needs to calculate for last 4 weeks then create DAX like shown below using DATESINPERIOD function Average of last 4 weeks = CALCULATE([Average For Report Period],DATESINPERIOD('Date'[Day],LASTDATE('Date'[Week Ending]),-28,DAY)) Average of last 12 weeks = CALCULATE([Average For Report Period],DATESINPERIOD('Date'[Day],LASTDATE('Date'[Week Ending]),-84,DAY))

Hi All we got a scenario where we have a table with daily granularity and we need to aggregate weekly taking denominator column MAX and numerator column relative SUM and create ratio (percentage). For aggregation above week we need to take SUM of denominator .so layer 1 within week and layer 2 above week format and we need to take averages for the percentage on both layers if you see the image , we need to represent data on weekly basis and  issue here is  [weekly freqency target] is present in all the records of the week in a table, in  above report each row represents weeks data ,so we have to take only one value from those 7 days , so here we need to take Max of that value Compliance %  = Reading towards Complaince/ weekly frequency target Another challange here is Reading towards complaince  may be greater than weekly frequency in those cases we need to consider only 100 % example               Reading toward...

Hi All we got a situation where we need to calculate daily budget , even  if fact table missing some dates we need to calculate for dates Cumulative Budget = VAR RowDate = DateTime[Date] RETURN     CALCULATE (         SUM ( DateTime[Budget]),         FILTER (             DateTime,             DateTime[Date] <= RowDate   && YEAR ( DateTime[Date] ) = YEAR ( RowDate )  && month ( DateTime[Date] ) = MONTH( ( RowDate )

Here i am going to explain how to calculate number of working dates between to given dates. In my approach i am taking a table called Holidays which contains holidays as shown below Once you have this table , we need to use this table in our Datetime table to calculate IsworkingDay column. Now give the relationship between Datetime and Holidays tables as shown below DAX :  IsWorkingDay = IF (NOT(DateTime[Day of Week]= "Saturday" || (DateTime[Day of Week]= "Sunday")) && COUNTX(RELATEDTABLE(Holidays),2)<1,1,0) Now you can create calculated column or measure  Total Working Days Column =  SUMX (     FILTER (         'Datetime',         'Datetime'[Date] >= Tasks[Input Start Date]             && 'Datetime'[Date] <= Tasks[Input End Date]     ),     'Datetime'[IsWorkingDay] ) Total Working Days Measure =...

1.        Please check Azure portal to get the Server and Database name based on the database connection strings shown below. 2.        Open Power BI Desktop 3.          3.  Select getdata and search for azure and from the list select azure sqldatabase  and click Connect 4.        Provide the Server nameand database name collected from azure portal

I am planning to start my career in Datascience and want to learn what are  machine learning models and algorithms. I have googled Before starting to learn and collected some of the points and wanted to share with you what i understand , we should understand  following main steps in analysis using machine learning models: 1. Diagnosing the data – before defining the possible approaches to work with data, it is necessary to analyse the raw data itself first. What kind of measurements are included,  which kind of models it is possible to apply to the data and defining the initial goal of the research. Try and identify all the metrics that are important to the business. The metrics we are optimising for have a profound effect on the solution we choose, so it is important to identify these early on. It also affects what alternatives there are to machine learning. 2. Data Preparation – merging data, imputing missing values or excluding variables with too many m...

For PowerBI pro License we have the feature called Groups . When we aCreate Group in Power BI ,It  automatically creates  a folder in OneDrive for Business for that Group . The best method for deploying  power BI Report/Dashboard fromPowerBI desktop to PowerBI  service is 1. Once the Report is finished in PowerBI desktop save the file. 2.Place that  .pbix file in Group folder in onedrive for Business 3. From PowerBI service Connect to the new file which is in Onedrive for that group 4.Later if you made any changes to the report , you can place the modified .pbix file in one drive it automatically reflects the change to powerBI when refersh happens

1. ADMIN of CRM online can select this feature to insert  Power BI Tiles in CRM dashboards 2. Go to Seeting --> System -- > Administrration --> system Settings 3. Go to Repoting Tab in system settings 4. Allow Power BI tile embeeding option to Yes 5. Go to Sales --> Dashboards  --> New 6. Select the layout whihc you want to insert 7. Now Click on Power BI Tile Tab ( appears after you perform step 4 ) 8. now you are able to see all the dashboards and tiles you have permission in power BI site 

Step 1 : Create a new web resource in CRM onine ( web resouces is under components is CRM solution) Step 2 :  In new Web Resource give the fallowing details         General          Name : PowerBI_Interactive          Displayname : Power BI CRM Dashboard          Description : CRM Dashboards using power BI     Content           Type : webpage (HTML)           Language : English              URL: iframe code to ccess powerBI reports , by using iframe code you can access power BI reports from any web page where it is used once web resource is created crosscheck if it is listed down in web resources section step 3 : In Dasbords Tab of CRM online  , create a new dashboard and link the web resources created previously Step 4 : save and publish the dashboard

How to Know Actual MDS Table name for Model Entity and Column name for the Attribute . here is the Script  USE MDS ------------------------------------------------------------ -- TODO: Define Model name, Entity name, and Attribute Name        DECLARE @Model NVARCHAR ( 50 )        = '<<GiveModelName>>' ;        DECLARE @Entity_Name NVARCHAR ( 50 ) = '<<GiveEntityName>>' ;        DECLARE @Attribute_Name NVARCHAR ( 50 )= '<<GiveAttributeName>>' ; ------------------------------------------------------------ -- Set @Table name        DECLARE @Table NVARCHAR ( 128 );              SELECT @Table = EntityTable FROM mdm . viw_SYSTEM_SCHEMA_ENTITY WHERE Model_Name = @Model AND name = @Entity_Name ; --...

Hi All when we tried to delete the members whose code is not autogenerated ( ie having specific code to the member) and try to insert the same code member we will get this error. The delete in MDS is a Soft delete which will available in MDS tables even after these deletes . So fallow these steps to avoid the error Step1 : Reverse the transaction of the deleted member to bring the member and its code back to an active state. See here for reactivating a deleted member: https://msdn.microsoft.com/en-us/library/ff487037(v=sql.110).aspx Step 2 : After that , use entity based staging to purge (permanently delete) the code from MDS. This will require using an import type of 4 or 6. Here is more information regarding entity based staging: https://msdn.microsoft.com/en-us/library/ee633854(v=sql.110).aspx Step 3:  Insert the member with same code now you wont get error .

Hi All we need to Create index on some of the Matching attributes in our Customer Entity. It is not straight forward in MDS DB , so for that we need to do the fallowing steps Step1 : Get the Entity ID and EntityTable metadata from mdmtblEntity  table in MDS DB Step2 : Need to Get the attribute tablecolumn from mdm.tblAttribute table . we can create a Dynamic query to create custome index when you know EntityName and attributes names. Dyanmic query USE MDS GO Declare @SQL Varchar(2000), @f int =0 Declare  @i int, @S Varchar(50) Select @S=EntityTable , @i=ID From mdm.tblentity where name='Customer' If Exists (Select Name From Sysindexes Where Name='IX_Customer_MatchAttributes_001') Begin Set @SQL ='Drop Index IX_Customer_MatchAttributes_001 on mdm.' +  @S exec  (@SQL) set @f=1 End Set @SQL   ='CREATE NONCLUSTERED INDEX IX_Customer_MatchAttributes_001 ON [mdm].' Set @SQL= @SQL + '['+  @S +'] (' Select @SQL = @SQL + '[...

Hi All I have listed out some of the differences between azure SQL Database and SQL server Databases. you need to select any one of the 2 based on the features you want in your project , in our project we are handling with PII data where we need Transparent Data encryption which is not available in Azure SQL Database so we went for SQL server databases .   SQL Server Database Azure SQL Database services Category       Size Limitations SQL server can grow upto 524,272 Terabytes 1 to 5 GB for web edition   and    10 to 150GB   for business edition       Connecion Limitations   Tcp/ip   , sharedmemory , and Named Pipes client protocols Only one TCP/IP   Windows authentication No windows authentication   only through username and password   Communication through static/Dyanic port only through 1433 port Unsupported Features In SQL database   Agent Service    ...

Hi All some times when we run the backup or restore of big databases  it will take time and we need to know the status of that task then please run the below query which will the percentage complete . select   T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)                  , R.cpu_time, R.total_elapsed_time, R.percent_complete from     sys.dm_exec_requests R                         cross apply sys.dm_exec_sql_text(R.sql_handle) T  

Hi All when we restore the MDS DB from one environment to another Environment and the versions are not same in those  environments  in this we case , after restoration is finished we connect to Master Data services configuration manager and then select the restored DB , it automatically enable Upgrade Database option , then go with that option . once everything is configured , when try to connect using Master data maestro tool we are getting I am getting the fallowing error  The execute permission was denied on the object ‘udpSystemget’ database MDS , schema mdm . Cannot find the object 'tblStgBatch', because it does not exist or you do not have permission. Cannot find the object 'tblUserMemberCount', because it does not exist or you do not have permission. Then I started debugging the issue I ran the MDS configuration manager  also it required upgrade database option and I did it , it is successful , and I am also able to browse MDS APP , there i...

Hi All when we restored the MDS database in sql server and configured the Master data services configuration manager  and all the settings went well . when we try to access the Master data services web service we are able to access the web service but when we click on any feature we are getting the [HttpWebRequest_WebException_RemoteServer] Arguments : NotFound  Debugging resouce strings are unavailable  as shown below then we try to browse the  MDS services http://localhost/MDS/Service/Service.svc we got the fallowing error Server Error in '/MDS_PreProd' Application. Memory gates checking failed because the free memory (5234458624 bytes) is less than 5% of total memory.  As a result, the service will not be available for incoming requests.  To resolve this, either reduce the load on the machine or adjust the value of minFreeMemoryPercentageToActivateService on the serviceHostingEnvironment config element. Description: An...

when we are trying to load huge data using ssis we are getting this error ole has sent a request and is waiting for a reply in ssis. The solution we got is apply delay validation = true on the package level so that you can avoid the above error.

Hi All    when are trying to load Float value into Varchar column the data is load with exponential notation .  which is not good for reporting , so we want represent the number as it is without  e   , so when try to convert that float into Bigint  and then convert into varchar we got the  error.  again tried with decimal(38,0)  still got the below error   Msg 8115, Level 16, State 6, Procedure mdm_GetEndXCustomerDetails, Line 16 Arithmetic overflow error converting float to data type numeric.   Then we have used the STR() function which worked very well   LTRIM ( Str ( mem . [MemberCardNo] , 38 , 0 ))  Hope this will save your time    

Hi All we need to get files from Https location and then extract and load . For this we are using Script transformation AS SOURCE . As the file need some string operations to get columns we used Script transformation in that we used Httpwebrequest  to connect to that URL by passing username and password . The package works fine from BIDS in my local machine but when we deployed in dev server and created SQL job  the job is getting failed at  HTTP url connection level . with the fallowing error Code: 0xC0047062     Source: get chat file list get files [1]     Description: System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it Code: 0xC0047038     Source: get chat file list SSIS.Pipeline     Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. ...

I just want to explain one of the issue we faced when we are using SSRS connecting to oracle  and how we solved that issue Issue : we have a report which connects to Oracle DB . the Query in the dataset is running in 9 Secs  in PL/sql developer  but the same query is taking more than 150 secs in SSRS. we are using Oracleclient type provider. Solution: we tried with different providers . we tried with Mirosoft OLEDB provider also but it is also taking same time . what we observed is the query designer is formating the query structure so that it is removing Index hints. even when we use the query hint the SSRS is removing those hints while executing . to solve this issue we did a workaround. there is a work around to keep the index Hints . once you have created the dataset ,later open the same datset in Expression mode which is formated by the query designer and write your query which contains Hints. It is working for me . Ref : https://social.msdn.micros...